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
 Site Related Forums
 Article Discussion
 Article: Using BULK INSERT to Load a Text File

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-03-18 : 18:23:52
This example combines dynamic SQL, BULK INSERT and the proper handling of double-quotes to solve a client's problem with loading various text file formats into a database.

Article Link.

ddoremon
Starting Member

1 Post

Posted - 2002-02-05 : 13:04:20
[quote]
ASK CS Timeout - Part 3
I call a stored procedure from ASP. My Server.ScriptTimeout is default 90 sec. I testing ADO time out by
Connection.CommandTimeout =1
Command.CommandTimeout = 1

But why my runrs is running until IIS time out... I think suppose ADO should be timeout first...That's what I want. Can you help me.

Thanks a bunch

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-06 : 11:32:25
There's no reason to set CommandTimeout to 1 second, if anything that will aggravate any timeout problems you are having. Set them back to 30 or 60 seconds and see if the timeout goes away.

Are you using a loop with rs.MoveNext and response.Write within the loop? I guarantee that if you are, this is where your timeouts are occurring.

Take a look at these links:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11019
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11833

I am a HUUUUUUUUUUUUUUUUUUUUUGE proponent of using GetString and GetRows in ASP pages. I heartily encourage you to play around and learn how to use them, it is probably the best way to create high-perfoming ASP pages that have heavy data requirements, and definitely worth your time. Follow all of the included links as well.

Go to Top of Page

Steve@CSC
Starting Member

6 Posts

Posted - 2002-07-29 : 17:30:33
quote:

You can import it into an Access database; Access handles quotes and embedded commas. You can then transfer it straight from Access or export it into a better text format.

Don't know if this is an option for you, but if someone sends me data in a format I can't use, I ask them to change formats. If they refuse, I let it be known that I can't do my job because the vendor/data provider isn't cooperating :) Unless they're using ancient software, it should be no problem for them to alter their format to your specs.





I'm surprised that SQL Server doesn't handle this situation (importing a quoted text field w/ embedded comma) since BOTH MS Access & FoxPro do...I've got the same situation with limited options on input file format. Another shame is that MS won't break the 2GB/Table glass ceiling on VFP. If we're still forced to 'upsize' to SQL, at least this simple import functionality should be respected.

(Sorry for griping, but this is an old sore spot with me & MS...)
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-29 : 20:46:38
Actually, you can do it, Garth has an article demonstrating it:

http://www.sqlteam.com/item.asp?ItemID=3207

And DTS can handle quotes too.

As far as 2GB table limit, that must only be FoxPro, SQL Server can handle more. MS always seemed to treat FoxPro as a bastard child anyway; I got the impression that they only included it in VS 6.0 to keep vocal FP supporters from complaining. It was very obvious even back then that MS was gonna throw their chips into VB and VC++ exclusively...and they didn't even do that, they went .Net!

Go to Top of Page

Steve@CSC
Starting Member

6 Posts

Posted - 2002-07-30 : 16:50:23
Thanks, but that wouldn't work in this case. Problem is that the comma field delimiter may be between nulls or numerics too, and the only time character input data HAS double-quotes surrounding it is when it contains an embedded comma.

Example:
Line1,7,,Happy Days,Formica,ThisLineIsFine
Line2,14,SomeText,Some More,"Twin, Evil",ThisLineChokesMeUp

Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-30 : 16:58:50
Use DTS then. You might have to write a custom transform task, but it's definitely possible.

Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-30 : 19:22:57
quote:

Example:
Line1,7,,Happy Days,Formica,ThisLineIsFine
Line2,14,SomeText,Some More,"Twin, Evil",ThisLineChokesMeUp

Steve,
Bear in mind that bcp/bulk insert is a low-level utility that trades features for speed. The above example isn't truly comma-delimited; escaping a field with quotes that has the delimiter character in it is common but outside the scope of bcp.

Moving data to and from SQL Server, especially when the other entity involved is a disparate data source, is nominally done with DTS. DTS does handle escaping the delimiter in that fashion.

