Importing multiple textfiles into table

[abbyanu]

Hi

I have an Access 2000 table called "Products". The primary field is "ProductNumber". This table also a memo field "Description", which should contain the product description.

I wish to import the product descriptions from DacEasy accounting text files (*.txt).

Each product's description is stored as a separate text file in the format "ProductNumber.txt", hence for each ProductNumberX record, there's a corresponding "ProductNumberX.txt" file.

My problem is:

How can I be able to read all the .txt files from a given directory, and export each .txt file into the corresponding "Description" field in the "Products" table, such that ProductNumberX.txt file is imported into the "Description" field of ProductNumberX, ProductNumberY.txt into ProductNumberY, etc...

Many thanks.

AbbyAnu.

 

[cmmrfrds]

Do you want to do it in VBA code?

 

[abbyanu]

yes, cmmrfrds. i would like the VBA code.

 

[cmmrfrds]

Here are a couple of functions that I used to read all the files in a directory and then process each file. Hopefully, this will get you started.


Function ProcessEDIFiles() As String
'
Dim mypath, myname, myfile(15)
Dim indx As Integer, pathname As String, pathPrefix As String
'--- Load the files from the directory into an array
mypath = "C:\edi\850in\imports\*.txt"
pathPrefix = "C:\edi\850in\imports\"
myname = Dir(mypath, vbDirectory)
'-- Get the 1st file
indx = indx + 1
myfile(indx) = myname
'-- Load remaining files
Do While myname <> &quot;&quot;
myname = Dir
If Not (myname = &quot;&quot Then
indx = indx + 1
myfile(indx) = myname
End If
Loop
''-- Read and process all the files in the directory
Dim indx2
'''Dim glbcnn As ADODB.Connection
Set glbcnn = CurrentProject.Connection
For indx2 = 1 To indx
'-------Debug.Print &quot;All my files = &quot;; myfile(indx2)
pathname = pathPrefix & myfile(indx2)
ReadTextFile (pathname)
Next

Set glbcnn = Nothing

End Function

Function ReadTextFile(pathname As String) As Variant
Dim inLine As String, RecType As String
Dim data1 As String
Dim retCode As Variant
'-----
'------pathname = &quot;C:\edi\850in\imports\850test1.txt&quot;
Open pathname For Input As #1

Do While Not EOF(1)
Line Input #1, inLine
glbSegmentName = Mid(inLine, 1, 5)
RecType = Mid(inLine, 1, 5)
data1 = Mid(inLine, 1, 1405)

Select Case RecType 'choose import based on first 5
Case &quot;HDBEG&quot;
retCode = WriteHDBEGTable(data1)
Case &quot;HDSAC&quot;
retCode = WriteHDSACTable(data1)
Case &quot;HDN1 &quot;
retCode = WriteHDN1Table(data1)
Case &quot;DTPO1&quot;
retCode = WriteDTPO1Table(data1)
Case &quot;DTOTH&quot;
retCode = WriteDTOTHTable(data1)
Case &quot;DTSAC&quot;
retCode = WriteDTSACTable(data1)
Case &quot;DTN1 &quot;
retCode = WriteDTN1Table(data1)
Case Else
MsgBox &quot;Unrecognized record type in FileLine &quot; & glbSegmentName
End Select
Debug.Print &quot;records segment = &quot;; glbSegmentName
Debug.Print &quot;data1 = &quot;; data1
Loop
Close #1

End Function