Import text to Access

I have around 4000 text files (.txt) in a folder each of which contain a few lines of text describing a product in an Access database. The file name matches the product code so when required these files can be opened by looking for a filename that matches the product code. I'd like to import all of these files into a table using the file name to create the product code and then copying the text within the file into a memo field next to the product code. I'm sure there must be a way of doing this via VBA but I'm not skilled enough with that to work out what it is?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
This could be quite complicated to create depending on your exact needs.

Can you post an example of the main tables , ...then post a few text files..

I see something like this as the design:
pCode (PK)

pnID (PK)
pn_pCode (FK)

Here each Product could have many notes...
Dale FyeOwner, Developing Solutions LLCCommented:
You will obviously need to loop through your product code and search for the appropriate file names.  Do that using a loop and the Dir() function, passing the function the name of the product code configured as a file name.  If it returns an empty string, the file was not found and you can move on.  If it returns the name of the file (without path), then it found the file.

You can then use the Open For Input #1 statement to open the text file and loop through the file reading each line and appending that to a string.  Then when you reach the end of the file, write the text string that you created to your table.
Dale FyeOwner, Developing Solutions LLCCommented:
posted from my iPad, so check syntax.

Reading the text file would look something like:

Public Function ReadText(FullFileName as string) as String

    Dim FileNum As Integer
    Dim s As String

    if dir(FullFileName) = "" Exit Function

    FileNum = FreeFile()
    Open FullFileName For Input As #FileNum

    While Not EOF(FileNum)
        Line Input #FileNum, s    ' read in data 1 line at a time
        ReadText = ReadText & vbcrlf & s
    Close #FileNum

End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TomBurburyAuthor Commented:
Just the job. With a little tweaking for my particular environment this worked a treat.
Dale FyeOwner, Developing Solutions LLCCommented:
glad to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.