May 20, 2010 at 4:58 am
Hi
I have a problem when I import a csv file to a table, when set up a connection manager for the csv file and preview the data, it concatenates the next row onto the previous row and adds 2 double 2 quotes and 2 squares ontp the start.
please see attachment
May 20, 2010 at 5:04 am
What are your row and column delimiters set to?
Looks like you need to set your text qualifier to be " to avoid importing all the quotes.
Use a hex editor to find out what the unprintable characters are - and use that as your row delimiter.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 20, 2010 at 5:05 am
I have my text delimiter to a " already because most of the fields have quotes in
May 20, 2010 at 5:15 am
clucasi (5/20/2010)
I have my text delimiter to a " already because most of the fields have quotes in
Please post a couple of records of source data. Do you really want column 1 entries to contain quotes?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 20, 2010 at 5:19 am
here is some sample data, also it only happens when the first filed is 21
21,"I",1,10024414482,1,2,2009-09-01,"CI03",100012717709,382029.00,398583.00,1,4230,2009-09-01,,2009-12-22,2009-12-22,"","00BRGE","",,,""
24,"I",2,10024414482,"4230L000164357","ENG",1,2009-09-01,,2009-12-22,2009-12-22,,"",,"","STUDIOS",,"",,"","VULCAN HOUSE",33433131,"","Y","M5 9JS","SALFORD","N",,,""
21,"U",3,10004678137,1,2,2003-12-01,"CI03",,376896.00,398991.00,4,4230,2003-12-01,,2009-12-22,2003-12-01,"DONE BROTHERS","00BRGC","",,,""
24,"U",4,10004678137,"4230L000015980","ENG",8,2003-12-01,2009-04-01,2003-12-01,2009-12-22,,"",,"","",,"",,"","FORMER DONE BROTHERS",33432125,"","N","M30 9QG","","Y",,,""
24,"I",5,10004678137,"4230L000164359","ENG",1,2009-04-01,,2009-12-22,2009-12-22,,"",,"","",,"",,"","UNIT 3T",33432125,"","Y","M30 9QG","MANCHESTER","N",,,""
21,"U",6,10004678205,8,4,2009-07-13,"CZ01",,378012.00,398588.00,1,4230,2003-12-01,2009-12-22,2009-12-22,2003-12-01,"","00BRGC","",,,""
May 20, 2010 at 5:30 am
If your text qualifier were working correctly, you would not be seeing quotes in the output.
It seems that you are suggesting that only rows beginning with 21 are failing. But the row beginning with 10 also looks wrong to me.
Did you use a hex editor to determine those unprintable characters?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 20, 2010 at 6:52 am
Your right the row beginning with 10 is different but that can be excluded because i can deal with that, i have tried to see the unprintable character using a hex text editor but I cannot see the problem, if I put the file into excel 2007 and save it as a text file it seems to work but in needs to be a csv file.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply