Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Skipping Columns (& other tricks) Using BCP Format Files (SQL Spackle)


Skipping Columns (& other tricks) Using BCP Format Files (SQL Spackle)

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44896 Visits: 39857
Comments posted to this topic are about the item Skipping Columns (& other tricks) Using BCP Format Files (SQL Spackle)

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16366 Visits: 13199
Always funny to see what you categorize as a "short" article Jeff :-D
It's a great one though and I'm sure I'll refer to it soon in the future when I or someone else is dealing with flat files.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44896 Visits: 39857
Koen Verbeeck (1/23/2014)
Always funny to see what you categorize as a "short" article Jeff :-D
It's a great one though and I'm sure I'll refer to it soon in the future when I or someone else is dealing with flat files.


BWAAAA-HAAA!!!! I guess after some of the 26 page marathon articles I've written, my measuring stick is all messed up. Thanks for the feedback, Koen. :-)

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
KenpoDBA
KenpoDBA
SSC-Addicted
SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)SSC-Addicted (464 reputation)

Group: General Forum Members
Points: 464 Visits: 610
I love any piece that ends with "crack filled".
Good job on this one. It's to the point and gives good practical advice.
I notice though that with everyone who talks about this topic, nobody ever
says what the space between the cols is in the format file.
Is it a tab, a couple spaces, can it be mixed, etc?
So in your example would it be:
1<space><space>SQLCHAR
1<tab>SQLCHAR
1<tab><space>SQLCHAR

Or can it just be any whitespace we want...

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44896 Visits: 39857
KenpoDBA (1/23/2014)
I love any piece that ends with "crack filled".
Good job on this one. It's to the point and gives good practical advice.
I notice though that with everyone who talks about this topic, nobody ever
says what the space between the cols is in the format file.
Is it a tab, a couple spaces, can it be mixed, etc?
So in your example would it be:
1<space><space>SQLCHAR
1<tab>SQLCHAR
1<tab><space>SQLCHAR

Or can it just be any whitespace we want...


Good point. I've never tried tabs (I avoid tabs even in code because of the different tab sizes of whatever reader someone is using) but I would imagine that tabs would work as the correct "white space". I'll try it after work and see.

Thanks for the feedback.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2014 Visits: 11167
And here I was, working out how to skip a column because I've been sent a csv file with 16 columns intended for a table that has 15...

Thank you for a well-explained article.

Steve Hall
Linkedin
Blog Site
batesview
batesview
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 422
Thanks, I have almost never used this tool so your article was particularly informative.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8474 Visits: 18082
Thanks for the article Jeff. It's always good to have a nice explained reference.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44896 Visits: 39857
Thanks folks. Heh... I aim to please... I sometimes miss but I'm always aiming. :-)

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Andy DBA
Andy DBA
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 753
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.)



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search