Importing files into Access 2007

[rjani1]

Dear Access Community,

Is there a function within Access which allows me to batch import a number of files?

I have a text file which contains some UNC network paths which include file names. Each file has an onwer defined. I then import the text file into Access which inserts all the file paths on one field and the owner in the other. The third field is the attachment field which will hold all the files.

I would like Access to read the file path and upload the file onto the attachment field based on this information.

Any tips or suggestion most appreciated.



Regards,

rjani1

[rpeare]

It's possible to do what you want but Access 2007+ have removed the ability to use import specifications which makes coding easier. Can you give an example of what your text files look like and which rows of the text files you want to import.

You will probably have to do some coding with filesystemobject to open each file (assuming the file names do not change?) then to cycle through them to parse the information you want then run a SQL command to append the record to an Access table.

[rjani1]

Thanks rpeare.

The format of the text files are like this

^\\server name\folder\folder2\folder2\filename.txt^rjani.department^

The ^ symbol is used as a delimiter. I have also imported the data from Excel 2007 as well. So the data in Cell A1 will have the pathname and the data in B1 will have the owner detail.

The files will otherwise remain the same. I am not much of a vb coder so if have any suggestions / tips / pseudo code then I will happy to be a book worm for a week and get to grips with vb.

[rpeare]

That's the entirety of the file?

One row of ^ delimited values?

Or is are they bigger files with man rows of ^ delimited values?

do the files start with a line of data you want to import?
are there any lines you don't want to import?

the code really depends on how many of these files there are, if they have names that are identifiable by using some sort of string recognition etc.

Let's assume for the sake of argument that you have ONE file with three rows of data you want to import:

FILE NAME AND PATH = c:\Input.txt

RecordA^ValueAA^ValueBA
RecordB^ValueAB^ValueBB
RecordC^ValueAC^ValueBC

We'll assume there are no lines you want to ignore as well.

You want to import this into an access table called "Network_Stuff"

Assume your table Network_Stuff has this structure
ID (autonumber)
FirstField (text)
SecondField (text)
ThirdField text)

your code to add the records to the table would be something like:

Dim fInput
Dim fs
Dim sFileName
Dim LineArray
Dim sSeg
Dim db As Database
Dim sSQL As String

Set db = CurrentDb
Set fs = CreateObject("Scripting.filesystemobject")
sFileName = "c:\input.txt"
Set fInput = fs.opentextfile(sFileName)

Do While fInput.atendofstream <> True
    sline = fInput.readline
    'reads in a line from the text file
    
    LineArray = Split(sline, "^")
    'loads each portion of the string into an array
    sSQL = "INSERT INTO Network_Stuff (FirstField,SecondField,ThirdField) VALUES ("
    'start the ssql statment
    For Each sSeg In LineArray
        sSQL = sSQL & "'" & sSeg & "',"
        'inserts the value and a comma for each item in the array assumes all fields are text
    Next
    sSQL = left(sSQL, Len(sSQL) - 1) & ")"
    'removes the final comma on the string and puts in a closing paren
    Debug.Print sSQL
    db.Execute sSQL 'adds the record to the access table
Loop

[rjani1]

Hi rpeare,

Thanks for the info. To make things simpler I have already imported the delimited text file into the Access database. This is the easy part but the field definitions are slightly different as below:

 
ID (autonumber)
PathName (memo)
Owner (text)
File (attachment)

So basically I am asking vb to fetch (or copy) the file from the directory listed in PathName and attach it to File. The PathName field is set to memo because text is limited to 255 characters and I am in an environment where there are path lengths above 255.

But you are right in that I am importing all the directories listed in the delimited text file. There are a lot of directories.

Many thanks,

Rjani1

[rpeare]

I don't understand. Your original question was about importing text files into an access table. I gave you an example of how to import one file which you can then adapt to do multiple files.

I don't know what you mean by attaching the pathname to the file

Are you talking about email or are you talking about making a unified string out of two fieldnames

Let's say your PATHNAME is c:\
and your FILENAME is rjani1.txt
(do not use the reserved words for database objects, file is one of them)
if you wanted to create a unified string in your code you would do this:

sUnifiedString = pathname & filename

which would give you a resulting

c:\rjani1.txt

If your path names are more than 255 characters and you want to then perform some code with that you will have to experiment because I've never tried using strings of that length in my code.

[rjani1]

Hi,

Many thanks for your help. Sorry if I did not make myself clear. What I will do is pick up on your code and experiment for a while and see where I
get to.

Regards,

rjani1