Bulk Insert with commas within quote deliminated fields

[weigoldk]

I have a CSV file that I need to import into 2005.

Here’s a sample of my data:

Expires,BName,Applied
9/11/2011,SANDY'S GIFTS,1/30/2004
9/11/2011,"LEO J TRUMBLE, III",12/3/1999
9/8/2011,S & S EQUIPMENT,7/4/1999
9/7/2011,JACKSTAR CO., 11/27/1996
9/11/2011,WALKER'S METAL ART,4/6/2001

The data is nicely formatted for us. When (and only when) a BName has a comma in it, the entire BName is surrounded by quotes. For example “LEO J TRUMBLE, III”

I've looked around the forum and since I’m a SQL newbie, I’ve learned a lot, but I’m still hitting a wall.

I can use the SQL Server Import and Export Wizard and that works fine with my csv file (I use " for my Text Qualifier).

From here I thought to use the package built by the Wizard for SSIS. Digging deeper, it is suggested that SSIS not be installed on the SQL server and since this is our stand alone web server, setting up a new workgroup, server, install SSIS, etc. just seems over kill. I’m sure it would be a blast to do all of this; I just don’t have the time.

MS says DTS is being phased out so I didn't even start down that road.

So then I found mention of a bulk insert and I thought OK—I’ll get that to work and then I’ll get it to automate in some kind of job. When I do a bulk install in MS SQL Server Management Studio, I run into an issue with commas within fields.

Here is my bulk insert:

BULK INSERT dbo.DBA
FROM 'C:\database\dbasample.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')
GO

The III in the name “LEO J TRUMBLE, III” is inserted into the Applied column.

Again, I’m not a SQL guru/programmer so if you have suggestions for scripts, a lot of hand holding will be appreciated.

 

[gmmastros]

Do you have control over the file you are importing? Specifically... is this something you get on a regular basis from an outside source, or is this something you can change the format for whenever you want and in any format you want?

-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

 

[weigoldk]

It is an outside source. I can ask them to make changes to their export, however, it is somewhat important that we keep the imbedded commas if at all possible.

What did you have in mind?

 

[gmmastros]

There may be a couple ways to handle this...

1. If every piece of text had the quotes (for every row), then you could use a format file to bulk load the data. This would only work if the quotes were consistent on every row.

Example:

[tt]
9/11/2011,[!]"[/!]SANDY'S GIFTS[!]"[/!],1/30/2004
9/11/2011,[!]"[/!]LEO J TRUMBLE, III[!]"[/!],12/3/1999
9/8/2011,[!]"[/!]S & S EQUIPMENT[!]"[/!],7/4/1999
9/7/2011,[!]"[/!]JACKSTAR CO.[!]"[/!], 11/27/1996
9/11/2011,[!]"[/!]WALKER'S METAL ART[!]"[/!],4/6/2001
[/tt]

Format files can be a pain to work with sometimes, but once you get it formatted properly, it works really well.

Alternatively, you could save the data as an Excel file and then set up a linked server between SQL Server and the Excel file to get your data.

Either method would work well, but it all depends on changing the file that you want to import.


-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

 

[weigoldk]

I tried adding in quotes to my small sample, but that did not correct the issue. I now have the quotes in the fields (tried
SET QUOTED_IDENTIFIER ON and that did't help either).

My fields all have the " in them now and the III in Leo J Trumble, III still has his III put into the next column. This data is a bid different than the sample above because I simplifed for my first post.

Expires Dissolved BName Name Applied DisDate
9/11/2011 0 "SANDY'S GIFTS" "SANDY'S GIFTS" 9/11/2006 12/30/1899
9/11/2011 0 "LEO J TRUMBLE III" "LEO J TRUMBLE III",9/11/2006,12/30/1899
9/8/2011 0 "S & S EQUIPMENT" "S & S EQUIPMENT" 9/8/2006 12/30/1899
9/7/2011 0 "JACKSTAR CO." "JACKSTAR CO." 9/7/2006 12/30/1899
9/11/2011 0 "WALKER'S METAL ART" "WALKER'S METAL ART" 9/7/2006 12/30/1899

 

[gmmastros]

Take a look at this article:

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/title-12

Pay particular attention to the section about "bcp utility" and the format file.

Take note of the 5th column, which is the delimiter for each columns. The when you want to use a quote as part of your delimiter, you need to escape it with the back slash character \.

The format file you create can be used with the bcp utility or bulk insert (which is shown in the same article just below the bcp section).



-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

 

[weigoldk]

Thank you--I'm studying the document right now....

 

[weigoldk]

I spoke with our vendor and he was willing to change the commas to the piping symbol. I then changed FIELDTERMINATOR = '|' and it is working great.

I've since found out, (grrr) that a bulk insert might not be the complete solution. It turns out that I might have to filter the records as I bring them in. I don't think I can do a where clause (or any other filtering) with a Bulk Insert. Bulk Insert probably says it all--it is bulk. If anyone can confirm this, please add a reply.

I'm now looking for solutions where I do the bulk insert to a dummy table and then some kind of transfer over to our production table using a filter. Does this sound like I'm on the right track?

 

[gmmastros]

I'm now looking for solutions where I do the bulk insert to a dummy table and then some kind of transfer over to our production table using a filter. Does this sound like I'm on the right track?

This is very common. I know many DBA's that use this technique.



-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

 

[simian336]

in that case you could just take everything between the first comma and the second command and remove our quotes.

Simi