SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using BULK INSERT to Load a Text File
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 4

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 07/30/2002 :  16:58:50  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
992 Posts

Posted - 07/30/2002 :  19:22:57  Show Profile  Reply with Quote
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 - 07/31/2002 :  14:46:29  Show Profile  Reply with Quote
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 - 08/15/2002 :  01:22:53  Show Profile  Reply with Quote
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

United Kingdom
1961 Posts

Posted - 08/15/2002 :  03:58:35  Show Profile  Reply with Quote
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

USA
992 Posts

Posted - 08/15/2002 :  08:21:45  Show Profile  Reply with Quote
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

USA
15655 Posts

Posted - 08/15/2002 :  08:30:55  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

United Kingdom
1961 Posts

Posted - 08/15/2002 :  08:58:55  Show Profile  Reply with Quote
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

USA
992 Posts

Posted - 08/15/2002 :  09:01:42  Show Profile  Reply with Quote
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

USA
4137 Posts

Posted - 08/15/2002 :  09:09:11  Show Profile  Visit graz's Homepage  Reply with Quote
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

USA
15655 Posts

Posted - 08/15/2002 :  09:14:09  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 08/15/2002 :  09:21:39  Show Profile  Reply with Quote
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 - 01/27/2004 :  19:32:03  Show Profile  Reply with Quote
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

USA
4137 Posts

Posted - 01/28/2004 :  11:07:44  Show Profile  Visit graz's Homepage  Reply with Quote
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 - 04/15/2004 :  00:31:28  Show Profile  Reply with Quote
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

blueman
Starting Member

2 Posts

Posted - 04/15/2004 :  00:48:26  Show Profile  Reply with Quote
quote:
Originally posted by robvolk
...keep vocal FP supporters from complaining.



You haven't been around nearly as long as some of your posts would indicate. A lot of serious, high-quality code was written in Fox by some very talented people. Everything from accounting applications to large projects involving satellite tracking for NASA.

I wrote in it exclusively from 86 until 94 and made a nice living at it. Switched to VB, then C#/.NET and haven't looked back.

You're right. Microsoft basically killed it by steering every people down the VB path. But then, FP/VFP was never a beginner's toy. That was probably its demise more than anything. And most of those wannabes are back to selling cars or whatever since the bust. Back to the big boys world again.

Blueman
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 05/12/2004 :  08:31:51  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
You haven't been around nearly as long as some of your posts would indicate.
Ha! I was a die-hard Clipper programmer back then. FoxBase (before MS bought it) was the new kid then, and just started coming up into FoxPro. I know what it's capable of, I just never had a chance to use it.

And I tell ya, I do miss Clipper tremendously sometimes.
Go to Top of Page

Frank_Galvin
Starting Member

3 Posts

Posted - 06/08/2005 :  14:56:44  Show Profile  Reply with Quote
I'm getting an error when trying to BULK INSERT from a text file (pipe delimited) "Bulk insert data conversion error (type mismatch) for row x, column x". The column coming in is a date field but in the dd-mmm-yy format (example: 17-APR-04). The damn thing works on one SQL Server but I can't get it to import on my other SQL Server. Can't understand why it would work in one place but not in another (same text file!!).

The destination field is datetime and the format file is using the proper host file data length of 24 (as specified for datetime fields in the documentation for format files).

Any ideas/suggestions?

Thanks.
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 06/09/2005 :  07:15:31  Show Profile  Reply with Quote
Different COLLATIONS (or REGIONAL settings) on the different SERVERS/DATABases???
Go to Top of Page

Frank_Galvin
Starting Member

3 Posts

Posted - 06/09/2005 :  09:11:28  Show Profile  Reply with Quote
The text file is a delimited file given to me from the Oracle group.
Go to Top of Page
Page: of 4 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000