how to import text file into tables

[purplehaze1]

I want to write routine to import text file into sql server database tables.
Here's a sample text file and the table structure where I want to import it to.

H;11/11/2003;00201725;12960.00
D;001111;11/13/2003;18.00;010
D;002222;11/14/2003;30.00;010
D;003333;11/05/2003;22.50;010
D;004444;11/25/2003;22.50;055
D;005555;11/11/2003;22.50;055
D;006666;11/19/2003;22.50;055
D;007777;11/06/2003;22.50;067
D;008888;11/11/2003;22.50;090

Here's the table structure:

Invoice_header:
--------------
InvoiceKey (PK, identity field)
InvoiceDate
InvoiceNumber
InvoiceAmount


Invoice_SubHeader:
------------------
InvoiceSubKey (PK, identity field)
InvoiceKey (FK)
vendorID


Invoice_Detail:
----------------
InvoiceDetailKey (PK, identity field)
InvoiceSubKey (FK)
itemId
purchaseDate
purchaseAmt


When imported, the header line (1456;11/11/2003;00201725;12960.00) indicated by 'H' will goes to
the Invoice_header table. The last field (vendorId) in 'D' line (i.e. 010, 055, 067, 090) goes to
Invoice_Subheader table (there can be only one 010 or 055, no duplicates).
Rest of the 'D' lines (i.e. 000351296;11/13/2003;8.00 ...) goes to Invoice_Detail table.

If the following file is imported succesfully,

header table will contain 1 header record.
sub-header table will have 4 vendorID (010, 055, 067, 090).
detail table will have 8 records for each itemID.

How can I routine to do something like this? I'd appreciate your help very much. Thanks.

 

[RebLazer]

It's not writing a routine like you asked, but the first thing that comes to mind is to use the Import Wizard to make a DTS (from Enterprise Manager) to do it. It can parse text files into SQL Server.

Lazer

 

[bigfoot]

Use Redim Preserve donorCenter(j)


Hope this helps.

 

[purplehaze1]

Thanks.