Importing Tab Delimited Files

[ChiTownDiva]

Good Afternoon All...

I need help...

I wonder if Access can:

1. In a windows folder, change the file extension from DAT to TXT (or CSV).
2. Use an import spec and import the file into the database.
3. Append the file to an existing table.
4. Drop the imported file from the database.

I need to figure out a way to do this via some code because I have about 100 tables to import into a database this way.

I'm pretty sure I would need to create a loop in order to accomplish this, but I have no idea where to start.

Any help would be greatly appreciated.

Thank you in advance for your help.

ChiTownDiva

 

[Blorf]

All of the above can be done.

Is your data all the same? Just different files with different data, but the same type of data with the same fields?

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[ChiTownDiva]

Thanks for responding ChaZ.

Yes. All of the files are exactly the same--only the names are different. And actually the file are semi-colon delimited, but I don't think that matters if I want to use an existing import spec.


ChiTownDiva

 

[Blorf]

Indeed.

Will you be importing the files on at a time? Or will you be scanning and importing a bunch at once?

If all at once, will you want to scan a directory to get all the names of the files, and import them in one shot? Or will you on occasion have a user import 1 at a time.

ChaZ


"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[ChiTownDiva]

Thanks ChaZ...

The files will always be in the same folder--always. I want to be able to import them all in one shot because there are no other users except me, so after I download the data to the folder, I want to be able to have Access get those files and append them to a table.

ChiTownDiva

 

[Blorf]

Cool. One last question.

You want to rename them. Is that just for the import?



"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[Blorf]

I am thinking that a function like the following would work.

Change the C:\Myfiles to the directory that has your files, and the .CSV to what ever your file type is.

Also, change "SpecName" to what ever your schema file is.

It will do all the files in the directory at once. Of course, I did no testing so...

ChaZ

Function Import()
With Application.FileSearch
.NewSearch
.LookIn = "C:\MyFiles"
.SearchSubFolders = True
.FileName = "*.CSV"
.MatchTextExactly = True
End With

With Application.FileSearch
If .Execute() > 0 Then

For I = 1 To .FoundFiles.Count
DoCmd.TransferText acImportDelim, "SpecName", "TempTable", .FoundFiles(I), True

DoCmd.OpenQuery "Your Append Query"

DoCmd.DeleteObject acTable, "TempTable"
Next I
End If
End With
End Function


"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[ChiTownDiva]

Thanks ChaZ...

Two things: how do I change the extension to TXT from DAT and how do I drop the imported file from the database after it has been appended? I don't want to have to go in and change the extension for 100 files.

Thanks in advance for your

 

[Blorf]

Well, the function imports it, you run a qauery to past the records, then the function drops it automatically.

Regarding DAT vs Txt, you should not need to, the function will read dat extensions if you change the paramaters.

If you do though, you can hit start, run, enter command, then type
C:
Cd \your directory
ren *.dat *.txt

ChaZ



"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[ChiTownDiva]

Thanks ChaZ...

You're right, it will read it the DAT file, but it won't import it using Access import specs. Also, I wanted to just drop the imported individual files, not the "TempTable". Also, when I ran the code, it works, but if I take out the "DoCmd.DeleteObject acTable, "TempTable"" line, it doubling the number or records on the TempTable.

 

[Blorf]

The temptable is the imported individual file, not your table holding the records.

The function brings in your text file as TempTable, then you need to run your sql query to read from it and put it into your real table. It then removes the temptable so you can move on to the next one.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert