Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
funk.phenomena
Posting Yak Master
121 Posts |
 Posted - 2013-02-20 : 10:25:05
|
| Hi All - I'm using the OPENROWSET function which imports a CSV file into a table on the server.There's a column on there which is numeric with a capitalized ALPHA character in front of it:[CODE]VENDORC12345C12346F12345F12345[/CODE]When I import it, it automatically converts the value to currency:[CODE]VENDOR12345.0012346.0012345.0012345.00[/CODE]I've tried casting the value as nvarchar, but always drops the 1st character. The only way around this is by manually replacing the capital letter with a small letter on the CSV file.Any ideas? Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-20 : 10:37:50
|
| change datatype by explicit casting to varchar in OPENROWSET------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
funk.phenomena
Posting Yak Master
121 Posts |
Posted - 2013-02-20 : 10:50:24
|
quote:
Originally posted by visakh16
change datatype by explicit casting to varchar in OPENROWSET
Here is my openrowset statement:SELECT * FROM OPENROWSET ('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};', 'SELECT * from G:\temp\AUDIT.CSV') How can I go about an explicit cast? I've tried doing this and doesn't work:SELECT * FROM OPENROWSET ('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};', 'SELECT CAST(VENDOR AS VARCHAR) from G:\temp\AUDIT.CSV') |
|
|
|
funk.phenomena
Posting Yak Master
121 Posts |
Posted - 2013-02-21 : 11:49:41
|
| Anyone else have a solution?Thanks! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-02-24 : 03:43:14
|
| Have you tried this?SELECT CAST(VENDOR AS VARCHAR(10)) FROM OPENROWSET ('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};', 'SELECT * from G:\temp\AUDIT.CSV') MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
OPENROWSET import problem