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"
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.
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
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
