Help with reading multiple text files into database table 2

[JustLJ]

Greetings.

I have some daily transactions in a tab delimited text file which I import into my database and then file the transaction files to a folder.

I found out there is a flaw in one of the update queries in the database and now I have to fix about 39000 records.

All of these records are contained in the aforementioned transaction files, so if I can bring them all back into the database, I can do an update query and fix the problem (instead of typing 39000 fixes!).

Now for the help part… how can I read in all 153 text files? They use the same import specification, so that’s not a problem. And I have a method for dealing with a daily file. But how can I loop - read and load – through 153 files? Can I do a
FOR
open *.txt
import
append to table
close *.txt
NEXT?
I would think I would only hit the same file over and over again. Can a variable be loaded with the file name from a folder? Maybe there is a way to merge all the files into one and then just import the one? The file names are date-ish (like 9982003.txt, 992003.txt, 9102003.txt, etc.) if that gives any help.

Thanks for your thoughts on this.
LJ

 

[SgtJarrow]

While you can do the For/Next loop, it take sa bit of coding.

But you mentioned that you could combine the files and import them as one big file....Try this:

Place all the files in one folder somewhere, open a command window, cd to the directory you have the files stored and run the following command:

cat *.txt >> C:\combinedfile.txt

This line will take each .txt file in succession in the folder and will append it to the bottom of the combinedfile.txt whcih will be placed on you root c drive. You can then move it wherever and import as necessary....

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[JustLJ]

Thanks Robert, but I get an error
>> 'cat' is not recognized as an internal or external command, operable program or batch file <<

Running Win2000 pro.

Any thoughts? I don't fine anything in help about concatenate or the like...

Love the idea if I can made that work!
LJ

 

[SgtJarrow]

oops, sorry bout that.....try the type command.

type *.txt >> C:\combinedfile.txt



****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[JustLJ]

BINGO!

Thanks!

 

[hymn]

Thanks mstrmage1768
I have being doing task one at atime

Hope this helps
Hymn

 

[FacilitiesCAD]

There is another solution. (I think) in access there is a Dir() command that would retrieve the first file and repeating the command (without an argument) will give you the next one.
Then you would be able to do something like you first intended.

Tim

This is taken from the Access help section.
Dir returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments. When no more file names match, Dir returns a zero-length string (&quot;&quot. Once a zero-length string is returned, you must specify pathname in subsequent calls or an error occurs. You can change to a new pathname without retrieving all of the file names that match the current pathname. However, you can't call the Dir function recursively. Calling Dir with the vbDirectory attribute does not continually return subdirectories.

 

[JustLJ]

Using Access 2002 while trying the DIR, I get a &quot;The expression you entered has a function name that Microsoft Access can't find&quot;. I recall using something like this in Access 97. Have they dropped support for this?

P.S. Something else weird, I tried to use a format in a macro condition and I got the same message. Did MS drop compatibility in Access 2002?

 

[JustLJ]

Follow-up to my last issue, I found out about the registry error Office 2002 has with sandbox needing to be a &quot;2&quot; instead of &quot;3&quot; and that fixed it. Weird.
LJ

 

[PeteJohnston]

I remember using Dir() in Access97. After you fixed the registry thing did Dir() work in Access 2002?

Is the FileSystemObject technique available in Access2002 (I've used it in VB6)? You can do stuff like

Dim fso As New Scripting.FileSystemObject
Dim fldr As Scripting.folder
Dim file As Scripting.file
Dim ts As Scripting.TextStream
Dim str As String

Set fldr = fso.GetFolder("SourcePath")
For Each file In fldr.Files
    Set ts = file.OpenAsTextStream(ForReading)
    str = ts.ReadAll
    .......
next

to read all of the text from a file into a string variable then chop it up as required.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)