Automating splitting a flat file into related tables.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to import an csv flat file into an existing database. Once the flat file has been imported, I want to split the columns of data into related tables (1:M). The primary keys of the first table is an autonumber. So although I can run a very simple append query to add the data to the first table, when it comes to appending the data to to the second table, the query doesn't know which autonumber was assigned to the record in the first table, and so can't link the related records
Because of the databases other functionality, I can't change any of the properties of the table. Is there a simple solution to this problem
If it is not entirely clear what I'm trying to do, please let me know, and I will try and clarify

Many Thank

Rob
 
Rob

Unless that imported flat file has some unique identifier in it, I don't see
how you could parse it out to separate related tables. The Autonumber is a
field added to what you parse into the first Access table, so, as you
mention, the flat file has no connection to it.

If you were going to do this manually, without a computer, how would you
break the flat file apart into your 1:M related tables?
 
Rob R.,

As a last resort, write some VBA code, create a recordset copy of the
table, and iterate through it one row at a time, checking the column values
for breakpoints, and then Appending (INSERTING) into the "many" table
whenever appropriate.


Rob R said:
I am trying to import an csv flat file into an existing database. Once the
Click to expand...
flat file has been imported, I want to split the columns of data into
related tables (1:M). The primary keys of the first table is an autonumber.
So although I can run a very simple append query to add the data to the
first table, when it comes to appending the data to to the second table, the
query doesn't know which autonumber was assigned to the record in the first
table, and so can't link the related records.
Because of the databases other functionality, I can't change any of the
Click to expand...
properties of the table. Is there a simple solution to this problem?
 
Back
Top