OpenRowSet with CSV - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

OpenRowSet with CSV

Author

Topic

shadowi
Starting Member

3 Posts

Posted - 2006-10-09 : 12:27:31

SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DEFAULTDIR=C:\;Extensions=CSV;',
'SELECT * FROM abc.csv')

ID, Name, Tel,
12, Peter, 637834
34, Mary, (853)34343434
43, "John,Lee", 234238

I find that the the 2nd row, tel column will return null
I think it is the mixed-type problem but it seems that "IMEX=1" doesn't work with CSV file, what can i do for this?
Please Help... ><"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-09 : 13:32:33

It is a mixed types problem - the text driver (which I believe uses the Excel driver in this case) sees that most of the values in that column are numeric then it will treat them all as numeric so when it then sees that value with the parens it has to ignore it. Change one of the others to include parens and it will work because then the majority of the values will be text and it will make the whole column text instead of numeric.

eg.

ID, Name, Tel,
12, Peter, 637834
34, Mary, (853)34343434
43, "John,Lee", (853)234238

The IMEX value won't have any effect unless you change a registry setting to tell the driver to always use text (which may be a solution in this case although you then won't get the first column being numeric for example).

See here for more about the registry entry
http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources