bcp issue with field terminator

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 2005 Forums

Transact-SQL (2005)

bcp issue with field terminator

Author

Topic

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-03-30 : 14:09:38

All Experts,

Need small help in here with the bcp format. I have a CSV file with the column delemiter as , and my data is shown in the below format...

abc,"pqr,ccc",xyz
aaa,"bbb,ccc",ddd

so the above data has only 3 columns but has 4 ,'s because of which the import is not getting the correct data into the fields.. is there any workaround for this(other than changing the file to other delimiters)?

i am using the below bcp commant to import...
bcp dbname..test_table in C:\test_file.txt -c -t,-T _SServername

please let me know the changes which i may need to do for this in order to import if possible?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-30 : 14:14:11

You would need to use a format file for that. You really need to have the file use a delimiter that doesn't exist in your data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-30 : 14:15:26

It would be a goofy way to do it, but I would probably take a stab at a universal search and replace.

First would be to replace ," with |"
then I would try ", with "|

In theory that would leave the comma between the values in the second column.

I know you specifically said you wanted a workaround other than switching delimiters, but this might be the most efficient way.

Hey, it compiles.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-30 : 14:18:31

In a dos box run BCP /? to see the possible switches.
Maybe it is the switch -q

No, you're never too old to Yak'n'Roll if you're too young to die.

sql_server_dba
Posting Yak Master

167 Posts

Posted - 2011-03-30 : 16:28:56

Thanks all for quick reply. I will ask the vendor to send the file with other delimiter.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-30 : 22:36:10

or you can use using OPENROWSET


SELECT 	* 
FROM 	OPENROWSET('Microsoft.ACE.OLEDB.12.0',
		'Text;HDR=No;Database=C:\', 'SELECT * FROM [test_file.txt]')

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources