problem with import data to sql server from csv files

Question

Post reply

problem with import data to sql server from csv files

Sridhar-137443

Hall of Fame

Points: 3762
More actions
January 18, 2005 at 10:09 am

#61522

Hi,
I have created a dts package wich will import data from csv files. The csv files will have records for example
20040325,000072,005309,1.00,1.00,2.00,LUNCH where the decimal values represent price, cost, qty. some times when the qty exceeds 999 they will send the qty as 1,000 or 1,005 instead of 1000, 1005. so when I run the DTS package, it is giving the error too many columns found due to that extra comma. So Is there a way to solve this? May be a tool which will remove those extra commas?
Thanks,
Sridhar!!

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Can you not get them to use a quoted identifier for the data?

Otherwise you are going to have to create some external script to rework the data using vb or perl.

Shamless self promotion - read my blog http://sirsql.net

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 2

Hi Nicholas,

This problem occurs not very often. So we cannot ask them to use quoted identifier for the data. If you have any vb or perl script, can you send them?

Thanks,

Sridhar!!

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

I don't have anything, and without using an identifier on the data, I'm not sure that you can do anything to resolve it (short of getting them to stop using , seperators, or changing their column delimiter)

Shamless self promotion - read my blog http://sirsql.net

oldgoat SSCrazy Points: 2344 More actions · Answer 4

If you could get them to build the csv with another delimiter - ; or | forinstance, that stops the comma casuign a problem. You'll have to change the dts though.

Whichever way you look at it, you can't have both and have it work!

Harry Mohrmann-153970 Grasshopper Points: 23 More actions · Answer 5

I have had a similar problem. I used a temp table and imported the entire record as a single field. Then used charindex to remove the the comma's.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 6

But if you remove all the commas you end up with a single long string.

Shamless self promotion - read my blog http://sirsql.net

oldgoat SSCrazy Points: 2344 More actions · Answer 7

test for the extra comma, replace it with something else, replace the remaining commas, return the remaining one to a comma.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 8

How would you know which comma is the extra comma?

Shamless self promotion - read my blog http://sirsql.net

oldgoat SSCrazy Points: 2344 More actions · Answer 9

if you know the number normally expected, do a charindex, loop up to that number, and if the next one comes back with a value, you've got the position.

David Burrows SSC Guru Points: 65141 More actions · Answer 10

The only way I can see to make this work is to input the data as one column and process the data, using your eample of

20040325,000072,005309,1.00,1.00,2.00,LUNCH

pass the first 3 commas

then 3 times, remove any comma until you reach a full stop, then proceed passt the next comma

once this is done then parse the string using the commas

success will depend on how many value fields there are, how spread out they are and that they all have a decimal point

you could write a UDF to do this

Far away is close at hand in the images of elsewhere.
Anon.

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 11

Hi all,

Thanks for the suggestions. I'm trying to use s.m.b's idea.

I have loaded all the data into a temp table as a single column.

Now my question is, Is there a way to find the number of occurences of ',' in the string column? In that way I can find the records with extra column?

20040321,000187,000251,0.89,0.89,39.00,BKFST

Thanks,

Sridhar!!

David Burrows SSC Guru Points: 65141 More actions · Answer 12

LEN([column])-LEN(REPLACE([column],',',''))

Far away is close at hand in the images of elsewhere.
Anon.

oldgoat SSCrazy Points: 2344 More actions · Answer 13

number of columns = expected number of commas + 1

something like a while loop would allow you to iterate with charindex to a point after the last #expected#, then a single charindex finds/doesn't find the extra.

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 14

Hi All,

I have used (len(col1)-len(replace(col1,',',''))) to find the number of occurances of ',' and I am able to display all the records which have those extra column. Now Is there a way to replace that extra column? The problem is, that extra column is not at the end. once I'm able to replace the extra comma how could I transfer this data to the new table(original table)? Can I automate this using DTS? since the problem does not appear often, Is there a way to check in DTS that if the extra comma appears in column then replace the extra comma with empty string and insert into new table. if not just insert into new table.

Thanks,

Sridhar!!