need help parsing a csv file using ssis

[pms18066]

We have several clients whos send us flat files comma seperated .csv files. These files may have text qualifiers around the text fields (") . the problem we r having is the parsing of these rows dont always come out correct. We have gotten to the point to try and use excel to parse the fields correctly, however this has introduced a new pbl. when a data element such as "10-23" is inserted into an excel sheet it turns it into a date, which then becomes too large to be inserted into the target table in SQL Srvr. we have commas and double quotes embedded throughout our data so parsing it is a bit difficult. the SSIS package uses Bulk load to insert the records and it creates the table based off of our DDictionary which may have less columns then the parsed files once the data is split out base don the delimiter. Does anyone know if we can force excel to treat everything as text so it doesnt reformat any of the data.??

 

[djj55]

I would suggest NOT using Excel it does bad things to good data. You can tell Excel the column is text but on "open" the data is converted. [sarcasm]Thanks Microsoft[/sarcasm]

I have taken to writing .NET code to import the data.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[pms18066]

yes That is what we r finding. We are using CSharp to do the parsing currently but that is not working as expected, so we r looking to do it another way. we have a script component which calls a utility func written in C# and it cant handle the variety of data issues we are seeing.. ie.

"1249",18,"**3/16/92 RESP COPY OF WAGE RETURNED ""NO SUCH ADDRESS"", 1941 MERDLE ST,"

this should parse into three columns but actually comes back as 5 columns with just the last double quote in the 5th column. Excel does this correctly (three columns) thats why we r trying to use it, but alas we find that we cant pre-determine the data type to pass into excel, as you say it reformats it upon opening the file.... Ughhhh!

 

[plevey]

Have a look that the SQL REPLACE function. You could replace the double quotes with a space.

If fishing was easy it would be called catching