Importing text files with a header row

Question

Importing text files with a header row

StefanJ

SSCarpal Tunnel

Points: 4713
More actions
September 3, 2004 at 8:14 am

#59409
I need to import a large number of large tab delimited text files into SQL Server. Would appreciate ideas on the best way of achieving this.
The files are in the following format:
- Tab delimited
- Header row indicates the filters e.g. date range etc.
- Second row contains the field headers. These can vary depending on what was requested, in the request screen.
- The final row contains two fields: DTLMLRTL #RecordCount
I can import these files using a Transform Data Task by skipping 2 rows and unchecking 'first row has column names'. Fortunately as the destination first field is numeric, the final row is not loaded.
I am hoping to do something like the following:
1. Ftp the files from Unix box to SQL Server box (using a dos ftp command?)
2. Examine the header row of each file in turn to determine type of file, which Transform Data Task, which destination table
3. Examine the second row to make sure the fields are in the right place
4. Delete existing data which meets the header criteria
5. Append the text files into existing tables.
6. Move the text file into an archive directory
7. Delete archived files older than x days

Viewing 3 posts - 1 through 2 (of 2 total)

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

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 1

Checkout BCP Format files. I'm pretty sure that with the BCP command line command and a format file you can skip the first two rows and the some at the end.

However, I'd recommend using some programming language outside of SQL server to ftp the files and examine the headers. DTS is really troublesome with things like this, and can often be more trouble than it's worth.

Use VBScript (if you must) Perl (if your a legend) or some other programming language to build a program that will read the headers from the files you download, pre-manipulate the data, and then call BULK INSERT or BCP to slurp the files in.

SQL Server is after a database server, not a file manipulation program. It excels at doing things with data that is inside it. It's hopeless at manipulating the outside world.

Julian Kuiters
juliankuiters.id.au

StefanJ SSCarpal Tunnel Points: 4713 More actions · Answer 2

Thanks for the reply.

I guess the scenario I outlined was the worst case.

The best case would be that my colleagues allow me to query our Oracle database direct from SQL Server. Unlikely I suspect....

Possibly they will agree to set up a Unix job which creates the text files and then ftp's them to my SQL Server machine.

The only program language I am really comfortable with outside of SQL Server is Visual Basic for applications. I would find it fairly simple to create and ODBC link in Access to the SQL Server backend and then do all the processing in VBA. Would start Access either from SQL Server or Windows Task Manager.

Although Access would be the easiest path I was hoping to keep this self contained within SQL Server.

I guess that I could use a VB Script to read the file. I did take a look at Bulk Insert but found it difficult to understand. In view of your comments, I will take another look.