Bulk Insert skipping footer!

SqlBeginner

unread,

Jul 30, 2008, 1:57:00 AM7/30/08

to

Hi,

I have provided a Sample flat file structure which I am trying to automate
using Bulk Insert.

Col1|Col2|Col3
a|b|c
aa|bb|cc
99|Foot|2

First row is the header and the last row is the footer. I want to skip both
of them and insert this into a table.

I guess for header i can make use of 'FirstRow' attribute ... but how can i
ignore the footer.

FYI, approx there would be 25K to 50K of rows in each data file which I am
trying to automate.

Regards
Pradeep

amish

unread,

Jul 30, 2008, 3:09:25 AM7/30/08

to

On Jul 30, 10:57 am, SqlBeginner

Pradeep
you can use Lastrow parameter

Lastrow Specifies the number of the last row to load. The default is
0, which indicates the last row in the specified data file

This works pefectly for me

bulk insert t1
from 'C:\test.txt' with (firstrow = 2,lastrow = 3,FIELDTERMINATOR
='|',
ROWTERMINATOR ='\n'
)

Thanks and Regards
Amish Shah

http://shahamishm.blogspot.com

Bob

unread,

Jul 30, 2008, 7:52:01 AM7/30/08

to

For the first row, obviously use FIRSTROW = 2.

For the last row, if you don't know the number of rows in the file, or it
can change, the easiest thing is to just import the row, then DELETE it
afterwards.

If you know the number of rows in the file then you can use LASTROW.

SqlBeginner

unread,

Jul 30, 2008, 12:36:01 PM7/30/08

to

Thanks for response Bob. Yep as of now I am importing everything and then
deleting the last row only. But i want to know whether i can set the LastRow
'dynamically' by any means?

Regards
Pradeep

Erland Sommarskog

unread,

Jul 30, 2008, 6:34:51 PM7/30/08

to

SqlBeginner (SqlBe...@discussions.microsoft.com) writes:
> Thanks for response Bob. Yep as of now I am importing everything and
> then deleting the last row only. But i want to know whether i can set
> the LastRow 'dynamically' by any means?

The only way I can think of is to first import the table into a one-column
table, count the rows you get, and then import the table for real. Which
is not really appetising.

Funny, I have not seen this request very often, but it seems like fairly
normal thing to do. Something LASTROW = -2 to indicate a row from the end
would be natural. Maybe you should put it on
http://connect.microsoft.com/sqlserver/feedback?

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Bob

unread,

Jul 31, 2008, 6:49:01 AM7/31/08

to

Obviously these things are possible, but is it worth it? ; )
SQL 2005 example below:
/* NB This is a sqlcmd script which must be run in SQL Server Management
Studio in sqlcmd mode.
Do this via the Query menu, and click 'SQLCMD Mode', or use the icon toolbar.
Lines prefixed with !! should then appear in grey.
*/

-- You could do this with xp_cmdshell
!!type c:\temp\temp.sql|find /i /c /v "they'll never find this" >
c:\temp\temp.txt
GO

-- Pick up the file
DECLARE @int INT

;WITH cte AS ( SELECT 0 x UNION ALL SELECT x + 1 FROM cte WHERE x < 9 )
SELECT @int = SUBSTRING( y, min_pos, xlen )
FROM
(
SELECT
x.y,
PATINDEX( '%[0-9]%', x.y ) AS min_pos,
MAX( CHARINDEX( CAST( c.x AS CHAR(1) ), x.y ) ) AS xlen
FROM cte c CROSS JOIN OPENROWSET( BULK 'c:\temp\temp.txt', SINGLE_CLOB ) x(y)
GROUP BY x.y
) z

SELECT @int