Search code examples
sql-servercsvflat-file

How to import flat file by skipping header and footer using Sql Server query

I want to import flat file using SQL Server query. Flat file is PIPE delimited with one HEADER row and one FOOTER row.

Flat file sample is as follows,

H|201501204|01
1|abc|123
2|efg|456
4|hij|789
T|03

in above file H and T are fixed defining HEADER and FOOTER respectively, '201501204' will be the date, '01'will be the flat file number, '03'will be count of data rows in flat file.

I have tried using Bulk Insert but I am losing one Data Row while importing Flat file. I am using FIRSTROW=2 and LASTROW=2651, number of data rows in my Flat file are 2650 but after importing i am getting only 2649 rows in table.

Solution

  • Use the BULK INSERT statement (see https://msdn.microsoft.com/en-us/library/ms188365.aspx), the option you want is FIRSTROW and set that to 2 (the 2nd row in the file). You can also use LASTROW.

    Alternatively load the data into a staging table first - single column and then split it in SQL Server having excluded the H and T by WHERE LEFT( input_line, 1 ) not in ( 'H', 'T' )