Recurring import of linked text file data

[David G]

Hi

I have a large amount of data in text files that I wish to append to a table
in Access. I did a manual link to the text file, set and saved some
specifications and successfully appended the data to my table. The problem
is I have a large number of text files (all the same specifications) that I
wish to append to the same table but I really don't want to do this manually.
I am hoping someone could point me to some VBA code that I could use to
automate this process? The other thing is the files are grouped in different
folders (Monthly folders with daily files) so I need something that could be
flexible enough to handle that, could someone assist me with this please?

 

[Ken Snell \(MVP\)]

Give us more details about the folder names and the text file names.

 

[David G]

Hi Ken,

From the research I have done on this question I recognise that I need to
use the DoCmd.TransferText method. The issue for me is to organise some code
so as to process the multiple files in multiple folders. The text file names
are designed according to a date process, i.e. the text files are named as
yyyymmdd.txt, e.g. 20071105.txt which is the file for 5th Nov 2007. I have
named the folders Nov_07, Dec_07, Jan_08 etc.

I have imported, actually I linked the txt file and created an append query
to import the data into my Access table "tblDailyData". The files themselves
are csv files and they have no field names. In the process I have created
and saved a specification for the link which I have saved as
"MyTextFile_Import_Spec", and the name of the manual link I created is
"MyTextFile_Link".

My next best guess is that I also need to use the Dir function. I need to
go to the relevant monthly folder, and select it from a dialogue box??? This
would return the path of the folder so that I could then pass Dir("*.txt")
and iterate through the daily text files, appending them to the tblDailyData.
When it is finished with that folder (when strTextFile = ""), the dialogue
box would then reappear and I could select the next folder, and repeat the
loop. When I want to stop the process I could click on Cancel or Exit. In
terms of appending data to the tblDailyData table, it doesn't matter what
order the Dir function returns the files because I have set Primary Keys in
the tblDailyData table.

If I was to do this job manually, Access wants to name the link
"strTxtName", if I was to repeat this job for Nov_07 I would wind up with 30
link names, and for Dec_07 I would have 31 names. I want to avoid this
problem, winding up with an unwieldly number of link names. This is why I
want to code this process, so that I don't end up with some manual job that
will take many, many hours.

Ken, I hope the information here helps clarify what I am looking for. If
you need any further information I will be happy to provide it. Much
appreciated.
--
Thanks
David G
Albury, Australia

Give us more details about the folder names and the text file names.

 

[Ken Snell \(MVP\)]

Here is some sample code for looping through text files (.csv extension) in
a single folder and importing them:

Public Sub pfImport()
On Error GoTo Err_F

Dim strPathFile as String, strFile as String, strPath as String,
strSpec as String
Dim strTable as String, ynFieldName as Boolean
ynFieldName = False
strPath = "C:\Documents\"
strSpec = "NameOfImportSpecification" ' Put your name here
strTable = "tablename"
strFile = Dir(strPath & "*.csv")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferText acImportDelim, strSpec, strTable,
strPathFile, ynFieldName
' Uncomment out the next code step if you want to delete the
file after it's imported
' Kill strPathFile
strFile = Dir()
Loop
Exit_F:
Exit Sub

Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F

End Sub


