delete a few lines in the text file before import

Question

Post reply

delete a few lines in the text file before import

Frances L

SSCarpal Tunnel

Points: 4727
More actions
May 11, 2006 at 7:45 am

#114637

I have text delimite file. It always have some headers to state when the file is created and how many records in the file. Are there any way in SQL data transform service delete the a few headers line. Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

In dts on the file property there is an option "First Row has column Names" this will only skip the first row.

If you use BCP then you can specify which row to start on.

-F3 will start on 3rd line.

JB-117464 Ten Centuries Points: 1279 More actions · Answer 2

I have a similar issue but with a footer saying the number of rows in the text file. Does anyone know how to drop the footer row without inserting into a stage table? I have tried activeX/script tasks to check for abnormal data in PK/knwon format columns (e.g. numbers in char columns etc) at the start of the row but they slow the import of multi-million row text files considerably.

The quickest method so far is to open a 500MB text file in a text editor and drop the last row manually!

Gaurav Kohli-326809 Newbie Points: 2 More actions · Answer 3

in DTS, instead of using the "First Row has column Names" which will skip only the first row -- If you have a text file source, then you can also specify the "Skip Rows" option to tell the DTS to start Importing from the nth Row. Hope this Helps

Gaurav Kohli-326809 Newbie Points: 2 More actions · Answer 4

for jb_lbm

in case you need to ignore the last few rows, you can import the entire file using DTS into a temporary table and then using a query, move only the required rows into the final table.

Normally, the header row may be identified as beginning with "01", Detail Row with "02" and footer data with "03"

you can use DTS to import the whole file into tbl_tmp_textfile_import

and then fire a query say

insert into tbl_textfile_data (select * from tbl_tmp_textfile_import where firstcol='02')

Hope you get the idea and can use this productively.

sql_Reporter SSCrazy Points: 2329 More actions · Answer 5

Hi guys i have the same problem regarding the text at the start of my file i want to discard it but then i do have my column headers, if i skip the first 6 rows in my case and then select the tick box (first row has column name) will this be correct in saying that the 7th row will now be counted as the 1st as in column headers? because i said skip the first 6?

Also my txt file contain double quotes example "text" "text" and am not getting it to import correctly any way around this ?

Thanks