Data import files

Question

Data import files

Steve Jones - SSC Editor

SSC Guru

Points: 736557
More actions
February 20, 2017 at 11:16 pm

#74557

Comments posted to this topic are about the item Data import files

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72634 More actions · Answer 1

Good question, thanks Steve.
Had me stumped for a while....

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

George Vobr SSChampion Points: 10013 More actions · Answer 2

Tough and interesting question, but well specified and after some time, I happily found a good explanation
on MSDN in the article Data Formats for Bulk Import or Bulk Export (SQL Server). Thanks Steve for this question.

TomThomson SSC Guru Points: 104773 More actions · Answer 3

George Vobr - Tuesday, February 21, 2017 9:04 AM
Tough and interesting question, but well specified and after some time, I happily found a good explanation
on MSDN in the article Data Formats for Bulk Import or Bulk Export (SQL Server). Thanks Steve for this question.

Looking at that or at the page referenced in the explanation first seems odd to me, as the obvious place (for me, anyway) seems to be the - the question is effectively asking " what sort of input file can be specified by the DATAFILE parameter of TSQL's BULK INSERT command(/query/statement/whatever it's currently fashionable to call it)?

Tom

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 4

The answer given is a bit of hooie if you consider that these QOTD questions are typically based on the latest version documented in BOL. It'll import properly structured CSV and, if that's not properly structured, then it's just "character based" that needs some special care. Here's the quote from https://msdn.microsoft.com/en-us/library/ms188365.aspx

Input file format options

FORMAT = 'CSV'
Applies to: SQL Server vNext CTP 1.1.
Specifies a comma separated values file compliant to the RFC 4180 standard.

This is also why I don't take tests on the subject.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

I do BULK INSERT all the time, but not on SQL 2016 yet. I hope they didn't break what was already there and worked so well.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 6

Ed Wagner - Thursday, February 23, 2017 3:39 PM
I do BULK INSERT all the time, but not on SQL 2016 yet. I hope they didn't break what was already there and worked so well.

The CSV functionality won't be available in normal 2016. It will only be available in VNext initially. I'm kind of happy about that. Let them do their experiment in something other than what I'm setting up to use this year. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)