import csv files to multiple tables

[Dave_epic]

Hi

I'm using the transfer text method to import multiple csv files, 10 or more.

DoCmd.TransferText acImportFixed, "Import_Specification", "table_name", strFolderPath & objF1.Name, False

as it is it only appends to one table "table_name" , so how can I import each file to a seperate table.
Can't figure it out.

Thanks

 

[DCrake]

What is the source of objF1.Name?

Is there a reason why the files are going into different tables?

Why can't you simply repeat the line of code with a different table name?


More details please....

 

[Dave_epic]

Hi

thanks for your reply.


I am importing to many tables as each file may contain duplicate id's and I need each record to have a unique id. The only way would be to import to one file with a new field with the relevant file name giving it unique status.
I think this can be done using a runsql update query after import but I haven't figured out how to make this work yet.

I could repeat the code with a different file name but that gets a bit messy and time consuming the more files you have, but I may have to in the end.

I have added the full code I am using below.

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
strFolderPath = "C:\test\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "csv" Then
DoCmd.TransferText acImportFixed, "Import_Specification", "table_name", strFolderPath & objF1.Name, False
Name strFolderPath & objF1.Name As "C:\david1\" & objF1.Name 'Move the files to the archive folder
 
End If
Next


Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing
bImportFiles_Click_Exit:
Exit Sub
bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub

 

[Atomic Shrimp]

If the files contain the same kind of data (just different batches of it), then it's probably a bad idea to import them into separate tables, as it means you have to design the rest of your application to talk to an indefinite number of similar tables.

I think you would be better off creating a field in your target table called (something like) 'Batch', then doing a loop where you:

Import the file into a temporary holding table
Append it into the main table, inserting a consistent batch number for the whole set of records (using an incrementing variable in your code)
Clear the temporary holding table
Repeat.

If you ever want just one file's data, it's a simple query with criteria specifying the batch number - and you'll only ever have to write one version of any form, report, etc - just building it so that it specifies the batch it's working on.

 

[DCrake]

If you create a data dump table with 1 extra field

fldSourceFileName (Text 255)



Import the data into the dump table, then run an update query to update the fldSourceFileName with the name of the source path and file name where the field is null.

David

 

[Dave_epic]

Thanks for the advice.
I'll try that