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.

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Remove Quotes from Text file

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" means

A,B,C
A,"b,b,b,b",C
A,"b,""b"",b,b",C

so 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",C

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-27 : 06:29:50
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -