March 13, 2009 at 8:39 am
Hello
I'm trying to bulk insert data from a text file which has a header and footer into a table by calculating the lastrow dynamically and pass that as a variable to the Bulk Insert statement but some how it is not loading the last data row inspite of the header and footer being eliminated. The footer row in the source gives the total count of the rows in the file.
I even tried to do the alternative way of using a format file but it is also not working as required.
I would appreciate if any one of you has already had a workaround to this problem which will help me to smoothly construct a logic around this for processing daily a set of ~ 100 files in such format.
Format File
-----------
9.0
2
1 SQLCHAR 0 100 ";" 1 Code SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "" 2 ItemId SQL_Latin1_General_CP1_CI_AS
In the process of google search, I read in one of the post that the format file should have a carriage return at the end for it to work and hence the above has it.
Text File Data
--------------
Attached to this post (Sample.txt)
TSQL Code
---------
Declare @Lstrow int
SELECT @Lstrow = COUNT(1) FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=\\localhost\Source\;','SELECT * FROM Sample.txt')
select @Lstrow
Declare @BlkStr VARCHAR(1000)
Set @BlkStr = 'BULK INSERT tempdb..##Stage
FROM ''\\localhost\Source\Sample.txt''
WITH
(CODEPAGE = ''ACP'',
FIRSTROW = 2,
FIELDTERMINATOR ='';'',
ROWTERMINATOR ='''',
LASTROW = ' + CAST(@Lstrow - 1 AS VARCHAR(100)) + ')'
SELECT @BlkStr
EXEC (@BlkStr)
SELECT * FROM ##Stage
If you see the results from the above code execution, the last row count is 26 (excluding the header and including the footer) and while bulk inserting it I'm deducting 1 from the last row to make sure that it is eliminating the last row. In total it is only inserting 24 rows instead of 25 which is the correct count.
I would appreciate for any inputs on this issue.
Thanks
Lucky
March 14, 2009 at 5:25 am
If the footer text is static and unique within the file, you can import everything including the footer into the temp table and delete that row which pertains to the footer. Otherwise, you import all rows including footer into a temp table having an identity column and then delete the last record from that temp table.
Currently, I do not have SQL at my machine, so I couldn't able to check your code.
--Ramesh
March 17, 2009 at 1:09 pm
I've had trouble with format files in the past. Sometimes if you will place a newline on the last column that will do the trick.
9.0
2
1 SQLCHAR 0 100 ";" 1 Code SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "" 2 ItemId SQL_Latin1_General_CP1_CI_AS
Hope it helps-- Grady Christie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply