Selective Text ImportTo SQL Database

[PaulSc]

Hi,
I'am after some info (again!) please...

I have a text file which I currently import via a DTS package into a SQL table.(Using a DOS script....)

My problem is that the file contains a header record, numerous fixed position data fields and a trailer record.

header11223q
data1 222 333 44 555
data2 222 333 44 555
Trailer2223400753

I need to be able to drop the header/trailer when I import as these fields dont agree to the DTS/table format and it merges the header/data1 into one field and dosent import it correctly as it throws all the fields out.

Can anybody suggest a way of automatically getting rid of these records prior to importing?? or suggest another way of getting the data loaded??

I've tried importing the complete table into SQL but it seems to loose its formatting..

Any suggestions gratefully received.

Regards

PaulSc.

 

[ClaireHCM]

The usual technique for importing text file
1. First importing into a temp table, do cleanup and format on the temp table, then bring the temp table into target table.Usually we will build the temp table alone with a identity filed to make our life easier.

2.Use DTS - ActiveX (overhead and complicated) to read thru the text file, delete the first and last row, then bring the text file either directly to target table or temp table for transformation.

3. My thought: Why not just delete the first and last row in the text file befor eimporting, why we need to have SQL Server do work for us. It's really not a big deal right.

 

[PaulSc]

ClaireHsu,
Thanks for replying.

The third suggestion is exactly what I'm trying to do. we currently do it manually but having automated the res of the process we would like to this as well...

Any ideas how we may achieve this??

The Activex option looks good but we are not sure how it works!

The text field is fixed with data, however when you import it with no field breaks into SQL it throws some of the lineups out.