Recordset Read and Table Update Question

[des0906]

Greetings. I have a project where I must import an Excel spreadsheet into a table, but the spreadsheet format has a single row with an ID value, and then three associated rows across four columns that go with that ID value. The end result must be a single row of data for each ID value.

Example

Field1 Field2 Field3 Field4

ID Text Text Text
Text Text Text
Text Text Text

My thought is a two stage import: Step 1 would be importing into an interm destination table, where each row in Field1 would be populated with the proper ID value for all three rows. Step2 would then be reading and parsing out the values so that all data associated with that ID ends up on a single row.

Any thoughts on how to accomplish this? I have some ideas but before I spend hours going down the wrong track I thought I would would go where the gurus gather....

Thanks

 

[xiong]

Sounds like you are on the right track. I'm assuming you want the three values for Field2-4 combined into a single string:

Field1 Field2 ... Field4
ID Text1, Text2, Text3 ... Text1, Text2, Text3




"Only the educated are free.

 

[des0906]

Not a single string, actually, but a single row. My final destination table has the following fields:

(AvayaID, AnnouncementNo, Label, Description, Announcement, Valboard)

The Excel spreadsheet I am importing from is laid out as follows, actually:

Field1 Field2 Field3 Field4

Row1 ID AnnouncementNo Label Valboard
Row2 Vector
Row3 Announcement

My interm import table is laid out exactly the same as the Excel spreadsheet. I want to populate Row2 and Row3 Field1 with the ID so I can then process that table and place all of the pertinent information in a single row in my final destination table.

 

[xiong]

Assuming that your Field2 values will validate as text values...

Use three seperate import processes. In the first step, import the entire spreadsheet as a single table and populate the Field1 with your ID's (alter the ID by adding an alpha character (102a, 102b, 102c, etc). Then run SQL update queries to add the main record with AnnouncementNO, Label, and Valboard to a new table, then again to import the Vector and Announcements to the appripriate fields. You can remove your alpha characters with a Left statement, and by identifying the alpha char, update the correct values to the appropriate field:

Assume:
Field1 Field2 Field3 Field4
102 AnnouNOA LabelA ValboardA
VectorA
AnnouncA
103 AnnouNOB LabelB ValboardB
VectorB
AnnouncB
104 AnnouNOC LabelC ValboardC
VectorC
AnnouncC

Import to New Table as:
Rec# Field2 Field3 Field4
102A AnnouNOA LabelA ValboardA
102B VectorA
102C AnnouncA
103A AnnouNOB LabelB ValboardB
103B VectorB
103C AnnouncB
104A AnnouNOC LabelC ValboardC
104B VectorC
104C AnnouncC

SQL Update...Where Right(Rec#,1)=A to New Table
SQL Update...Where Left(Rec#,3)=New.Rec# to New.Field3/4




"Only the educated are free.