Import .txt files & create date field

[dSurvivalist]

Hello everyone,

I have a problem with importing text files with an existing specification.
My specification works well if i import a file manually, but i also use a code to import multiple files (.txt) with a criteria which is to be found in below code.
The code i acquired is importing the files but not the first lines of them. my .txt file structure is quite simple, and identical to each other. i need it to include also the first line of the texts, and add this line in a field in each records until another file begins to import. In other words, the date in reds below, has to be in each record as Field2. because each files first lines have different dates. the text files layout is like below;

1st File:

VPLHDREAAFB 20100423
VPLISTAN0AT0363704S41 3K305 AA 001.00000
VPLISTAN0AT0363706E5Q 6A228 BD 001.00000
VPLISTAN0AT0363706G9N 11000 AB 001.00000
VPLISTAN0AT0363706S43 7M125 BB 001.00000
..........

2nd File

VPLHDREAAFB 20100425
VPLISTAN0AT0363709T16 4B435 AB 001.00000
VPLISTAN0AT0363709T16 5E211 AC 001.00000
VPLISTAN0AT0363709T16 5E292 AD 001.00000
VPLISTAN0AT0363709T16 9D289 AC 001.00000
..........

and so on...

this is the code i use,

Function Load_Attendance_Data()
Dim NextFile, ImportFile, FileCriteria, ctr As Variant
Dim DB_Path, Folder As Variant

' get the DB path
DB_Path = "C:\Users\Q\Desktop\FTP\"
Folder = ""
FileCriteria = "C:\Users\Q\Desktop\FTP\" & "VPL*.txt"

' create field with path and filename to import MAKE THIS READ ALL files that start with 'VPL'
NextFile = Dir(FileCriteria)

' Check we have something to import
If NextFile = "" Then
MsgBox "No files to import", , "Error"
Exit Function
End If
ctr = 0

' Import each file that meets the criteria 'VPL*.txt'
While NextFile <> ""

' count files imported
ctr = ctr + 1

' add the path to the returned filename
ImportFile = DB_Path & Folder & NextFile

' Import file into table
DoCmd.TransferText acImport, "VPL", "VPL", ImportFile, True


' get another file if it exists
NextFile = Dir()

Wend

MsgBox ctr & " files imported", , "VPL"
End Function

 

[RuralGuy]

When you say "My specification works well if i import a file manually", how many records are you creating in the input table?

 

[dSurvivalist]

hi,
records are about 100k for each file.

 

[dSurvivalist]

i attached an image file shows what i exactly need to do within the code above

 

[RuralGuy]

So basically you are importing each record into a single field within Access, right?

 

[dSurvivalist]

Yes, exactly..

 

[RuralGuy]

Let's start by changing just one element and see what you think.
DoCmd.TransferText acImport, "VPL", "VPL", ImportFile, False

 

[dSurvivalist]

well friend, nescience is worst
i can now import also the first lines as records! thaks for the tip!

 

[RuralGuy]

We can still create the table you described but it requires more code. If you are still interested, we can take a shot at it.

 

[dSurvivalist]

of course.. i still need it..

 

[RuralGuy]

Is one field for all of the data in an input record what you want or would having the various values in separate fields be more helpful? is the date record *always* the first record in the input and does it always have VPLHDREAAFB at the head of the record and is it the *only* record with the value at the beginning?

 

[RuralGuy]

You have also hard coded the location and name of the folder. Would you prefer to ask the user to locate the folder or does hard coding it serve your purposes?

 

[dSurvivalist]

Is one field for all of the data in an input record what you want or would having the various values in separate fields be more helpful? is the date record *always* the first record in the input and does it always have VPLHDREAAFB at the head of the record and is it the *only* record with the value at the beginning?

Each file begins with the record "VPLHDR*" but the following date changes for each .txt files.

Considering this project i work on i s a ABC analysis and forecast analysis, i will need the date information in a second field for each records.

Should be like below;

VPLHDR*** 20100420
VPLLIST*** 20100420
VPLLIST*** 20100420
VPLHDR*** 20100427
VPLLIST*** 20100427
VPLLIST*** 20100427
...
...

Date in the header record goes for each record as a field string until the "VPLHDR*" record changes with the date.

About the hard coding; if i could select the path of the file folder, that would be marvelous indeed...