Echoing Rob's comments on VFP, that product has not (never,imho) received the attention that VB or Access has, and certainly not the attention that SQL Server has. Moving to SQL Server is definitely upsizing - I'm curious what aspects of SQL Server you find disadvantageous compared to VFP.

Jonathan Boott, MCDBA
{0}
Go to Top of Page

Steve@CSC
Starting Member

6 Posts

Posted - 2002-07-31 : 14:46:29
Thanks, guys - I'll go with DTS.

My (unfounded) discontent is really born of lack of familiarity with SQL Server. Otherwise, I'm just a long-time Visual FoxPro advocate (harking back to old FoxPlus days), who never got Mr. Gates' ear.

Ah well - another lesson learned. Back to work!

Thanks again!
- S.
Go to Top of Page

Callaway
Starting Member

16 Posts

Posted - 2002-08-15 : 01:22:53
This article was a great help. I have added bulk import functionality to my Newsletter System (built using the Mail Queue Article!) Just to let you guys know, I built my script with a Cursor for the temp table. Why did I do this? I needed to add an Xref record to match new subscribers with the appropriate email list. It was easy. Just loop through your records checking for previous email address (as not to create duplicates). Then insert if the email address doesn't exist and use the @@Identity to create the Xref.

Good Luck,

Callaway



Edited by - callaway on 08/15/2002 01:23:46
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-15 : 03:58:35
quote:

Bear in mind that bcp/bulk insert is a low-level utility that trades features for speed.

I don't agree that this is a valid rationale. The code to deal with quoted CSV fields including quote-escaping is trivial and requires about 3 bits of state; the effect on speed would be negligable. The benefit of recognizing a data format generated by other pieces of Microsoft software would be significant.


Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-08-15 : 08:21:45
quote:

Bear in mind that bcp/bulk insert is a low-level utility that trades features for speed.
The above statement is true, though - bcp/bulk insert is faster than DTS and has less features. Yes I make the assumption that DTS is slower because it has more features; I suppose other factors could be in play but why not make DTS as fast as possible?

quote:
The benefit of recognizing a data format generated by other pieces of Microsoft software would be significant.
Using DTS it does, of course. As far as why MSFT chooses to not implement this sort of CSV parsing in bcp ... given the significant improvement in DTS from version 7 to 8, I wouldn't be surprised if Yukon continued the trend and bcp was discontinued altogether.

Not my most articulate response ever but it's early

Jonathan Boott, MCDBA
{0}
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-15 : 08:30:55
IMNSHO, the whole issue of quoted CSV files is very easily solved:

DON'T USE COMMAS AS A DELIMITER IF YOU HAVE COMMAS IN YOUR DATA!

I learned this so long ago I'd be embarrassed to say how long ago it was, but in truth I'm too old to remember how long ago it was. And if I had to guess I would imagine the MS coders who developed bcp had the same idea. I'm pretty sure the definition of a "delimiter" is some UNUSED, NON-DATA character that can always be discarded.

I don't see any valid rationale for using a quoted CSV file, especially if it creates the kind of issues discussed here. You really have to go out of your way to find software that DOESN'T allow a tab-delimited export, or some other delimiter character. Even dBase II could do it, PFS:Professional File, I mean, COME ON! Unless you're grabbing a file from some ancient mainframe (like ENIAC or Colossus) I don't agree that quoted CSV files are mandatory.

And just to throw another monkey wrench in there, whaddya do if your data has embedded quotation marks in it?????????

Edited by - robvolk on 08/15/2002 08:32:22
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-08-15 : 08:58:55
So how do you know in advance what character will never exist in your data?

quote:

And just to throw another monkey wrench in there, whaddya do if your data has embedded quotation marks in it?????????



You escape them.


Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-08-15 : 09:01:42
Having been quick on the draw this morning I read Fribble's first draft of his latest and was disappointed he didn't stick with it . Well, disappointed but somewhat relieved.

