Import Excel Spreadsheet - Move Data to the Correct Record

[j.t.w]

Hello Everyone,

I'm trying to import data that is currently in an Excel spreadsheet
into an Access table. The data (information) looks fine and somewhat
makes sense as you look at it as a spreadsheet. Unfortunately, it won't
import well into a table unless I move cells around before I import or
somehow upon import programmatically move the data to the correct
record. 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. And so
on.

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? Any and all help would be appreciated.
Thank you.
j.t.w

 

[Joseph Meehan]

Hello Everyone,

I'm trying to import data that is currently in an Excel spreadsheet
into an Access table. The data (information) looks fine and somewhat
makes sense as you look at it as a spreadsheet. Unfortunately, it
won't import well into a table unless I move cells around before I
import or somehow upon import programmatically move the data to the
correct record. 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. And
so on.

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? Any and all help would be appreciated.
Thank you.
j.t.w

It looks like you need to merge ABCD EFGH and IJKL so you only have
three columns of data in Excel After than it should import well with the
exception of the data in line one column D That needs to be added to a new
row under row 1.

I suggest if you are going to continue this that you start with your
Excel format and fix it.

I must also add that this will result in poorly normalized database.
That is because in a properly normalized database you would have one table
with just the names and a second table with just the numbers. This will
allow a more efficient and more useful set of data in Access.

Table Names
First Name John Jane ...
Last Name Doe ...
Number 101 ...
Linkfield 1A 2A

Table Data
Number 123 456 789 159 103 ....
Linkfield 1A 1A 1A 1A 2A ...

 

[j.t.w]

Dear Joseph Meehan,

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

I wasn't thinking when I posted my question and as you've suggested,
importing the data into 2 separate tables would be better.

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

 

[Joseph Meehan]

Dear Joseph Meehan,

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

I wasn't thinking when I posted my question and as you've suggested,
importing the data into 2 separate tables would be better.

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

Here is an idea.

Start by copying the number from row #1 rows A, B & C; E, F &G etc. down
to all the rows below where there is no data. I seem to recall that Excel
has a easy to use feature to do this.

Next copy EFGH and past it under ABCD, and likewise IJKL also under ABCD
etc.

Next just import the whole thing, indicating that the first row does NOT
contain field names.

Name the fields during the import like (A = CutomerNo - B=FirstName -
C=LastName - D=Amount)

Access has a split table wizard to split the table into two tables for
you.