To allow you to choose the folder as an additional "outer" loop, you could
either use the InputBox function to manually type in the path to that
folder, and use that to set the strPath value for the "inner" loop that
imports the text files; or you could adapt the code at The ACCESS Web that
lets you open a Browse dialog box and navigate to the folder ( see
http://www.mvps.org/access/api/api0001.htm ).

How are your VBA coding skills? Post back if you need assistance or have
more questions.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi Ken,

From the research I have done on this question I recognise that I need to
use the DoCmd.TransferText method. The issue for me is to organise some
code
so as to process the multiple files in multiple folders. The text file
names
are designed according to a date process, i.e. the text files are named as
yyyymmdd.txt, e.g. 20071105.txt which is the file for 5th Nov 2007. I
have
named the folders Nov_07, Dec_07, Jan_08 etc.

I have imported, actually I linked the txt file and created an append
query
to import the data into my Access table "tblDailyData". The files
themselves
are csv files and they have no field names. In the process I have created
and saved a specification for the link which I have saved as
"MyTextFile_Import_Spec", and the name of the manual link I created is
"MyTextFile_Link".

My next best guess is that I also need to use the Dir function. I need to
go to the relevant monthly folder, and select it from a dialogue box???
This
would return the path of the folder so that I could then pass Dir("*.txt")
and iterate through the daily text files, appending them to the
tblDailyData.
When it is finished with that folder (when strTextFile = ""), the dialogue
box would then reappear and I could select the next folder, and repeat the
loop. When I want to stop the process I could click on Cancel or Exit.
In
terms of appending data to the tblDailyData table, it doesn't matter what
order the Dir function returns the files because I have set Primary Keys
in
the tblDailyData table.

If I was to do this job manually, Access wants to name the link
"strTxtName", if I was to repeat this job for Nov_07 I would wind up with
30
link names, and for Dec_07 I would have 31 names. I want to avoid this
problem, winding up with an unwieldly number of link names. This is why I
want to code this process, so that I don't end up with some manual job
that
will take many, many hours.

Ken, I hope the information here helps clarify what I am looking for. If
you need any further information I will be happy to provide it. Much
appreciated.

 

[David G]

Hi Ken,
Thanks for the code outline, I have put it in to a module and tested it, it
works fine, and I understand it. I appreciate you giving me the link to the
Access Web code sample written by Litwin and Getz. This is the code that I
aspire to but at the moment I'd have to admit it is beyond me at this stage.
I guess I could copy it in to my module and adapt it but some of it would be
guesswork. I want to be able to understand the code that I write. I think
it would be best if I stuck with the InputBox function that you suggested.
At least I would follow what I'm doing.

If you think the Litwin & Getz code is not as difficult as I make out, maybe
you could offer me some guidance as to what I would need to do? Anyway,
we've handled the main task, thank you very much. As for the "outer" loop I
will stay with the InputBox function unless you think otherwise.

 

[Douglas J. Steele]

I think Ken made a slight mistake. He should have pointed you to
http://www.mvps.org/access/api/api0002.htm instead. That's the code to
implement the BrowseForFolder dialog, which is all you'd seem to require.

 

[Ken Snell \(MVP\)]

I will post some information for you soon about the Litwin & Getz code.

 

[Ken Snell \(MVP\)]

Paste all the Litwin and Getz code (from
http://www.mvps.org/access/api/api0002.htm ) into a new, regular module.

Then modify the original code that I'd provided to this:

Public Sub pfImport()
On Error GoTo Err_F

Dim strPathFile as String, strFile as String, strPath as String,
strSpec as String
Dim strTable as String, ynFieldName as Boolean
ynFieldName = False
strPath = BrowseFolder("Select the folder that contains the "".csv""
files:")
strSpec = "NameOfImportSpecification" ' Put your name here
strTable = "tablename"
strFile = Dir(strPath & "*.csv")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferText acImportDelim, strSpec, strTable,
strPathFile, ynFieldName
' Uncomment out the next code step if you want to delete the
file after it's imported
' Kill strPathFile
strFile = Dir()
Loop
Exit_F:
Exit Sub

Err_F:
MsgBox Err.Number & " " & Err.Description
Resume Exit_F

End Sub


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[David G]

Thanks Ken,

Interestingly something surprising happened during the import of data. At
about 2.6 million rows Access threw up a message saying "Your computer is
running out of disk space, continue yes, no?" This kept recurring so I
stopped the import. The mdb file was about 256mb, not very big because the
rows, consisting of 8 fields adding up to about 60 bytes per row. Hard to
fathom. Frustrating because the import procedure was working so well. I
definitely wasn't going to run out of space because that drive is 750gb with
690gb free.
Having just returned from the Sydney OfficeDevCon 2008, I did ask around
about this but didn't get any conclusive answers.
One of the suggestions, due to the size that this mdb will get to, is to
migrate it to SQL Server 2000. The file grows by about 2,500 rows per
business day. So that seems to be plausible, but doesn't answer the unlikely
error message I received.
Thanks for the modified code,
Regards

 

[Ken Snell \(MVP\)]

Just a guess on my part, but the message from ACCESS may have been an
indication that the amount of data being imported ("changed") was exceeding
the buffer space that ACCESS could use and thus be able to rollback the
import if a failure occurred. 256MB for an ACCESS backend is not a large
size, but my preference would be to start looking to SQL Server or other
large database system when the ACCESS file approaches 1.25 - 1.5GB. Reason:
although ACCESS can handle 2GB max size for a file, the file size grows as
you manipulate data in it, which compacting will shrink back down; but,
during that interim growth, if you hit 2 GB, that will crash the database
file. So it's best to not get too close to the maximum size.

At the rate of about 150KB of new data per day, using your numbers, you can
calculate how much time you have before you would hit my arbitrary size.

 

[David G]

Thanks Ken,
Yes, I recently purchased a wonderfully written book on Access & SQL Server
by Mary Chipman and Andy Baron. It seems to have a lot of good information
in there, not only about integrating Access & SQL, but a striking degree of
depth to the text also. So, I will have my work cut out over the next few
months but it should be informative and rewarding to learn these new skills.
Thanks for all your suggestions during the course of this post, they have
been insightful and encouraging. I never fail to admire the dedication of
people such as yourself who watch over the developing flock.
Regards
--
Thanks
David G
Albury, Australia

Just a guess on my part, but the message from ACCESS may have been an
indication that the amount of data being imported ("changed") was exceeding
the buffer space that ACCESS could use and thus be able to rollback the
import if a failure occurred. 256MB for an ACCESS backend is not a large
size, but my preference would be to start looking to SQL Server or other
large database system when the ACCESS file approaches 1.25 - 1.5GB. Reason:
although ACCESS can handle 2GB max size for a file, the file size grows as
you manipulate data in it, which compacting will shrink back down; but,
during that interim growth, if you hit 2 GB, that will crash the database
file. So it's best to not get too close to the maximum size.

At the rate of about 150KB of new data per day, using your numbers, you can
calculate how much time you have before you would hit my arbitrary size.