Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
 Posted - 2010-06-17 : 07:18:39
|
| I have just had a csv sent to me from an external source and am having problems trying to import it. Some (most) of the fields (including the headers which are in row 1) are enclosed in quotes " ", but there is one of the columns (the last one) which does not always conform to this format, sometimes it has quotes around the content, whilst other times (if there is a lot of text with commas in it) it is not enclosed in quotes.Now if i try and import the csv using SSIS using quotes as text identifier the import will fail (because of the above reason).Also if I try a bulk insert using quotes as delimiters it will also fail.I have tried stripping out the quotes using an excel macro, but then that leaves me with the problem of commas in the text of each field creating problems, shifting data forward into the wrong fields.The external people say they have no control over how the file is exported/built as they are using legacy systems.Any ideas on how i can get around this please, as i also need to automate the process as well. |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-17 : 07:41:16
|
We talk about how many lines per file?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-06-17 : 07:55:19
|
One chance I see.Add an additional line to the end of the file.If your file has 4 columns then for example this:"","","",""So MS has a change to identify the rest (above) columns in the right format for you.In my test that has solved it.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2010-06-17 : 09:03:23
|
| The files differ each time they come across, so there's no fixed length to the file.Thanks, I'll give a shot at what you've suggested. |
|
|
|
|
|
|
|
|
HELP! Importing a non standard csv?????