Creating a list of Tables from CSV file

[CJSwed]

Hi.. just wondering if there is a way to do this.. I have a list of 100 tables that need to be made, I have a list of each table name in a excel file.. can I have access create a table for each name on this list? or am I trying to automate things a bit to much by trying to do this.

 

[MajP]

You description and title are not the same, so not sure of the requirement. Either one is probably doable. Does the excel file list the paths to CSVs, and the CSVs are supposed to be made into tables? Are the CSVs data, or a description of the table structure?

 

[CJSwed]

Maj basically I have a list in an excel sheet that looks like

TableA
TableB
TableC


and so on.. what I would like to do is take a new db and somehow import so that a table is made for each name in the list (TableA, TableB, etc) As for the table structure, I could manually set that up later unless there was a way to tell it to have 3 columns in each table but not sure if there is a way to do that, my main concern is to have a table for each name right now, the table structure I could always manually set up when I enter data into each individual table.

 

[MajP]

I got to run, but yes that is easy to do. Access has a tabledef collection and a tabledef object. So you would loop your excel table, create a new tabledef and add to the tabledef collection. As you add it you can define every property of the table to include
name,
fields and all field properties
primary keys,
indices,
etc.

In your excel you could put the field names and their datatypes.

I will see if I can do an example later. But read up on the tabledef collection

http://msdn.microsoft.com/en-us/library/bb177479(office.12).aspx

 

[PHV]

Why not simply generate a CREATE TABLE sql instruction for each row of your excel table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CJSwed]

PH, this is why I came here to ask.. great idea. A simple search and I found

CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",
... )



I could simply copy and paste the table into a table and use a SQL statement to write the SQL! Ha! Looks like the whole thing wouldnt take more than 10 minutes.. that is assuming it works as planned! lol

Great idea! Assuming it goes as planned and makes a table for each and every name. I will give it a shot a bit later tonight...

Majp, thanks for the tips as well, that looks complex but I will give it a shot if the above does not work.

Thanks guys! Ill post back just for anyone who may find this thread and let them know the outcome!

Chris

 

[Golom]

The fly in the ointment here (and there is always one) is that you can't create an empty table (i.e. one with no fields.)

You can certainly do what PHV suggests but you will have 100 tables that contain fields that are probably dummy fields that bear little relation to the data that you will eventually want to put into them. When you do get around to populating them you will need to remove or rename those dummy fields and/or insert new fields to match tha data names and types that you want in the tables.

Neither of those activities is particularly difficult but they do require a lot more meta-data than just generating 100 tables with dummy fields.

There may also be a design issue to consider. Generally a table roughly corresponds to an entity in your system. Do you really have 100+ distinct logical entities that you need to track? Is it possible that you are creating separate tables to distinguish slightly different characteristics of otherwise similar blocks of data? If so, you may want to look at all those different table names as data values in a table rather than entity names implying different tables.

 

[BigRed1212]

I concur with Golom. I suspect a larger design issue.

As an example, If table1 is sales for unit one, and table2 is sales for unit two, etc ad infinitum, then you may want something more like

recordid  salesunit  salesamount  date
1         1          100          5/5/12
2         1          50           7/12/19 
3         2          200          12/25/01
4         3          40           5/1/10