When Written: Feb 2011
I am not alone in getting annoyed about incompatibilities between the way different products from the same company deal with data. I came across a particularly annoying problem the other day when having to write an import routine to take some comma separated data and bring it into a SQL database. Normally a simple job with a simple ‘BULK INSERT ‘ type query, but this time it was failing, so I fired up Excel and it imported the data perfectly. Strange I thought, and investigated it further with the excellent Notepad++ (http://notepad-plus-plus.org ) below is a example of the data that was causing the problem:
123,”fred smith,The avenue”,567
Now according to Wiki (http://en.wikipedia.org/wiki/Comma-separated_values) this is a perfectly valid format for CSV files so what was going on? After a lot of trawling various forums it turns out that only Excel reads CSV files correctly, all the SQL import tools fall over with this file. I needed a way of importing this data in code, so in the end I had to write my own CSV parser to get the data in, but why should this be necessary? Shouldn’t a query like:
BULK INSERT CSVTest FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
Work? Apparently not! Come on Microsoft let’s fix this, or perhaps it is already in the soon to be released next version of SQL server, I wonder? But I’m not holding my breath.
Article by: Mark Newton
Published in: Mark Newton