Combine tables

[Bill Smith]

I received a database where 160 .csv files were imported as individual
tables. I need to combine these records into one .csv file. The table
structures are identical. Any suggestions? There are about 1200 records per
table.

 

[Klatuu]

You can do it with an append query, but with 160, that would take a while.
How are the table names structured? If there is any consistent naming, you
could use a for next loop to interate through the tables, change the name of
the source table in VBA, and append each table to one master table.

Let me know how your tables are named, and perhaps I can help with a solution.

 

[Bill Smith]

You can do it with an append query, but with 160, that would take a while.
How are the table names structured? If there is any consistent naming,
you
could use a for next loop to interate through the tables, change the name
of
the source table in VBA, and append each table to one master table.

Let me know how your tables are named, and perhaps I can help with a
solution.

Thanks Dave,
Tables are named with 5 digit numbers. I could rename them if need be.

-Bill

 

[Klatuu]

Are the numbers sequential
10001
10002
10003
etc.

If so, are there any gaps?
Also, post a couple of table names as examples, please.

 

[Bill Smith]

Are the numbers sequential
10001
10002
10003
etc.

If so, are there any gaps?
Also, post a couple of table names as examples, please.

No, they are zip codes by the looks of them. I can make them sequential. It
would only take a couple of minutes.
-Bill

 

[Klatuu]

That would be helpful. Once you have that done, here is a routine you can
use to append all the data to one table. I would start with the first table
and give it the name you want to use in your application. Then make the
others tbl001 through tbl159 (or how many you have).

now paste this into module. You can run it from the immediate window using
Call CombineTables

Public Sub CombineTables
Dim strFileName As string
Dim lngCtr as Long
Dim strSQL as String
With CurrentDb
For lngCtr = 1 to 159 'Change 159 to the highest number table name
strFileName = "tbl" & Format(lngCtr,"000")
strSQL = "INSERT INTO tblCombined SELECT " & strFilename & ".*
FROM " & strFileName & ";"
.Execute(strSQL), dbFailOnError
Next lngCtr
End With
End Sub

Change tblCombined to the name of the destination table.

 

[Douglas J. Steele]

Rather than rename the tables, create a table that contains the table names
and put a Yes/No field to indicate which ones you want to append.

To get a list of all tables in a given database, you can use

SELECT Name
FROM MSysObjects
WHERE Type = 1
ORDER BY Name

(for linked tables, Type is 6, for tables linked through ODBC, it's 5)

 

[John W. Vinson]

I received a database where 160 .csv files were imported as individual
tables. I need to combine these records into one .csv file. The table
structures are identical. Any suggestions? There are about 1200 records per
table.

To append all tables with five-digit number names into a table named MASTER
you could use code like:

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim tdf As DAO.Tabledef
On Error GoTo Proc_Error
For Each tdf In db.Tabledefs
If Len(tdf.Name) = 5 And IsNumeric(tdf.Name) Then
strSQL = "INSERT INTO Master SELECT * FROM " & tdf.Name
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
End If
Loop
Set qd = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
<do approprieate error handling>
Resume Proc_Exit


You probably should explicitly include the fieldnames into strSQL but if they
(and Master) are all identical this should work.

John W. Vinson [MVP]

 

[Bill Smith]

That would be helpful. Once you have that done, here is a routine you can
use to append all the data to one table. I would start with the first
table
and give it the name you want to use in your application. Then make the
others tbl001 through tbl159 (or how many you have).

now paste this into module. You can run it from the immediate window
using
Call CombineTables

Public Sub CombineTables
Dim strFileName As string
Dim lngCtr as Long
Dim strSQL as String
With CurrentDb
For lngCtr = 1 to 159 'Change 159 to the highest number table name
strFileName = "tbl" & Format(lngCtr,"000")
strSQL = "INSERT INTO tblCombined SELECT " & strFilename & ".*
FROM " & strFileName & ";"
.Execute(strSQL), dbFailOnError
Next lngCtr
End With
End Sub

Change tblCombined to the name of the destination table.

That did it. Thanks Dave!
-Bill

 

[Bill Smith]

Rather than rename the tables, create a table that contains the table
names and put a Yes/No field to indicate which ones you want to append.

To get a list of all tables in a given database, you can use

SELECT Name
FROM MSysObjects
WHERE Type = 1
ORDER BY Name

(for linked tables, Type is 6, for tables linked through ODBC, it's 5)

Thanks Doug.
I read your post a little too late, but I'm filing it for future use.
Thanks! -Bill

 

[Bill Smith]

To append all tables with five-digit number names into a table named
MASTER
you could use code like:

Dim strSQL As String
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim tdf As DAO.Tabledef
On Error GoTo Proc_Error
For Each tdf In db.Tabledefs
If Len(tdf.Name) = 5 And IsNumeric(tdf.Name) Then
strSQL = "INSERT INTO Master SELECT * FROM " & tdf.Name
Set qd = db.CreateQuerydef("", strSQL)
qd.Execute dbFailOnError
End If
Loop
Set qd = Nothing
Proc_Exit:
Exit Sub
Proc_Error:
<do approprieate error handling>
Resume Proc_Exit


You probably should explicitly include the fieldnames into strSQL but if
they
(and Master) are all identical this should work.

John W. Vinson [MVP]

Thanks John!
-Bill