Import Spreadsheet Data into Access

[mmitchell]

I am trying to import some data from an Excell spreadsheet into Access, but not every row contains the same data.

Basically I have one row that contains the Parent information and then rows directly underneith it that contain the child data, and the child data can be in various number of rows, so it is not constant.

1. Each Parent row has the word "Transformer" or "Transformer 2 Data" in column C.
2. Each Child row has a number from 1 to x in column C, and is directly below its parent.
3. Each row has its own ID in column A

I need to import the data and have the child data be linked to the Parent data.

I was thinking of importing into two Access tables one for the Parent and one for the Child.

Is there some way to automate this so that the child records all reference the parents ID? Maybe using VBA/macro in Excell or import the whold think into Access and do something in VBA??

A            B               C           D
ID	SUBSTATION	RECLOSER #	TYPE/RATING
===================================================================
5195	Grape 12470	Transformer 2 DATA (EAST)	"ABB 22.4 @ 65"
5196	Grape 12470	1	ABB
5197	Grape 12470	2	ABB
5198	Grape 12470	3	ABB
5199	Grape 12470	4	ABB

5200	Grape 12470	Transformer 1 DATA (WEST)	"ASEA 22.4 @ 65"
5201	Grape 12470	5	ABB
5202	Grape 12470	6	ABB
5203	Grape 12470	7	ABB
5204	Grape 12470	8	ABB

5206	Logan 4160/12470	Transformer 1 DATA	"Waukesha 22.4 @ 65"
5207	Logan 4160	1	W
5208	Logan 4160	2	W
5209	Logan 4160	3	W
5210	Logan 4160	4	ABB
5211	Logan 4160	5	AC
5212	Logan 4160	6	ABB
5213	Logan 4160	7	ABB

5214	Russ Street 12470	Transformer 1 DATA	"US Tran 10.5 @ 65"
5215	Russ Street 4160	3	SA
5216	Russ Street 12470	4	AC
5217	Russ Street 12470	5	W

5218	Russ Street 4160/12470	Transformer 2 DATA	"ABB 14.0 @ 65"
5219	Russ Street 4160	1	AC
5220	Russ Street 4160	2	AC
5221	Russ Street 4160	6	W
5222	Russ Street 4160	7	W
5223	Russ Street 4160	8	AC

So, for example:
The parent is:
5195 Grape 12470 Transformer 2 DATA (EAST) "ABB 22.4 @ 65"

The Children are:
5196 Grape 12470 1 ABB
5197 Grape 12470 2 ABB
5198 Grape 12470 3 ABB
5199 Grape 12470 4 ABB

I need all the children to reference the parents ID which is 5195.

 

[llkhoutx]

Import into one table and then split your data to the parent and child with a query, DAO or ADO. You're going to have datatype problems on import and can fix the datatype when you split the data. This code (import, and two queries or and ADO/DAO routine) will run very fast.