Text Import CSV; conditional double quote delimiter in data 1

[lameid]

I am using SQL Express 2008 R2...

I have used BULK INSERT before but never with a format file which after reading an example here of a non-xml file, I am not sure that what I want to do can be accomplished...

The CSV file looks like what Excel does when it saves to a CSV file and has data formatted as currency....

So I have a column of currency that conditionally decides if each datum shouold be double quote delimited if it does have a comma in it....

Something like this...

1,2,$5.25,
3,4,"1,255.25"

Actually I have multiples of these currency fields and they are not always at the end.

How should I go about getting this type of data to import? What I read about XML format files in BOL was not really that insightful at this level of detail.

Is this easy in SSIS?... In theory my database should be moving to a real SQL 2008 R2 server in the next few weeks.

My ultimate goal is to read this data into a temp so Ican pick up from there to run validation to verify that the other system's calculations reconcile to mine.

Any insight, relevant articles etc. would be greatly appreciated.

 

[gmmastros]

You will have MANY problems with this. The problem is that the data is "quoted" inconsistently. If the data in the 3rd column ALWAYS has quotes, then you can get it to work. Since it doesn't, you will have an extremely difficult time loading the data.

My first suggestion is to go back to the source of the data and tell them that they need to be consistent with their quotes.

If they refuse, I would then write a small app that can do this for you. Basically, this app would read the file, check for the quotes. If the quotes are missing, add them in, and then save the file. Once the data in each column is quoted consistently, you will be able to load it in to the database.

There is (of course) another way to do this. You would load each row in to a temp table that has just one column, and then you could write a series of updates to peel off each column. This is likely to be slower than the other approaches, and it's not necessarily something I would recommend. If you are comfortable with T-SQL, and you are importing just a couple thousand rows at a time, this may be a viable solution for you.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lameid]

As I suspected... The first thing I did of course was to request the data formatted not as currency and not double quotes or all columns with double quotes.... But you know how counterparties can be... you never know how long it will take just to find out if they will do it, let alone when.

And many thanks...

Right now my front end is Access... It will import that data without trouble... There's a thought... I know you can use Jet for Excel could you do the same with Text... and that would require SSIS, correct?

 

[lameid]

Ok so SSIS with Access / Jet may be a solution to evil data but the small dedicated app would work too / better.

 

[simian336]

Why don't you just use a text editor to do a find and replace on all double quotes and dollar signs with nothing.

Simi

 

[flapeyre]

Why don't you just use a text editor to do a find and replace on all double quotes and dollar signs with nothing.

I don't think that is going to work, simply because the currency column is using a comma as a thousands separator. Remove the quotes, and the comma becomes a column separator, and your data is kaput.

Better to import to Excel, reformat the columns, and save as a .csv.

-- Francis
Et cognoscetis veritatem, et veritas liberabit vos.