Importing .txt to SQL 2000

[KevinS]

I receive a .txt sales report each month from our retailers with the following format…

A[Customer Name] [Customer ID] [Customer Address] [Customer Phone]
L[Customer ID] [Quantity] [UPC Number]
L[Customer ID] [Quantity] [UPC Number]
...
A[Customer Name] [Customer ID] [Customer Address] [Customer Phone]
L[Customer ID] [Quantity] [UPC Number]
L[Customer ID] [Quantity] [UPC Number]
...

I get one file per retailer per month, so each file would be its own import/batch.

I am trying to import these files into SQL 2000 and populate 3 tables; batch, customer and line-item. However, since there is no delimiter (the fields are defined by char length), I do not know how to parse out the data. I also do not know how to separate the file into the different tables.

Any help would be greatly appreciated.

Thanks

[ck9663]

Use format file in BCP. For more of format file, read this

Happy coding!

-- CK

[KevinS]

OK, I see how BCP can be a HUGE help to me on this, thanks. However, I do not see how BCP can separate the customer data line from the following line items.

If I am not mistaken, BCP assumes the entire file is essentially a single table, which my files are not.

Thanks again!!

[ck9663]

Read the part about the -f switch.

It allows you to pass a format file. Format file can specify the structure of the file and table you are trying to upload.

Good luck.

--- CK