Bulk Insert Question

Question

Post reply

Bulk Insert Question

SQL Dude-467553

Hall of Fame

Points: 3895
More actions
February 23, 2011 at 12:15 pm

#388922

I'm missing something very simple here:
I have a text file I want to Bulk Insert into a table.
Here is an example of the data, notice the center row is missing a record at the end:
0010 (tab) 345 (tab) 456 (tab) 9 (Return)
0010 (tab) 565 (tab) 457 (tab) 9 (Return)
0010 (tab) 345 (tab) 426 (tab) (Return)
0010 (tab) 345 (tab) 46 (tab) 9 (Return)
0010 (tab) 345 (tab) 46 (tab) 9 (Return)
When I import using SSIS the job completes just fine. In SSIS I have the format "Delimited", the "Row Delimiter" {CR}-{LF} and the "Column Delimiter" Tab{t} . If I try and use the BULK INSERT TSQL command the lines that do not have something in the last field end up with the previous line. It's like the format I'm selecting isn't correct and BULK INSERT sees those as one long row.
Here is the syntax I'm using below. I've tried a few combo's and have read the MS articles for syntax but can't catch my error here.
BULK INSERT MyDatabase.dbo.MyTable FROM 'c:\Import.txt'
WITH
(
DATAFILETYPE = 'char'
,FIELDTERMINATOR = '\t'
, ROWTERMINATOR = ''
,KEEPNULLS)

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

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

Lowell SSC Guru Points: 323506 More actions · Answer 1

this is the syntax I typically use;

looks the same as yours too me:(the forum hates{slash n} so you have to html escape it)

CREATE TABLE BULKACT(RAWDATA VARCHAR (50),MoreData varchar(50))

BULK INSERT BULKACT FROM 'c:\Export_o.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR = ' \t',

ROWTERMINATOR = '\n', --might be \r if this file came from a unix system!

FIRSTROW = 1

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL Dude-467553 Hall of Fame Points: 3895 More actions · Answer 2

Too bad... I was hoping I was overlooking something obvious. I keep looking at the syntax for "BULK INSERT" and the options in Books Online but nothing is jumping out at me. I could fall back and use SSIS I just prefered to do it with TSQL since that is the only step I could not complete in my script.

Paul White SSC Guru Points: 150467 More actions · Answer 3

Did you try Lowell's suggestion? The 'missing' column in your data should work just fine I think. I'm too lazy to set up a test myself - perhaps you could supply a small text file example, a CREATE TABLE statement to define the destination, and the exact BULK INSERT syntax you are using? That would make it easier for us to see what you see. Don't forget to be clear about the results you expect (particularly with regard to the empty column).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQL Dude-467553 Hall of Fame Points: 3895 More actions · Answer 4

Here is a text file I used as an example with the code that fails upon BULK INSERT.

I tried it with the code that Lowell left and it fails to import.

I also checked and the code Lowell wrote is identical to the code I have tried.

Carlton Leach SSCarpal Tunnel Points: 4202 More actions · Answer 5

Carlton Leach

SSCarpal Tunnel

Points: 4202

February 24, 2011 at 10:07 am

#1291091

What happens if you provide the missing value?

Carlton.

Paul White SSC Guru Points: 150467 More actions · Answer 6

SQL Dude-467553 (2/24/2011)
Here is a text file I used as an example with the code that fails upon BULK INSERT. I tried it with the code that Lowell left and it fails to import. I also checked and the code Lowell wrote is identical to the code I have tried.

Ok. This imports the sample file correctly, giving:

row1row1

row2row2

row3row3

row4

roow5row5

row6NULL

NULLrow7

GO

-- Minimally-logged bulk load with on-the-fly transformations

INSERT BULKACT

WITH (TABLOCK)

(

RAWDATA,

MoreData

)

SELECT CASE WHEN TAB.pos > 0 THEN LEFT(Src.Data, TAB.pos - 1) ELSE NULL END,

CASE WHEN TAB.pos < DATALENGTH(Src.Data) THEN SUBSTRING(Src.Data, TAB.pos + 1, 4000) ELSE NULL END

FROM OPENROWSET

(

BULK 'C:\Users\Paul White\Downloads\Export.txt', -- Input file

FORMATFILE = 'C:\Users\Paul White\Downloads\Format.xml', -- XML Format file

CODEPAGE = 'RAW',

FIRSTROW = 0,

LASTROW = 0,

MAXERRORS = 0,

ROWS_PER_BATCH = 0

) AS Src

CROSS

APPLY -- Or use a string-splitting routine

(SELECT CHARINDEX(NCHAR(9), Src.Data)) AS TAB (pos)

;

/*

====================================

XML FORMAT FILE CONTENT - Format.XML

====================================

<?xml version="1.0" ?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="01" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="100"/>

</RECORD>

<ROW>

<COLUMN SOURCE="01" NAME="data" xsi:type="SQLVARYCHAR" />

</ROW>

</BCPFORMAT>

*/

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SQL Dude-467553 Hall of Fame Points: 3895 More actions · Answer 7

SQL Dude-467553

Hall of Fame

Points: 3895

February 25, 2011 at 2:48 pm

#1291790

I will give this a try and let you know, thanks!