Importing multiple text files 1

[tekkyun]

Hi,
I need to import data from numerous txt files held under different names in a single directory to a single table.

The txt files are all formatted exactly the same, have header information in rows 1-3 (part no, serial no, date) which I would like to pick up. The main data starts on line 4 (tab separated).

Does anyone know of a way of coding this to automate the task?

 

[Tranman]

Tekkyun,
You will need to process the first 3 records individually, then process the 4th and all subsequent records in a loop.

For the multiple files, you would probably want to instantiate a folder object and a file object that represents each individual file in the folder.

I assume that your database is normalized, so the "header" information will be propagated to every row of the table.

At this point, it's hard to know what your level of technical expertise is, so here's a skeleton of how your program will need to work. If you need an actual example, write back.

pseudocode:
<dim variables for holding records and header data>
<dim a field array for holding tab separated fields>
<dim object variables (recordset, folder, file)>
for each file in folder
  Open file
  read first record into record variable
  move header data to variable(s)
  read next record into record variable
  move header data to variable(s)
  read next record into record variable
  move header data to variable(s)
  open recordset based upon destination table
  Do while not EOF (<filenumber>)
    read next record into record variable
    Split() the record area into the field array
    add new row to recordset
    move header data from variables to recordset fields
    move file data from field array elements to recordset fields
    update recordset
  Loop
  close file
Next

Let me know if a little more guidance is needed. If so, please include a sample of the data and the table layout. (This is not as complicated as it sounds)

Tranman

Adam was not alone in the Garden of Eden, however,...much is due to Eve,
the first woman, and Satan, the first consultant.
Mark Twain

 

[tekkyun]

Tranman,
Thanks for your advice and I do need a little more help!

I have attached a sample layout of the text data.
Ideally I need to pick up the cooler number, date and time which I am happy to propagate to each row of the table.
You can see that the other field headers start on row 4 (text wrapped in example below), with data starting in row 5 on.
Can you explain better how I would set the folder and file
objects?
Also, I am struggling to construct an import spec which doesn't start at line 1?

Text file sample starts below:-
PAT Test for Cooler Number p1001-1
Recorded on 29/04/2004 at 16:33:14 by ct

Elapsed Time TSD Flow BCU OBV Solenoid 1 Solenoid 2 dT/dt
0.000 296.490 0.019 7642.761 3111.457 5.000 -0.002 0.202
0.100 296.485 0.809 7352.293 3125.980 10.000 0.000 0.101
0.200 296.490 8.907 7163.488 3222.803 10.000 -0.001 0.253
etc etc

 

[tekkyun]

Tranman,
I have managed to write macro in Excel to format the text files to give all field headers in line 1 with data in line 2 on.
I have now saved these text files (5000+ files)in a single folder and I simply need to write code to do the following:

Look in folder
For each file in folder
Import and append data to Tbl
Next file in folder

Can you help with this piece of code?

 

[PHV]

Take a look at the Dir function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tranman]

Tekkun,
Send me a sample of how your new file looks now and we'll do some code that will import its contents.
Tranman



"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant."
Mark Twain

 

[tekkyun]

Tranman,
Thanks for your offer but I have now done the import using the DIR function as PH advised.
I would still be interested to know how to go about writing the spec in Access to import the raw text file (as per my example above). It seems that Excel is 'friendlier' when it comes to manipulating txt files!

I need to end up with the following format for easy import to Access:-
Serial_no Date Elapsed Time TSD Flow BCU OBV Solenoid 1 Solenoid 2 dT/dt
P1001-1 29/4/2004 0.000 296.490 0.019 7642.761 3111.457 5.000 -0.002 0.202

Any advice on this?

 

[Tranman]

tekkyun,
Here's a little snippet that will load your file into a table named TB1. It puts cooler#, Date, and Time into the first 3 columns, then puts the data into columns 4 thru 11.

For simplicity's sake, I just declared all columns as text and wrote this with the assumption that there were actually 4 spaces between the data fields (like in your example).

It's probably not ready for prime time, but it does work...

Tranman

Private Sub LoadCooler()
Dim strData As String
Dim strSQL As String
Dim strInRec As String
Dim aryClr() As String
Dim aryDT() As String
Dim aryData() As String

Open "c:\Cooler.txt" For Input As #1
Line Input #1, strInRec
aryClr = Split(strInRec, " ")
Line Input #1, strInRec
aryDT = Split(strInRec, " ")
Line Input #1, strInRec
Line Input #1, strInRec
Do While Not EOF(1)
  Line Input #1, strInRec
  aryData = Split(strInRec, "    ")
  strData = Join(aryData, ",")
  strSQL = "Insert into tb3 values('" & aryClr(5) & "', '" & aryDT(2) & "', '" & aryDT(4) & "', "
  strSQL = strSQL & strData & ")"
  CurrentProject.Connection.Execute strSQL
Loop
Close #1
End Sub

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant."
Mark Twain

 

[Tranman]

tekkyun,
I re-read your post. Guess what I gave you is not really a "spec" at all. To me, a spec would be something for the loader/import thingy. I do not use that tool--ever. I'm really comfortable with VB/VBA, and got hosed by the import deal early on. Gave up on the thing and have never looked back.
I'm also an Oracle person, and I don't use their sorry loader, either.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant."
Mark Twain

 

[tekkyun]

Tranman,
Thanks, I will have a play with your 'snippet'.
Just what I was looking for!