Prepare Spreadsheet for Import into Access Table

[j.t.w]

Hello Everyone,

I'm trying to prepare some data that is currently in an Excel
spreadsheet to be imported into an Access table. The data (information)
looks fine and somewhat makes sense as you look at it as a spreadsheet
but, it won't import well into a table unless I move cells around
before I import. I would like to automate this process if at all
possible, as there are many spreadsheets in this format.

Here is what the spreadsheet looks like...

.. A B C D E F G H I J K L
1 101 John Doe 123 102 Jane Doe 459 103 Mary Smith 985
2 456 126 653
3 789 378 167
4 159 453 439

The numbers in column D, H, and L are dollar amounts. The amounts in
column D belongs to 101 John Doe. The amounts in column H belongs to
102 Jane Doe. The amounts in column L belongs to 103 Mary Smith.

Here is what I would like...

.. A B C D E F G
1 101 John Doe 123 456 789 159
2 102 Jane Doe 459 126 378 453
3 103 Mary Smith 985 653 167 439

There are usually more than 3 people(typically 12 to 20, or sometimes
even more), which is why I would like to automate this. Could someone
point me in the right direction or have any suggestions on how to
accomplish this? Should this be done on the Excel or Access side? Any
and all help would be appreciated.

Thank you.
j.t.w

 

[JulieD]

hi

admittedly without knowing your data (and not trying to be rude or
anything), i find it interesting that you want to export in the format that
you do, as i would think that this indicates a non-normalised database ... i
would have thought that in an access database the person information
(columns A, B & C) would go in one table with column A as the primary key
and the payments would go in another table (columns A & D) where each
payment was a separate record. If your database was set up like that it
would make importing the data quite easy. However, as i said i don't know
your data so i might be totally off-track here - but if you'ld like to
explore this concept further if you'ld like to outline the purpose of your
database i'll be happy to discuss it with you.

Alternatively, going with what you originally asked - does each person only
ever (and always) have four payments or are there more?

Cheers
JulieD

 

[Jamie Collins]

The data (information)
looks fine and somewhat makes sense as you look at it as a spreadsheet
but, it won't import well into a table unless I move cells around
before I import.

the following may be of use to you:
http://j-walk.com/ss/excel/usertips/tip068.htm

Jamie.

--

 

[j.t.w]

Hi JulieD,

Thank you for responding. Sorry I didn't get back with you earlier.

You are absolutely correct about importing the data where "the person
information (columns A, B & C) would go in one table with column A as
the primary key and the payments would go in another table (columns A &
D) where each payment was a separate record". I wasn't thinking when I
posted my question.

Could you help me with making this a reality? How would I go about
preparing the excel spreadsheet to import the data as you suggest?

As of right now (as far as I can tell), there are only four dollar
amounts per person, which hopefully makes this easier. Although,
sometimes there are blank dollar amounts in the 2nd, 3rd, or 4th rows,
which I would consider 0.

Again, thanks for your help.

j.t.w

 

[Jamie Collins]

You are absolutely correct about importing the data where "the person
information (columns A, B & C) would go in one table with column A as
the primary key and the payments would go in another table (columns A &
D) where each payment was a separate record".

How would I go about
preparing the excel spreadsheet to import the data as you suggest?

No preparation required. See the below code:

Sub test()
Set con = CreateObject("ADODB.Connection")
With con
..Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyJetDB.mdb"
..Execute _
"CREATE TABLE ReferencedTable (" & _
" employee_ID INTEGER NOT NULL," & _
" lname VARCHAR(35) NOT NULL," & _
" fname VARCHAR(35) NOT NULL," & _
" CONSTRAINT pk__ReferencedTable" & _
" PRIMARY KEY (employee_ID));"
..Execute _
"CREATE TABLE ReferencingTable (" & _
" employee_ID INTEGER NOT NULL," & _
" occurence INTEGER NOT NULL," & _
" earnings CURRENCY NOT NULL," & _
" CONSTRAINT pk__ReferencingTable" & _
" PRIMARY KEY (employee_ID,occurence)," & _
" CONSTRAINT fk__ReferencingTable_ReferencedTable" & _
" FOREIGN KEY (employee_ID)" & _
" REFERENCES ReferencedTable (employee_ID)" & _
" ON UPDATE CASCADE ON DELETE CASCADE);"
..Execute _
"INSERT INTO ReferencedTable" & _
" (employee_ID, lname, fname)" & _
" SELECT F1 AS employee_ID," & _
" F3 AS lname, F2 AS fname FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MySheet$];"
..Execute _
"INSERT INTO ReferencingTable" & _
" (employee_ID, occurence, earnings)" & _
" SELECT F1 AS employee_ID," & _
" 1 AS occurence, F4 AS earnings FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MySheet$]" & _
" WHERE NOT (F4=0 OR F4 IS NULL);"
..Execute _
"INSERT INTO ReferencingTable" & _
" (employee_ID, occurence, earnings)" & _
" SELECT F1 AS employee_ID," & _
" 2 AS occurence, F8 AS earnings FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MySheet$]" & _
" WHERE NOT (F8 = 0 OR F8 IS NULL);"
..Execute _
"INSERT INTO ReferencingTable" & _
" (employee_ID, occurence, earnings)" & _
" SELECT F1 AS employee_ID," & _
" 3 AS occurence, F12 AS earnings FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\MyWorkbook.xls;].[MySheet$]" & _
" WHERE NOT (F12 = 0 OR F12 IS NULL);"
..Close
End With
End Sub

The table/column names are for demo purposes; choose alternatives
appropriate to your data. Note it would be better to use start_date and
end_date columns in place of my single occurrence column. Using the
occurrence column may make future INSERTs more difficult e.g. to find
the MAX(occurance) for the employee_ID, perhaps best done in a
PROCEDURE.

Jamie.

--