Help with BULK INSERT?
-
SQL 2000, SP3
I'm trying to load in a comma delimited text file where the row delimiter is ASCII character 10 (I've checked this with a hex editor). The first row in the text file contains field headers. I am using the code:
BULK INSERT preneed.dbo.tbl_globalir_load_data
FROM '\\dcfcolifs01\shared\everyone\gir_load.csv'
WITH (
DATAFILETYPE='char',
FIELDTERMINATOR=',',
ROWTERMINATOR = '''+CHAR(10)+''',
FIRSTROW=2
)
This loads no data. If I specify FIRSTROW=1, the column headers are loaded into the correct fields, with all the data concatenated into the last field, i.e. the program is not recognising the row delimiter. I've tried ROWTERMINATOR='\r' and '\n' with the same results. The text data fields are delimited with " - I was planning to strip these out later.
This looks pretty straightforward, but I can't see where I've gone wrong.
Thanks for any help
--
Scott -
You cannot use LF (ascii 10) as a separator for either BULK INSERT or bcp, see 'terminating characters' under bcp in BOL, quote
When you use bcp interactively and specify \n (newline) as the row terminator, bcp prefixes the \r (carriage return) character automatically.
Far away is close at hand in the images of elsewhere.
Anon. -
Drat. Have to find a plan B then. I had read that but it didn't sink in. Brain too hot.
Thank you.
--
Scott -
Scott,
Post the first 10 records of data (including the header) and let me take a crack at it tonight for ya... I think there may be a couple of ways to skin this cat...
--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)
Intro to Tally Tables and Functions -
Thanks for the offer Jeff. I've got back to the data suppliers and they've agreed to make the row terminator carriage return + line feed, which should do the trick. If you still want to get your teeth into something....
These are test data and I only have seven rows to play with - they relate to applications for pre-paid funeral plans, in case the references to cremation raised an eyebrow.
"ref","referrer","title","titleother","forename","surname","address1","address2","town","county","postcode","telephone","dateofbirth","gender","email","proxytitle","proxytitleother","proxyforename","proxysurname","proxyaddress1","proxyaddress2","proxytown","proxycounty","proxypostcode","proxytelephone","proxyemail","planchoice","cremationorburial","specialrequest","specialrequestinfo","optin","termsandconditions","completed","exported","xl_transaction","xl_amount","xl_response_code","xl_auth_code","created","active"
"88","","Mrs","","Lesley","Leon","Plantsbrook House","94 The Parade","Sutton Coldfield","west mids","B72 1PH","0121 354 1557","1896-08-06","Female","lesley.leon@dignityuk.co.uk","Mr","","","","","","","","","","","The Windermere - Single Payment","Cremation","false","","true","false","true","false","193010711253","2445.00","00","5433","2006-07-13 13:53:27.275881","true"
"87","","Mrs","","Lesley","Leon","Plantsbrook House","94 The Parade","Sutton Coldfield","West Midlands","B72 1PH","0121 354 1557","1921-04-03","Female","lesley.leon@dignityuk.co.uk","Mr","","","","","","","","","","","The Derwent - 12 Months","Cremation","false","","false","false","true","false","193120723843","95.00","00","087019","2006-07-13 12:54:01.476424","true"
"86","","Mrs","","Lesley","Leon","Plantsbrook House","95 The Parade","Sutton Coldfield","West Midlands","B72 1PH","0121 354 1557","1904-10-07","Female","lesley.leon@dignityuk.co.uk","Mr","","","","","","","","","","","The Windermere - Single Payment","Cremation","false","","false","false","true","false","193120746376","2445.00","00","3602","2006-07-13 12:30:55.456891","true"
"85","","Mr","","Jason","Barnes","Plantsbrook House","94 The Parade","Sutton Coldfield","West Midlands","B72 1PH","0121 354 1557","1897-03-04","Male","lesley.leon@dignityuk.co.uk","Mr","","","","","","","","","","","The Derwent - Single Payment","Cremation","false","","false","false","true","false","193120718882","1995.00","00","076976","2006-07-13 12:03:55.170321","true"
"78","","Mr","","Garry","Marsland","24121","ewrwe","dsa","fsa","fs","fsa","1895-01-01","Male","garry.marsland@g-ir.com","Mr","","","","","","","","","","","The Windermere - 12 Months","Cremation","false","","true","false","true","false","","","","","2006-07-13 10:31:37.740802","true"
"74","","Mr","","Jason","Barnes","Plantsbrook House","94 The Parade","Sutton Coldfield","west mids","B72 1PH","0121 354 1557","1904-10-10","Male","lesley.leon@dignityuk.co.uk","Mr","","","","","","","","","","","The Derwent - Single Payment","Cremation","false","","false","false","true","false","179110638591","1995.00","00","928940","2006-06-29 11:07:21.240275","true"
"70","","Mrs","","Lesley","Leon","12 Anytown","","sutton coldfield","west midlands","b72 1ph","0121 354 1557","1904-10-10","Female","lesley.leon@dignityuk.co.uk","Mr","","","","","","","","","","","The Derwent - Single Payment","Cremation","false","","false","false","true","false","158050630496","1995.00","00","325731","2006-06-08 17:11:14.339251","true"
"69","","Mrs","","Lesley","Leon","12 Anytown","","sutton coldfield","west midlands","b76 4rt","0121 354 1577","1899-09-05","Female","lesley.leon@dignityuk.co.uk","Mr","","","","","","","","","","","The Derwent - 36 Months","Cremation","false","","false","false","true","false","158030614317","95.00","00","257987","2006-06-08 15:14:51.814473","true"
--
Scott -
If the suppliers of the data have agreed to provide the data with correct row delimiters,
I guess I don't need to do much. Too bad... I was "dying" to work on it
--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)
Intro to Tally Tables and Functions -
Gave up so soon Jeff
And there I was looking forward to your solution
Far away is close at hand in the images of elsewhere.
Anon. -
All the help offered is appreciated. I haven't actually had a modified file yet, so may be back...
--
Scott -
In that case, I'll give it a whirl... I was just "busy"...
--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)
Intro to Tally Tables and Functions -
Do you read only first lines from help topics?
Read again and this time pay some attention to sentence about FORMATFILE.
Than go by the link "Using Format Files" and read closely, find the link "Field Terminator" and look where it describes the difference between row terminators specified in BULK INSERT statement and in FORMAT FILE.
Hope it will help.
_____________
Code for TallyGenerator -
That's kinda where I was going to take it but Scott already beat the heck out of his data provider...
--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)
Intro to Tally Tables and Functions -
Ok folks... Dave and, I'm sure, Scott will be interested in this...
First, Scott posted what I call "QCSV" or "Quoted Comma Separated Values". This is a format where the double quotes allow for embedded commas in any given field so you can have names like "SQLServerCentral.com, Inc" without having your import go nuts because of the embedded comma...
...and it drives folks nuts especially when they try to do the import with BCP or Bulk Insert because of the single leading quote. It can't be considered a delimiter and most people end up importing the character and then stipping it out with the REPLACE function... that's IF they figure out how to define the delimiter as "," in other double quotes.
Just to keep the example simple, here's the test data I used...
"ID","FirstName","Initial","LastName","Age"
"1","Edgar","R","Burrows","79"
"2","John","","Carter","39"
"3","Tarzan","","","24"
Notice that the included header must have the exact same format as the data or BCP will loose it's lunch and reject both the header and the first line of data. I don't have an editor that I can copy from but the end of each line is terminated only with the NewLine charater (hex 0A, decimal CHAR(10)).
Here's the table I imported to...
CREATE TABLE dbo.ImportData
(
ID INT,
FirstName VARCHAR(25),
Initial VARCHAR(25),
LastName VARCHAR(25),
AGE TINYINT
)
...and here's the real trick... the BCP file that's required to do the import...
8.0
6
1 SQLCHAR 0 1 "" 0 LeadingQuote ""
2 SQLCHAR 0 100 "\",\"" 1 ID ""
3 SQLCHAR 0 100 "\",\"" 2 FirstName ""
4 SQLCHAR 0 100 "\",\"" 3 Initial ""
5 SQLCHAR 0 100 "\",\"" 4 LastName ""
6 SQLCHAR 0 100 "\"\n" 5 Age ""
You can read up on what the various columns are in Books Online under "bcp utility, format files, using format files" but I want to point out a couple of things...
Notice that the Leading Quote row has a width of 1 character and will be input to column 0 of the table which, of course, does not exist so the data is ignored. The leading quote goes the way of the proverbial bit bucket.
Notice also how the "," delimiter is formed... each quote is prefixed with a \ as an escape character.
Notice also the last line where the NewLine character is represented by the \n combo of characters. Of course, there's a quote at the end of each line so it needed to be included as well.
Because the fields in the source file are delimited, it doesn't matter what the width of the rest of the columns are so long as the number is bigger than what's in the table. Why? So if you get a larger than expected input, it will fail and not be inserted into the table in a possibly truncated fashion.
Last but not least, here's the BCP command I used...
BCP dbname.dbo.ImportData IN C:\Temp\ImportData.txt -fC:\Temp\ImportData.bcpfmt -F2 -Sservername\instancename -Uloginname -Ppassword
Of course, that command needs to go in on a single command line. If you look real close, you can probably figure out what file/table names I used are but you can name them just about anything. You'll need to make substitutions for dbname and the login info, as well. Again, checkout Books Online to find things like the -F2 parameter which says to start the import at line 2, effectively skipping the header (BUT MUST STILL BE IN EXACT SAME FORMAT AS THE OTHER LINES IN THE FILE).
If your file lines are terminated with CrLf, then just add a \r before the \n in the BCP format file.
By the way, here's the output from my BCP run just to give all of you the warm fuzzies that it actually works...
Starting copy...
3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 47
The same format file will work from BULK INSERT so you don't necessarily have to bother with the CMD prompt.
Questions?
--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)
Intro to Tally Tables and Functions
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply