Can I use a text qualifier in bulk insert?

[bmacbmac]

Hi, I have a table:

create table #temp (field1 varchar(200), field2 varchar(200), field3 varchar(200))

I have a text file with the following data:

"JOHNSON, BOB","1","SQUARE"
"PIPER, PETER",,"CIRCLE"
"JENKINS, LORI","13","RECTANGLE"
"SMITH, LISA",,"TRIANGLE"

I am trying to run a bulk insert. However....

If I use (with fieldterminator = ',') - the data file is split between the last name and the first name in the first field.
Also, the 2nd field doesn't use double quotes when it is empty. Instead of ,"", it's just ,,

I can do a few find/replaces in my text file to get where I need to go, but I'm hoping to set this up for someone else so they can insert the files we receive each day. Is there a way to keep the bulk insert from considering a comma if it falls within the double quotes? Like using a text qualifier or something?

Thanks!

Brian

 

[gmmastros]

You can use a format file to accommodate text qualifiers.

Also, the 2nd field doesn't use double quotes when it is empty. Instead of ,"", it's just ,,

This is your "show stopper". In an ideal world, you would be able to go back to the source of data and tell them to fix it.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Thanks gmmastros...

Can you expand on:

You can use a format file to accommodate text qualifiers


I'm not sure what you mean by a format file?

Thanks!

 

[trevil620]

In Access 2010 I saved your sample as a ".txt" file, then imported into a new table in Access. I specified: (a) field delimiter is a comma; (b) Text fields delimited by Quotation mark (later I added headers and told Access the first row was column names).
If that is an option for you, you could either:
1. Check if table exists, delete, import, run append query (assumes you use 'import into new table' option)
2. Delete all rows from existing temp table, import into temp table (appending rows), run append query.
Access also will save the import specs if you want to use again.

Sometimes computer problems are like genealogy... The answer to one problem leads to two more!

 

[gmmastros]

Format files are discussed here:

http://blogs.lessthandot.com/index.php/datamgmt/dbprogramming/title-12/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bmacbmac]

Thanks trevil620. I am trying to automate this for a user to just run an SQL query. I'm not sure if the Access route will work in that enviroment.

Thank you for the link gmmastros.. Looks like some great information I'll read it over now.