Importing multiple textfiles

[abbyanu]

Hello

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 use VBA 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.

 

[B827]

Hi Abby,
I had to deal with a similar problem, reteiving certain lines from text files used in another system. You should be able to do this in VB Code reasonably easily. The method I used was read through the folder and collect all the file names, then extract the data required into my table.

This Code will find the file names and store the name in an array.

With Application.FileSearch
.LookIn = FileLoc
.SearchSubFolders = False
.FileName = "*.txt"
If .Execute() > 0 Then
J = .FoundFiles.Count
For I = 1 To J
ReDim Preserve Fnames(I)
Fnames(I) = .FoundFiles(I)
Next I
Else
MsgBox "There were no files found."
End If
End With


This code will open each file from the array and read the contents. Then add the data to the table.

For I = 1 To J
Open Fnames(I) For Input As #1
Input #1, FileRead
ProdNo = Mid(Fnames(I),StartPos,Len(Fnames(I)-calculatedNumber))
With Rst
.AddNew
!ProductNumber= ProdNo
!Description= FileRead
On Error Resume Next
.Update
On Error GoTo Err_LoadRST
End With
End If
Loop
Close #1
Next I

the line highlighted in red in the code above is to extract the Partnumber from the file name. So if your files are stored in C:\Partnumber Files\BoughtIn the value stored in the array for part number 123456 will be C:\Partnumber Files\BoughtIn\123456.txt and the Mid function will extract the 123456. If your part numbers all all the same length than you can hardcode the start position and length into the Mid function. Otherwise only the start position is fixed.

let me know if you have problems. Sandy

 

[abbyanu]

Thanks so much for the code, Sandy. This is precisely what I wanted. I've been trying to tinker with it, but I'm getting an error message:

[tt]Compile error: Variable not Found[/tt]. The line below seems to be the culprit.

[tt]ReDim Preserve Fnames(I)[/tt]

Any clue what's causing the error?

Many thanks,
Abby.

 

[abbyanu]

Hello!

I'm still a newbie to VBA, and though I'm sure I the code above can be modified to serve the requirements I specified, I'm still getting other errors:

1. [tt]Loop[/tt] needs a [tt]DO[/tt], and the [tt]End If[/tt] above it needs a corresponding [tt]If[/tt] statement.

2. The [tt]ReDim Preserve Fnames(I)[/tt] is also still generating the error [tt]Compile error: Variable not Found.[/tt]

Am I missing something? Please help.

Gratefully,
Abby.

 

[B827]

Hi Abby
Yes I hadn't realised you were new to vba programming. I have a couple of urgent things to sort out. I'll post some better code as soon as I can.

In the mean time you have to declare all the variables used so you need a Dim Fnames() at the start of the procedure. You can remove the loop as that should have been deleted from my original code. Many appologies for that mistake.

You'll also have to include the following statements at the start of the procedure.

Dim Mydb as datebase, Rst as recordset
set Mydb = currentdb
Set rst = db.OpenRecordset("tablename", dbOpenDynaset)

Hope that helps.

By the way I'm in the UK so we may have a time difference. Sandy

 

[B827]

Hi Abby,

I have tried this code which works on my system. To load descriptions with carriage returns the code will need a slight change. The folder location is passed to the function and I have put a bit of code to enter this via an InputBox at the bottom of this posting. Please let me know if you still have problems.


Function GetPartData(FileLoc As String)
Dim FNames() As String, j As Double, I As Double, Prodno As String
Dim Mydb As Database, Rst As Recordset, FileRead As String, StartPos As Integer, CalculatedNumber As Integer
Set Mydb = CurrentDb
Set Rst = Mydb.OpenRecordset("Partslist", dbOpenDynaset)

With Application.FileSearch
.LookIn = FileLoc
.SearchSubFolders = False
.FileName = "*.txt"
If .Execute() > 0 Then
j = .FoundFiles.Count
For I = 1 To j
ReDim Preserve FNames(I)
FNames(I) = .FoundFiles(I)
Next I
Else
MsgBox "There were no files found."
End If
End With
StartPos = Len(FileLoc) + 2
For I = 1 To j
Close #1
Open FNames(I) For Input As #1
Input #1, FileRead
CalculatedNumber = InStr(StartPos, FNames(I), ".txt&quot
Prodno = Mid(FNames(I), StartPos, (CalculatedNumber - StartPos))
With Rst
.AddNew
!ProductNumber = Prodno
!Description = FileRead
On Error Resume Next
.Update
End With
Close #1
Next I


End Function

Function LoadPartData()
Dim fred As Variant
fred = GetPartData(InputBox("Please Enter Source Folder", "Part Number Data Import", "I:\PartList&quot)
End Function

Sandy

 

[abbyanu]

Really appreciate your effort. I'm actually in Trinidad - 5 hours behind the UK.

I'm using Access 2000, and as such all the code is in the Access form.

The line [tt]Dim Mydb As Database[/tt] is now generating an error message:
[tt]user-defined type not defined[/tt]

When I tried to change the line to read [tt]Dim Mydb as CurrentProjext[/tt], I was getting errors in the two lines below as well:
[tt]Set Mydb = CurrentDb
Set Rst = Mydb.OpenRecordset("Partslist", dbOpenDynaset)[/tt]

Also, at the line [tt] !ProductNumber = Prodno[/tt], I presume that will replace the [tt]ProductNumber[/tt] in the table with the value in [tt]ProdNo[/tt]. I only wanted to replace the [tt]Description[/tt] field in the table with the [tt].txt[/tt] file, WHERE [tt]ProductNumber[/tt] = [tt]ProdNo[/tt].

Isn't an SQL statement appropriate here? How can I include such a statement to read something like:

REPLACE [tt]!Description[/tt] WITH [tt]FileRead[/tt] WHERE [tt]!ProductNumber = Prodno[/tt]

Thanks for your patience.
Abby.

 

[B827]

Try changing the Dim to

Dim Mydb As DAO.database


Which should clear the errors. You may have to add the reference to DAO object library.

To do this, when you're in the code goto the 'Tools' menu and select 'References'. The see if

Microsoft DAO 3.6 Object Library is selected. (All selected objects are at the top of the list). If not scroll down and select it.

Yes you can use SQL, I was adding both fields to the table. If there is data already there you can use SQL. You can also do it in code by using the FindFirst method. If you have speed problems with SQL that might be worth investigating. Sandy

 

[B827]

Abby,
I think you need

Rst As DAO.Recordset

in the declarations as well, you'll have to forgive me as I normally use Access97, 2000 is new territory for me. Sandy