Data import from Excel

[Seveni]

Okay, apologies up front for I am not a DBA by any means but I have never had such problems trying to import data in my life, I'm hoping someone here can help me out with this one.

I have an Excel spreadsheet with the following information in each row:
ResponseID, Response1, Response2, Response3,..., Response99

I need to import into an already existing Access table the data from the Excel spreadsheet as follows (each line below basically represents a new entry into the table):

Data from Row 1:
Row1 - ResponseID, Response1
Row1 - ResponseID, Response2
Row1 - ResponseID, Response3
...
Row1 - ResponseID, Response99

Data from Row 2:
Row2 - ResponseID, Response1
Row2 - ResponseID, Response2
Row2 - ResponseID, Response3
...
Row2 - ResponseID, Response99

and so on...

Does this make any sense? I can't for the life of me figure out how to import all of this information. I can, if need be, import this into a SQL DB if it will make any of this easier.

Thanks,
Seveni

 

[BLeslie88]

Here's one way....

Using VBA you could strip each row building a cross tab type of your own.... Meaning import the table as it is, you don't have much of a choice unless you want to run a macro in excel to clean it up first... So if you import the file in like it is now. You would read the file in a loop,

Dim db As Database
Dim RSImportFile As DAO.Recordset
Dim RSResponse As DAO.Recordset
Dim counter as Long
Set db = CurrentDb()
Set RSImportFile = db.OpenRecordset(impTable, dbOpenDynaset)
Set RSResponse = db.OpenRecordset(tblResp, dbOpenDynaset)
If RSImportFile.EOF AND RSImportFile.BOF then
msgbox "Error", vbokonly, "Empty File!"
else
RSImportFile.Movefirst
Do While NOT(RSImportFile.EOF)
counter = 1
Do While counter <= 100
RSResponse.Addnew
RSResponse(0) = RSImportFile(0)
RSResponse(1) = RSImportFile(counter)
RSResponse.Update
RSResponse.Edit
counter = counter + 1
Loop
RSImportFile.Movenext
Loop
End If

This sytax is close, obvisiously I haven't tested it... Hope this helps...

regards

 

[The_Doc_Man]

Since you do not claim a lot of experience in DBA work, I don't dare give you the VBA approach that would make this go fast. Your problem description is also somewhat ambiguous.

In the simplest case, you would import this information to a temporary table in a single operation. Then go into design mode, update the row names to match your target table, then do an append * of the imported info to the master table. But that assumes that you have one table with 100 fields - the response ID and reponses 1-99.

If that is wrong, we need to know what the response table looks like in Access.