VB Importing text files with different names

[BrettLG]

Hi, I hope someone can help, I am not very familiar with VB, but I believe what I am trying to do is simple, and I'm sure someone will know the answer.

I am using Access 2000 with VB 6.5

I am trying to import text files, which have different filenames, into an existing Access Table. The structure of all files are the same and they are stored in the same directory.

My problem is, I have to specify the filename, and therefore can not import more than one file. My VB code is: -
_____________________________________
Function ProdImport()
On Error GoTo ProdImport_Err
DoCmd.TransferText acImportDelim, "Import Specification", "ProductionImp", "c:\daten\es7000\160209_1456.txt", False, ""
_______________________________________

What I want to do is change the filename (currently 160209_1456) to select ALL files in the directory c:\daten\es7000. Additionally, if I run the import more than once I get duplicate records, so ideally after I ahve done the import I want to either delete the files imported or move them to a new directory, as to avoid 'Operator Error'.

I hope a 'wizz' can help me!
Kind regards
Brett

 

[ezfriend]

Create a text box where you can enter the folder path then follow the instruction on http://www.blueclaw-db.com/listbox-directory.htm to fill a list box with all your text files. Once the listbox is filled with files, create a button to perform the import function above by looping through the listbox and replacing current text file with the file in the listbox based on the listbox index.

If you are worry about duplicates, create a temp table with same fields and load all the text file to the table first. Once it is completed, you can insert the DISTINCT records from that temp table to your actual table and then delete records from the temp table.

Hope this help.

EZfriend.

 

[BrettLG]

Thank you for your answer..... seems harder than I thought, I will give it a go, if I get stuck, hope you can help me some more.

 

[gemma-the-husky]

basically you need to change this

Function ProdImport()
On Error GoTo ProdImport_Err
DoCmd.TransferText acImportDelim, "Import Specification", "ProductionImp", "c:\daten\es7000\160209_1456.txt", False, ""

into this

Function ProdImport(fname as string)

On Error GoTo ProdImport_Err
DoCmd.TransferText acImportDelim, "Import Specification", "ProductionImp", fname, False, ""

so that you can get your routine to import any file

so how do you get your file

basically have an import form with a command button that calls the above routine - and either

a) type the filename into a text box directly
b) use a file picker (fileopensavedialog) to select a file (the same way that windows does), then use that
c) pick a folder and process every file in the list.

how do you trap previously imported files
a) most developers would just move them to another folder, after processing
b) if you think they might get put back then you need to store a table in your database of the files you have processed.

c) but - either the same data could come back under a different name - or different data could come under an old file name, its possibly better NOT to do this - but to import every file requested, and find a way of testing it to see if you have alreadt seen the data in the file

 

[BrettLG]

Thank you.... lots of things for me to try... hope I'm successful!!

 

[DCrake]

Take a look at this mdb it does what you want, I think. Remember to look at the code and make the necessary changes to fit your environment.

David

 

[BrettLG]

Hi David,
Thanks for the findfiles.mdb..... works very well, but seems as if its related more to Excel files and there is a lot of code, I'm a novice of Access Databases, and even more so at reading the source codes, so I cant fully understand how it functions.

I will play with my database and see where it gets me, I was hoping that this was something very simple, but I can see thats definately not the case. I think Im trying to do too much and I think for me to do it will take many hours, and thrustrating hours... If anyone has some time to kill I can upload what Ive got so far and maybeyou or someone can take look at itl? As I mentioned, the basic I want is to simply select a folder and import all files against a set Import format.... but lot easier said than done. I will be more than happy if I can just change my Macro to 'folder' rather than a 'file' then I can get the rest to work (I think!)

 

[DCrake]

If you read the instructions more closely it states that you can change the extension of the files you want to search for. Plus the default location. The remmed out code uses the contents of the listbox and enumerates through the list and uses the Transfertext to import each file one at a time. This is a two click operation. What can be simpler than that.

Look at the code behind the Import Command button

David