Automatically import ASCII text?

[ausmoran]

I have a fairly simple database (Access2000 Developers Edition) with a table with only six fields. Each month I will receive comma Delimited ASCII text files from around 40 offices to our FTP site. Each file will contain between fifty and two-thousand records.

As it is now, I am manually importing each file manually (one at a time) and running an append query to add the new data to the master table.

Is there any way that I can automate this process? Is there also a way that I can generate a table of those records that were not appended to the master table (if, for example they failed some validation check?)?

Thanks in advance. I am really a novice at this, so I think I need a step by step walk-through on how to do this.

Austin

 

[SmokingCrater]

Austin,

You have a couple of options. First, to automate your manual process a bit, first import the text file into Excel. Excel's got a great little wizard that takes the file (especially if you know how it's delimited) and converts into cells. From there, you can copy and paste much easier right into the table. (as one cell = one cell)

Second option, you can write a little program that takes the file, reads it in, and just does the append query over and over until all the records are inserted. Since you know the file is delimited, one line equals one record and you can chop the line up using string manipulation functions to get the different values. Then, just write a few lines of code that dynamically creates the append query with each record and you're set!

If you don't feel like writing a program, just import the file into Excel which will change your delimited file into cells and then you can just make an easy copy and paste from Excel to your database. One easy copy and paste and you're done.

Hope that helps.

-crater

 

[tlbroadbent]

You can ceate an import specification for the text files. Then create a VBA module to import the data, using the TransferText method of DoCMd. Run the query to append the data to the Master table using OpenQuery. Then run a query to find records on the import table that are not on the master. You can use the Find Unmatched Query Wizard to create this query. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.