To perhaps steer this thread in another direction, any thoughts on MSFT removing bcp in future versions?

Jonathan Boott, MCDBA
{0}
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-08-15 : 09:09:11
Actually I would not be suprised to see BCP removed in future versions. When I was at the PASS conference in January I attended a presentation on bulk loading data. They said BCP was the slowest way to do it. The code for BCP hasn't changed since either 4.2 or 6 -- I can't remember which. As I think about that more, I don't think they'll remove BCP, they'll just tell you not to use it.

Oddly enough, BULK INSERT was much. much faster. They used the same format files and options so I assumed they were the same program under the hood. Apparently BULK INSERT uses a completely different code base to import data.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-15 : 09:14:09
They might, but they've included BULK INSERT into T-SQL, so the functionality will still exist. I worship DTS but many times it's not the right tool to use, especially in a stored procedure.

And I KNOW that tab characters don't exist in my data. I just know. They don't. Believe me, I know.

And I've got a challenge for you Arnold: the reason the + sign doesn't appear in the preview window is because of character escaping. If you can fix that, A) you're a hero, B) I'll cave in on quoted CSV files and recommend them to everyone I see.

To me, if I escape a character that can't be properly reconstructed, well, that means I'm losing data, and perhaps escaping is not a good thing after all. And I still think it's much easier to change a delimiter and completely avoid the need to escape ANYTHING.

Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-15 : 09:21:39
Boo-Woo-Woo .... comma's in my data .... question-marks in my data ...

Fixed-Width, yeah, that's the sh$t ... (bigger and slower, but works).

Graz, I have had mucho mucho problems with bulk insert in SQL 7. It seems to be connected to using sp_OA* procs and FSOs in other parts of my batch . . . I had to bag bulk insert in favor of a bcp solution, just to get it to run.

Jay White
{0}
Go to Top of Page

tiffchen
Starting Member

2 Posts

Posted - 2004-01-27 : 19:32:03
Hello,

I tried your BULK INSERT and it doesn't work right for me.

The query is:
BULK INSERT mytable FROM 'd:\myfile.csv' WITH (FIELDTERMINATOR = '","', FIRSTROW=2)

My data is like
"Apple","2","200","Sold"

After loading data into the database, the data in the table is like:
"Apple,2,200,Sold"

So the first double-quote and the last double-quote doesn't get stripped off.

Do you have this problem or is there any solution to fix it?

Many Thanks
Tiff

Go to Top of Page

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-01-28 : 11:07:44
I don't think you can include the double quotes in the field terminator and make it work. DTS might be a better solution for you. It will handle quoted fields.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

blueman
Starting Member

2 Posts

Posted - 2004-04-15 : 00:31:28
After reading this old thread (I was having this problem myself not long ago) I'm quite amused. For those who yell "DON'T PUT COMMAS..." blah blah blah, you should back off and realize we don't often get to choose. Several of our B2B partners export data from a mainframe in exactly this format. My bitching about the format won't get it changed.

Now that I've pissed some of you off, here is the answer I found:

You have to escape the quotes like this:

"\",\""

The last column has to be:
"\"\r\n\""

So your format file looks like this:

8.0
13
1 SQLCHAR 0 0 "\",\"" 0 FIELD1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 "\",\"" 0 FIELD2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\",\"" 0 FIELD3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 0 "\",\"" 0 FIELD4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 0 "\",\"" 0 FIELD5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 0 "\",\"" 1 CAssetID SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 0 "\",\"" 0 FIELD6 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 0 "\",\"" 0 FIELD7 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 0 "\",\"" 0 FIELD8 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 0 "\",\"" 0 FIELD9 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 0 "\",\"" 0 FIELD10 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 0 "\",\"" 0 FIELD11 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 0 "\"\r\n\"" 2 Status SQL_Latin1_General_CP1_CI_AS


Wasn't that easy?

Blueman
Go to Top of Page
    Next Page

- Advertisement -