Importing Data-Multiple Sources

[iam]

I want to import data from multiple sources.

Each source has multiple entries with 6 fields in each entry.

Is there an easy way during import to assign the souce to each entry
in a 7th field.

field 1 First Name
field 2 Middle Name
field 3 Last Name
field 4 Birth Date
field 5 Death Date
field 6 Inscriptions
field 7 Source

These are all comma delimited text files and I am trying to save
having to add the 7th field to all of the entries in each file
manually. 160 + files. As many as 2000 entries per file.

In other words, on each import, the name read as source would be
"plugged in" to field 7 only on those entries.

 

[Ken Snell]

Two ways come to mind:

(1) If all the files are in one folder, use Dir function to loop through the
files and, on each loop, import a file to a table and then run an update
query that writes the name of the file to the 7th field based on that field
being "empty" (Null?).

(2) Open each file via VBA file manipulation commands, and write each record
from the file to a recordset that is based on the table, and include a 7th
field in the writing process that contains the file name.

Post back with more info about where/how your files are located, and we'll
go from there.

 

[iam]

Two ways come to mind:

(1) If all the files are in one folder, use Dir function to loop through the
files and, on each loop, import a file to a table and then run an update
query that writes the name of the file to the 7th field based on that field
being "empty" (Null?).

(2) Open each file via VBA file manipulation commands, and write each record
from the file to a recordset that is based on the table, and include a 7th
field in the writing process that contains the file name.

Post back with more info about where/how your files are located, and we'll
go from there.

All of the files are in one folder. I was trying to avoid importing
each file into it's own table.
IE: Creating 160 separate tables.

The whole point of what I want to do is merge the 160 souces into one
massive alphabatized listing that lookups by last name can be pulled
from but still retain original source notation.

Putting all the eggs in one basket so each location does not have to
be searched separately is the whole point of the project.

 

[Ken Snell]

Ok -- this isn't as difficult as it may sound.

First, create a temporary table to which the records will initially be
imported. You'll want 6 fields:
FirstName text-formatted
MiddleName text-formatted
LastName text-formatted
BirthDate date/time-formatted
Death Date date/time-formatted
Inscriptions text-formatted

Name the table "tblRecords" for now.

Then create a second table to which the "completed" records will be copied.
You'll want 8 fields:
FirstName text-formatted
MiddleName text-formatted
LastName text-formatted
BirthDate date/time-formatted
Death Date date/time-formatted
Inscriptions text-formatted
FileSource text-formatted
RecPK autonumber (primary key)

Name this table "tblFinal" for now.

Third, put this code in a regular module (this code assumes that the files
are in the path C:\MyFolder\ and that all files end with ".csv" and that
there are no other files in that folder and that there are no header rows in
any of the files):

Public Sub GoGetMyRecords()
Dim strFileName As String, strSQL As String
Dim dbs As DAO.Database
Const strPath As String = "C:\MyFolder\"
On Error GoTo Err_Code
Set dbs = CurrentDb()
DoCmd.Set Warnings False
strSQL = "DELETE * FROM tblRecords;"
dbs.Execute strSQL, dbFailOnError
DoCmd.Set Warnings True
strFileName = Dir(strPath & "*.csv")
Do While strFileName <> ""
DoCmd.TransferText acImportDelim, , _
"tblRecords", strPath & strFileName, False
strSQL = "INSERT INTO tblFinal (FirstName, " & _
"MiddleName, LastName, BirthDate, Death Date, " & _
"Inscriptions, FileSource) SELECT tblRecords.*, """ & _
strFileName & """ AS Sour FROM tblRecords;"
DoCmd.Set Warnings False
dbs.Execute strSQL, dbFailOnError
strSQL = "DELETE * FROM tblRecords;"
dbs.Execute strSQL, dbFailOnError
DoCmd.Set Warnings True
strFileName = Dir()
Loop
ExitCode:
On Error Resume Next
dbs.Close
Set dbs = Nothing
DoCmd.Set Warnings True
Exit Sub
Err_Code:
MsgBox "Error occurred: (" & Err.Number & ") " & _
Err.Description
Resume Exit_Code
End Sub


Fourth, run this code.

 

[Ken Snell]

Had a slight brain freeze while typing this code. You can delete all the
code lines that start with
DoCmd.SetWarnings

They're not needed in this code. Sorry.

 

[iam]

Thanks Ken,

I'll have to study this a bit, programming doesn't come too easily to
me. : )

But, from what I see initially it's exactly what I want so I do thank
you for your knowledge and expertise.

 

[Ken Snell]

Be sure to post back with questions and how it works!