Import Delimited file into MS Access

[MKH2001]

New saga in the database from hell.

For a long time I haven't had to hinder you with my trials and tribulations..... but now I have a new humdinger on my plate.

I have need to create a form that performs multiple functions.

1)Allows the user to select a .csv or .txt file by browsing to a location (.csv or .txt file name will change) and selecting on (or more) files for import.

2)Imports the data to a temporary table.

3)Runs various validating queries and imports if criteria is met (ie all the fields is valid) or else throws up errors (looking to create a interface that displays the offending record and identifies the erroneous entry).

4)Once all errors are corrected (or if there was no errors in the first place) transfers the data and appends it to the master table and then deletes the records (not the table itself) from the temporary table.

I realize this is a large project and must admit to asking here for info before i start (individual input in how best to do any or each part has always be constructive in the past when faced with a issue I have no experience with previously).

The main issue is the import and the ability to browse and then import a file whose name is not specifically specified and refer it to a import macro (if that is the best way) since the import macro I have played with so far seems to require a defined path and file name before attempting to import.

Any pointers at where to start would be greatly appreciated.

Mark
(

 

[jadams0173]

You may try the SPLIT function to get the csv or txt file into an array. Then load the data into your temp table.

 

[randy700]

How about the TransferText method?


Randy

 

[Remou]

This may help:
Automating Import Files
thread705-1204262

 

[MKH2001]

Using the TransferText method

Code:

Private Sub Command3_Click()

Dim InputDir, ImportFile As String, tblName As String, FinalName As String
Dim InputMsg As String
InputDir = "C:\SL_DBReports\Upload\PreLoad\"
ImportFile = Dir(InputDir & "\*BCC.csv")
Do While Len(ImportFile) > 0
tblName = "tbl_BCCInspInputTemp" DoCmd.TransferText acImportDelim, "ImportBCCTemp", tblName, InputDir & ImportFile, True
ImportFile = Dir
Loop
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject")
Then
fs.MoveFile "C:\SL_DBReports\Upload\PreLoad\*BCC.csv", "C:\SL_DBReports\Upload\PostLoad\"

End Sub

Works nicely, loads all files with *BCC.csv prefix into tbl_BCCInspInputTemp table.

However I do have 2 issues.

One is a problem is that regardless of a successful load it transfers the file into the PostLoaded folder.
What I would ideally like is for it to remain in the PreLoad folder until any issues are addressed before it then moves each file upon successful completion.

The other is that I would ideally have like the files to have been selected from a browse of the directory the files are on. The problem with this is although I could get it to capture a single file name and parse it through .... I could not figure the method to pick a selection of files to run against.

Any ideas?

Mark

 

[randy700]

I would ideally like is for it to remain in the PreLoad folder until any issues are addressed

Addressed in what way? If you mean for the user to review the imported data, you're going to have to remove the portion of code that moves the files. Maybe create a new button that allows the user to move the files after the data has been accepted?


Randy