Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Skipping Columns (& other tricks) Using BCP Format Files (SQL Spackle) Expand / Collapse
Author
Message
Posted Thursday, January 23, 2014 1:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:39 AM
Points: 101, Visits: 489
Apart from the fact that I needed this 4 years ago, excellent article.
Post #1534233
Posted Thursday, January 23, 2014 1:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 36,786, Visits: 31,244
Andy DBA (1/23/2014)
Great article! Exellent choice on where to draw the line on its scope.
Also, sometimes it's just as valuable to learn what can't be done as it is to learn what can be done because then you don't waste time trying to figure out how to do the impossible. So, thank you for confirming right away that BULK INSERT can't import spreadsheet files and for the reminder later on that BOL says using BULK INSERT to import CSV files is not supported.

Jeff Moden - Article
The real key is to get whomever or whatever is providing the data to provide it in a consistant manner just as you would do in a properly designed table.

So true, but easier said than done! Sometimes you gotta take what you get. I've recently had to strip text qualifier quotes from TSVs prior to BULK INSERTING and wondered if I was missing out on some new command line or format file option that was available since the last time I read any documentation. Sadly, I see the answer is "No".

I'm only going to mention one tiny correction in case you did it on purpose to see if anyone is paying attention:

]Jeff Moden - Article
For example, the first "format line" is supposed to be for "ColC" of the table but I've used "Doesn't matter" here because it truly doesn't matter what's in this column.

The posted example has "ColC", not "Doesn't matter". (BTW, I did not know this value was arbitrary, so thank you for pointing that out in your article.)


If the quotes are consistent in the column, then you are, in fact, missing out on a format file option that MS didn't document because they "don't support CSVs". \" is what you use as part of the FIELDTEMINATOR column to represent double quotes. Importing such CSVs using BCP and BULK INSERT will be the subject of another article.

Apologies for the error on the "Doesn't Matter" column notation. I had second thoughts about that and thought I had caught all of the places in the article that were based on that. Apparently, I missed one. I'll submit a correction for that. Thanks for bringing it to my attention.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534248
Posted Thursday, January 23, 2014 4:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 6, 2014 2:13 PM
Points: 147, Visits: 533
Jeff Moden (1/23/2014)
If the quotes are consistent in the column, then you are, in fact, missing out on a format file option that MS didn't document because they "don't support CSVs". \" is what you use as part of the FIELDTEMINATOR column to represent double quotes. Importing such CSVs using BCP and BULK INSERT will be the subject of another article.
Of course! Yes, I do remember coming across the approach where double quotes are included as part of the field terminator. There's also a way to "eat" the leading double quote from the first column, but I'll wait for your next article on the topic. I was able to take an approach where I didn't need format files, but I guess what I was really hoping for is a Magically-treat-double quotes-like-Excel-does command line option!



Post #1534292
Posted Thursday, January 23, 2014 6:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:02 PM
Points: 3, Visits: 6
I'm using SQL2012 and following your steps as carefully as possible.
I have copy and paste your example code and data.
When I run the BULK INSERT in Studio the result is error 'Cannot bulk load. Invalid column number in the format file...'
I have googled for more info but no-one seems to have an answer that makes sense to me.

Suggestions?
Post #1534312
Posted Thursday, January 23, 2014 6:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:02 PM
Points: 3, Visits: 6
Ignore my last.
When I copied the BULK INSERT statement I missed the last semi-colon. The error message was misleading.
Yes Jeff. Works like a charm and thanks for posting it.
Post #1534313
Posted Thursday, January 23, 2014 7:15 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 36,786, Visits: 31,244
Andy DBA (1/23/2014)
Jeff Moden (1/23/2014)
If the quotes are consistent in the column, then you are, in fact, missing out on a format file option that MS didn't document because they "don't support CSVs". \" is what you use as part of the FIELDTEMINATOR column to represent double quotes. Importing such CSVs using BCP and BULK INSERT will be the subject of another article.
Of course! Yes, I do remember coming across the approach where double quotes are included as part of the field terminator. There's also a way to "eat" the leading double quote from the first column, but I'll wait for your next article on the topic. I was able to take an approach where I didn't need format files, but I guess what I was really hoping for is a Magically-treat-double quotes-like-Excel-does command line option!


The old 32 bit "Jet" drivers used to work a treat for exactly that using OPENROWSET. Ii've not tried the 64 bit ACE drivers but rumor has it they they work just as well. The problem with OPENROWSET is that it requires some serious privs whereas BULK INSERT does not.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1534314
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse