| 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 =1Command.CommandTimeout = 1But 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 |
 |
|
|
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=11019http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11833I 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. |
|
|
|
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...) |
|
|
|
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=3207And 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! |
|
|
|
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,ThisLineIsFineLine2,14,SomeText,Some More,"Twin, Evil",ThisLineChokesMeUp |
|
|
|
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. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-30 : 19:22:57
|
quote:
Example:Line1,7,,Happy Days,Formica,ThisLineIsFineLine2,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} |
|
|
|
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. |
|
|
|
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,CallawayEdited by - callaway on 08/15/2002 01:23:46 |
|
|
|
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. |
|
|
|
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} |
|
|
|
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 |
|
|
|
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. |
|
|
|
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} |
|
|
|
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. |
|
|
|
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. |
|
|
|
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} |
|
|
|
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 ThanksTiff |
|
|
|
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. |
|
|
|
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.0131 SQLCHAR 0 0 "\",\"" 0 FIELD1 SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 0 "\",\"" 0 FIELD2 SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 0 "\",\"" 0 FIELD3 SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 0 0 "\",\"" 0 FIELD4 SQL_Latin1_General_CP1_CI_AS5 SQLCHAR 0 0 "\",\"" 0 FIELD5 SQL_Latin1_General_CP1_CI_AS6 SQLCHAR 0 0 "\",\"" 1 CAssetID SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 0 0 "\",\"" 0 FIELD6 SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 0 0 "\",\"" 0 FIELD7 SQL_Latin1_General_CP1_CI_AS9 SQLCHAR 0 0 "\",\"" 0 FIELD8 SQL_Latin1_General_CP1_CI_AS10 SQLCHAR 0 0 "\",\"" 0 FIELD9 SQL_Latin1_General_CP1_CI_AS11 SQLCHAR 0 0 "\",\"" 0 FIELD10 SQL_Latin1_General_CP1_CI_AS12 SQLCHAR 0 0 "\",\"" 0 FIELD11 SQL_Latin1_General_CP1_CI_AS13 SQLCHAR 0 0 "\"\r\n\"" 2 Status SQL_Latin1_General_CP1_CI_ASWasn't that easy?Blueman |
|
|
|
Next Page
|
Article: Using BULK INSERT to Load a Text File