The best way to handle excel data imported to Access

  • Thread starter Thread starter Frank Situmorang
  • Start date Start date
F

Frank Situmorang

Hello,

I have finished distiributed the trial version of my church membership data
bases to more than 100 churches of my denomination.

Their comments are that they like the database, but the challange for me
now, that they ask me to be able to migrate their excel data into access,
since keying in thru the form will take quite a long time.

Any idea to handle this problem?, This is what I am going to do, please
suggest or correct me if I am wrong:

1. There are 2 important table which are
a. member table
b. Address Table
I want to export this table as templates where they will copy the data
from their excel. I will send these 2 excels and ask them to fill it

2. I will also aske them not to key in foregin key ( for example iddress id
in membertable), because after I succesfully imported their excell, I will
return the file the database to them and let them choose the idress in a
member form combo box

I prefer this way, since migration is only once in a life time, while
current record will be keyed in the form.

I appreciate if any idea to handle this kind of mass requests ( could be
more then 50 potential cllients). Making a menu is quite difficult and tricky)

Thanks in advance.
 
Will all of the Excel sheets from the various churches have the same column
names? This will be important.

One approach you may consider is writing instructions for them to give
specific columns specific names in their Excel sheets. That way, you can
import them pretty easily or link to them and use an append query to add the
data to your tables.

For example:
To correctly import your Excel data into the database, the first row of your
spreadsheet must contain the following names. The order of the columns is
not important. It is important you begin the column names in cell A1. The
rows with member data must begin in row 2. Do not leave any blank rows in
your spreadsheet. The following column names must be used so the Access
database will be able to find the correct values and automatically enter them
in the correct locations in the database.
The Column where the member's home telephone number must be named
HOME_PHONE. etc...
 
Dear Klatuu:


Thanks for your idea. To overcome that other churches will have different
column name, that is why I export first to excel the sample data from my
database. and then I will ask them to copy paste/valuie to my worksheet as a
template to be imported later after they copy pasted to this worksheet.

Since the columname of my exported excell is not always understood ( moslty
in abreviated) I put in the 2nd row the understood column name, an then when
they copyied from their worksheet to my worksheet, then I will delete the
renamed/understood column name, leaving the orginal column name.

My problem now, how if the date column of churches are different style,
there will be dd/mm/yy or mm/dd/yy or dd_mmm_yy. I do not know yet how can
I normalize/ cutomize it tob in my format: dd_mmm_yy

How can I normalize all their worksheet to my worksheet when they are doing
copy paste. What should I aske them to do, is it better to do copy value, not
copy paste?

I am the developer of the database by the help of this forum.

Thanks for any idea.
 
Back
Top