Importing Text Files

[toon10]

I have a customer-based system written in VB6, which is coming on nicely. For the next stage, I need to be able to import a comma separated text file as one is sent to the user on a weekly basis. At the minute I’m using Access so I need some code or articles on the standard Access “File” “Get External Data” “Import” wizard as I can just use that rather than trying to be clever and write something myself.

I will be uploading this into SQL Server 2000 at some point so I’ll have to change my data links and also this import functionality. Does anyone know of a similar method of achieving this with SQL?

Any help would be much appreciated
Thanks
Andrew

 

[mmilan]

I have a class at home I could give you for decoding a line on a CSV - gives you an easy way of accessing the "columns"...

Mail me on martin at mjmilan dot com if you're interested...

I'm not trying to sell anything - you can just have it...

mmilan.

 

[dragony]

I'm not sure if I understand you correctly. But if you are indeed trying to do this in Access (and not VB), then here's something I've done to import a text file into a table.

DoCmd.TransferText acImportFixed, "CommissionSpec", "tblImport", "C:\data\commission.txt", False

This is to import a fixed-width (acImportFixed) file (c:\data\commission.txt) into a table (tblImport) using an import specification (CommissionSpec) that I've created using import wizard in Access.

In your case, you'll have to use "acImportDelim" instead of "acImportFixed". The "False" at the end indicates that there is no column header in the data file.

I think that will work for you. Although, if you are using VB to do this, then I'm not sure if there is a similar command or not.

Good luck,
Chutikorn

 

[toon10]

OK thanks guys, I'll give it a go.

 

[Glasgow]

Investigate BULK INSERT under SQL Server (Transact SQL). If you are loading data into a table, this could achieve it in a single line of code.