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 Wednesday, January 22, 2014 10:14 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 35,618, Visits: 32,214
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."

(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 #1533906
Posted Thursday, January 23, 2014 6:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:55 AM
Points: 13,570, Visits: 11,383
Always funny to see what you categorize as a "short" article Jeff
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1534037
Posted Thursday, January 23, 2014 6:59 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 35,618, Visits: 32,214
Koen Verbeeck (1/23/2014)
Always funny to see what you categorize as a "short" article Jeff
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."

(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 #1534057
Posted Thursday, January 23, 2014 7:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, November 17, 2014 3:55 PM
Points: 320, Visits: 362
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.ITBookworm.com

Read my book reviews at:
www.ITBookworm.com

Blog Author of:
Database Underground -- http://www.infoworld.com/blogs/sean-mccown
DBA Rant – http://dbarant.blogspot.com
Post #1534058
Posted Thursday, January 23, 2014 7:47 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 35,618, Visits: 32,214
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."

(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 #1534088
Posted Thursday, January 23, 2014 7:54 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 6:43 AM
Points: 1,482, Visits: 8,485
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.


BrainDonor
Linkedin
Blog Site
Post #1534096
Posted Thursday, January 23, 2014 8:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 6:03 AM
Points: 30, Visits: 404
Thanks, I have almost never used this tool so your article was particularly informative.
Post #1534125
Posted Thursday, January 23, 2014 8:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:20 PM
Points: 3,943, Visits: 8,951
Thanks for the article Jeff. It's always good to have a nice explained reference.


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1534139
Posted Thursday, January 23, 2014 9:27 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:55 PM
Points: 35,618, Visits: 32,214
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."

(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 #1534162
Posted Thursday, January 23, 2014 10:57 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, November 21, 2014 8:17 AM
Points: 147, Visits: 548
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.)



Post #1534197
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse