UtterAccess Discussion Forums > Need to Import and Append several pipe files

Full Version: Need to Import and Append several pipe files

UtterAccess Discussion Forums > Microsoft® Access > Access Automation

tiggerjohnson

Mar 1 2005, 03:15 PM

Hello all.

I have a messy situation here.

I have about 3000 pipe delimited files to import.

The good news is they are all named by date only.

I have 10 directories where they are located in and within each directory, they are all named the same so everyday, I get 10 files named for instance... 20050227 (YYYYMMDD)

To make matters worse, each record has a record count as the top line of the file which I DO NOT need in the results tables.

I need to somehow get the files from these 10 Directories into 10 tables (One per Directory)

Then I need to be able to do this daily once I get all the past data files done.

Any help would be appreciated.

I am assuming that I need some kind of looping SQL or VB code?

please help.

Thanks in advance!

strive4peace

Mar 2 2005, 10:43 PM

here is a start for you

with this method, you will need to also define a recordset variable (or APPEND SQL) and parse everything in

If your files are delimited, you can input more than one field per line

paste this code into Access

press F1 on each keyword to get more help on that command...

CODE

Function ImportFile()
   Dim mFileNumber, mFileName As String, mLine As String, i As Integer

   'you can open a recordset to put values into
   'Dim r As Recordset
   'Set r = CurrentDb.OpenRecordset("Tablename")

   mFileNumber = FreeFile
   mFileName = "c:\filename.TXT"
   Open mFileName For Input As #mFileNumber
   i = 0
   Do While Not EOF(mFileNumber)
      'i is the line number you are on
      i = i + 1
      Input #mFileNumber, mLine
      'remove this line after you get your parsing stuff working right
      If MsgBox(mLine, vbOKCancel, "Line " & i) = vbCancel Then Stop
      'put your parsing stuff here
   Loop
   Close #mFileNumber
End Function

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.