Adding date from header in file to data while importing (1 Viewer)

[sandy22]

Hello, I receive multiple .txt files everyday, The import code is working fine. I have been looking for examples to see how I can do the below though I am not sure if it can be done, Any guidance is much appreciated.
The First row in every file is the date as below:

HEADER;2021051701
ABC;Origin1; destination1;;;29870
DEF;Origin2; destination2;;;29870

Is there a way where I can take the date from the first row and add it to the data as below?

ABC;Origin1; destination1;;;29870;20210517
DEF;Origin2; destination2;;;29870;20210517

 

[theDBguy]

Hi. There are several ways to read a file. For example, you can use FSO or the Open statement. If the header info always match the date the file was created, you can even use the FileDateTime function.

How to Read a Text File

Invariably, at one point or another in your journey to write VBA in Access, there will come a time when you will need to read the contents o...

thedbguy.blogspot.com

 

[arnelgp]

make sure that you always have that "Header"
copy and paste to a Module.
call it:

Call fnAddDate("theTextfileName")

'arnelgp
'add Reference to Microsoft Scripting Runtime
Public Function fnAddDate(ByVal pTextFile As String)
Dim fso As Scripting.FileSystemObject
Dim f As TextStream
Dim v As Variant
Dim sContent As String
Dim sNewContent As String
Dim sDate As String
Dim tfFirstLine As Boolean
Set fso = New Scripting.FileSystemObject
Set f = fso.OpenTextFile(pTextFile, ForReading)
tfFirstLine = True
With f
    Do Until .AtEndOfLine
        sContent = .ReadLine
        If tfFirstLine Then
            v = Split(sContent, ";")
            sDate = Left$(Trim$(v(1)), 8)
            tfFirstLine = False
        Else
            sNewContent = sNewContent & Replace(Replace$(sContent, Chr(10), ""), Chr(13), "") & ";" & sDate & vbCrLf
        End If
    Loop
    .Close
End With
Set f = fso.OpenTextFile(pTextFile, ForWriting)
f.Write sNewContent
f.Close
Set f = Nothing
Set fso = Nothing
End Function

 

[sandy22]

I understood the reading part, However I can't edit the files I receive how can I make the code to write the date into a field say DATE in the database table once file is imported ?

 

[theDBguy]

I understood the reading part, However I can't edit the files I receive how can I make the code to write the date into a field say DATE in the database table once file is imported ?

Could you use an UPDATE query?

 

[conception_native_0123]

However I can't edit the files I receive

why not? you are saying that you receive text files that are not editable? if they *are*, or that they should be because you are viewing them on your own machine, these are the functions that you would probably, or should use before your importing process:

Open statement (VBA)

Office VBA reference topic

docs.microsoft.com

Get statement (VBA)

Office VBA reference topic

docs.microsoft.com

Put statement (VBA)

Office VBA reference topic

docs.microsoft.com

how can I make the code to write the date into a field say DATE in the database table once file is imported ?

you would have to use a subquery wouldn't you? or, capture the date from the first record however you wish and then run an update query like dbGuy said using a literal like you receive (2021051701) or possibly a "broken-up" literal put into the dateSerial() function:

DateSerial function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com