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 |
|
Trudye
Posting Yak Master
169 Posts |
 Posted - 2008-03-28 : 10:09:46
|
| Hi guys, I am importing a file using the Bulk Insert command, but the fieldterminator is not working for me. My data:“lname”, “fname”, “addr”, “phone”, “lang”My command:BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:\operations\datafiles\GE\FTPIN\GE_RCF_POS_EXPORT.txt' WITH (FIELDTERMINATOR = '","')The quote are removed from every field except the first quote in front of lname and the trailing quote behind lang. I tried changing the command to:BULK INSERT dbo.zGE_RCF_POS_IMPORT FROM 'E:\operations\datafiles\GE\FTPIN\GE_RCF_POS_EXPORT.txt' WITH (FIELDTERMINATOR = '"')But ran into field conversion problems, I also tried defining two fieldterminators got a syntax error. How can I get the desired results of removing all dbl quotes? Or do I have to write something to follow the Bulk Insert to remove the leading and trailing quotes? |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2008-03-29 : 07:56:29
|
| Hi Could you post some sample data - afew rows and the exact error mesageJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-29 : 09:18:10
|
| You can do it after the import if you wish. Just insert into a single text column then parse it. Note you can't use a comma as the delimiter as there can be commas embedded in the fields (that's why tyhey are quoted).Here's a function that will split the string http://www.nigelrivett.net/SQLTsql/f_GetEntryDelimiitted.htmlTo bulk insert the quoted fields needs a format file - here's an examplehttp://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.htmlat the end of that article.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Trudye
Posting Yak Master
169 Posts |
Posted - 2008-03-29 : 13:50:15
|
| Soooo nr what you're telling me is there is no clean way to import my data using the Bulk Insert command?Jackv, my after data looks something like this when I use '","' fieldterminator:"lname, fname, addr, phone, lang"The only problem is I can't get rid of the first and last quotes. I guess I'll have to take nr's advice and write some code to remove it. You would think Microsoft would have thought of that. I just assumed they had and I was missing something. It's very disappointing to find out I was wrong. Thanks to everyone who read this thread and a special thanks to those who responded. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-03-29 : 13:56:50
|
| >> Soooo nr what you're telling me is there is no clean way to import my data using the Bulk Insert command? Not at allTo bulk insert the quoted fields needs a format file - here's an examplehttp://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.htmlat the end of that article.That's how to import quote delimyed data using bulk insert without needing to reformat after the load.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Bulk Insert not working properly