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 |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2003-10-17 : 11:21:29
|
I have some files that need to be imported daily and I want to use BCP rather than DTS. Here is my problem:The first two columns have quotes around the data and I don't see a switch to specify a double quote as a text qualifier as you can in the import wizard in virtually every microsoft program.Is there a way that I can specify that the text qualifier is a double quote? Right now the double quotes are imported with the data!  Thanks in advance.DanielSQL Server DBA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-17 : 13:06:22
|
| When I googled your problem, I saw that some people had said to use a format file.But since BULK INSERT is faster than bcp, why not use that instead?[url]http://www.sqlteam.com/item.asp?ItemID=3207[/url]Tara |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2003-10-17 : 13:23:38
|
quote:
Originally posted by tduggan
When I googled your problem, I saw that some people had said to use a format file.But since BULK INSERT is faster than bcp, why not use that instead?[url]http://www.sqlteam.com/item.asp?ItemID=3207[/url]Tara
Actually, at a glance this article will not work. Sorry for not giving more detail before.Here is an example of what my data would look like:"WD01","Widget",0,1,67,8,97"ZR01","Other Stuff",1,43,10,0,0I'm trying to figure out the BCP format file right now but it looks kind of hopeless.DanielSQL Server DBA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-17 : 13:27:50
|
| You could also just remove the double quotes after the import using the REPLACE function, but I'm sure you are trying to avoid this.I couldn't get the format file to work when I tried it using your example.Tara |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2003-10-17 : 14:14:03
|
quote:
Originally posted by tduggan
You could also just remove the double quotes after the import using the REPLACE function, but I'm sure you are trying to avoid this.I couldn't get the format file to work when I tried it using your example.Tara
Actually I think it sucks that Bulk insert and BCP dont have something that is even standard to EXCEL. If you look and any text import untility from Microsoft you'll see the "Text Qualifier". Excel, Access, Outlook, SQL Server DTS, ... they all have that as a selector.I'm trying to use a format file that I found from a google search. I'll keep you posted. But the fact remains that this is much too hard for something that seems like MS just forgot to add a switch for.DanielSQL Server DBA |
|
|
|
|
|
|
|
|
BCP - - Specifing a text qualifier?!?!?