how do i insert an ascii text file to a table

Question

how do i insert an ascii text file to a table

david myers-292056

SSC-Addicted

Points: 413
More actions
February 8, 2006 at 8:37 am

#168223

Hi I have an ascii text file which I read and extract data from (actualy its a payslip print file from which i extract the ID number of each worker and the position of the payslip within the print file).
I then want to insert these records into an sql server table.
This is a monthly run and I insert about 5000 records per run.
Could someone please give me an outline of what I need to do. I'm running it from a vb.net windows app. Pointers to any good tutorials also appriciated
If I understand correctly I first need to create a disconnected Dataset and only when it's complete
update the table.

Thanks for any help

David

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

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Are you going to do this in Sql server, or in vb.net?

If vb then your going to have to read the file line by line, and parse the lines by the delimeter, and put the values in your dataset.

But if you use sql server, specifically BCP, or DTS, you can get the job done much quicker and easier. (Only if you do not have to impart business logic on the values of the data coming in. If you need complex logic, vb may be better choice)

Books Online about BCP

http://msdn2.microsoft.com/ms162802.aspx

Good SqlDts Site.

http://www.sqldts.com

David Korb SSCrazy Points: 2505 More actions · Answer 2

I recommend never applying complex logic during the data transfer. To import files, load your raw data into a buffer table then process it within a stored procedure. After cleaning and normalizing you can load into your production tables.

First, use DTS to get your buffer able created. Right click over the Tables icon in EM, choose import. Select text file as your source and your database as the destination.

Once you have created the buffer table (it now has your first set of data), modify the types and sizes of your columns to meet whatever requirements you have (sizes chosen by the import wizard may not be quite right).

Now lookup bulk copy (BCP) in books online and learn the required synax. Once you have a BCP script ready you can schedule a job to run it as needed. This is about the easiest way to get started!