question

redder avatar image
redder asked

Comma delimited File Load using SQL 2005

I have a 6 million record flat file where some records have issues. Delimiter is a comma. Text qualifier is quote ". Ex: Bad record -- notice how the extra quotes in the address that is killing the load process. Not all records are like this. "Firstname","Lastname","12"3" Main St" Ex: Good records -- 95% of file. This loads fine. Is there a work around for the above in flat file load without having to find each record manually and then re-saving file. 6 million+ records takes a while. Thx. "Firstname","Lastname","112 Main St"
sql-server-2005sql
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
I would have handle it at Extraction level. Anyhow, If the problem is the load process fails (assuming you are using BULK INSERT TASK), you can handle it by increasing the MAXERRORS (0 indicates that an infinite number of errors are allowed). Hope this helps.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
You could also consider using SSIS. SSIS works on the basis of data flow, allowing you to run the import and re-directing bad data into a separate output (text file, table etc.). This would at least allow you to import the good data and push the bad data to the side for cleansing. [Take a look at this question on Stackoverflow for a great example of doing this][1]. [1]: http://stackoverflow.com/questions/6464601/validate-csv-file-data-before-import-into-sql-server-table-in-ssis
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
You could use LogParser to read the file in. It will accept the commas as field delimiters and strip the leading and trailing double quotes. You would then need to remove the double quotes in the middle of the data with TSQL if that is necessary. Take a look at http://www.simple-talk.com/community/blogs/jonathanallen/archive/2010/05/24/LogParser_part_1.aspx It wont be as fast as the other methods but may be worth considering given the control it gives you
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Gah - logparser. I forgot it again. (+1)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.