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 |
|
niall29
Starting Member
5 Posts |
 Posted - 2006-01-25 : 21:33:07
|
| Hi,I am not sure if I am in the right forum this time so please be patient with me.I have a text file which is sent to me every day with roughly about 70 columns and about 300,000 rows which has columns delimited by commas and Text by quotations and Rows by LF.and I run a DTS to import the file into a table everyday before I get to work, but it has crashed a few times and today I investigated why (I was getting an error saying "Column Delimiter not found") when I looked at the record where the problem was I noticed that if the street was only one letter the file which was sent to me has quotations aroun it to show it is supposed to be like that,(eg ,"72 E "N" St",) but this is causing me havoc as my DTS thinks it is a Text field and is looking for a comma after the quotes close and hence "Column Delimiter not found" I thought of building a Replace statement into my DTS in VBscript but that is not helping I need to remove the quotes before I start the import.PLEASEEEEEEEE can someone help. I am totally lost with this one.Thanks in advance for any help. |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-26 : 01:02:13
|
| Your source file is wrong - in the sense that "comma delimited" meansA,B,CA,"b,b,b,b",CA,"b,""b"",b,b",Cso it should have the embedded quotes "doubled up".However, "Comma Delimited" is a very loose specification, honoured more in the breach!, so I'm not surprised that your source file doesn't properly adhere.So I suggest you start with getting the provider of the file to double up the embedded quotes (probably a good idea to do it manually with Notepad or somesuch and just prove that DTS will honour doubled-up embedded quotes - its possible it use some other variant, like:A,"b,\"b\",b,b",CKristen |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-01-27 : 06:29:50
|
| http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.htmlMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Remove Quotes from Text file