Excel to Access

[mjdemaris]

Hello,
I’m working on this program (Requisition program in access) that is replacing an Excel version. I’ve posted various questions related to this project already, but this is regarding the Excel program. We keep a history of parts and supplies in Excel (and now Access). I’ve been able to copy (import really) most of the history data into Access and normalize it, but that takes a lot of time and figuring out how to keep the history categorized by department, check for spelling errors, remove duplicates, remove trailing/leading spaces and apostrophes, etc.

Now, the time has come again to move more data into Access, because we just released the Access version for everyone (though it still has a few quirks). I have a few months’ worth of data that I’d like to move into Access. Each department has its own requisition history sheet.

We are in a networked, multi-user environment; I believe full permissions on network folders. (Don’t know if this makes any difference, but we use the MS domain/group policy stuff – sorry don’t know the correct wording.)
It took me a long time the first time I moved data over – mainly trying to normalize the incoming data and spelling errors!

And, no, we have not removed the Excel app from use, yet, just in case there was a major issue with the Access app.

My questions:
Is it easier to clean up the data in Excel, so that the import to Access is smoother?

Would it make sense to copy all data into one sheet first or not?

Would it make sense to create a connection to the Excel workbook, and use recordsets to import or copy the data? If so, what type of connection should I use?

What are some of the ways you guys have dealt with this?

Thank you again for your help!

 

[CJ_London]

Is it easier to clean up the data in Excel, so that the import to Access is smoother?

generally speaking, yes

Would it make sense to copy all data into one sheet first or not?

probably, but not essential

Would it make sense to create a connection to the Excel workbook, and use recordsets to import or copy the data? If so, what type of connection should I use?

depends how big the dataset is - if only a few thousand records, then link (linked table manager) and use append queries, if more, import to a temporary table, index it and use append queries

What are some of the ways you guys have dealt with this?

all of the above. It depends on volumes. For a one time exercise, choose what is easiest for you

 

[mjdemaris]

CJ: nothing about this seems easy, lol.
Pat: how would I go about cleaning the data through an append query? Are you talking about normalization or extra characters or duplicates?
The problem I have found with duplicates is that I don't have an easy way to delete them -if I use the query wizard to find them, then I have to go line by line and delete the duplicate record(s), while retaining one good record.

 

[CJ_London]

you asked if it was easier

 

[mjdemaris]

Yeah, I can see why companies charge so much for data migration!

 

[plog]

And, no, we have not removed the Excel app from use, yet, just in case there was a major issue with the Access app.

I think this needs to be in the back of your mind as you import data for the first time. Specifically, that the first time will not be the last time you do this. That means, document the import process, refine it and make it as easy as possible for the next time you do it.

Having gone through similar processes, my guess is there will be many importations not just a couple. And as you are discussing, there are many ways to skin this cat. Be sure you choose just one skinning method and follow it each time you import otherwise that can lead to inconsistent data being added to the new system.

 

[mjdemaris]

plog: the first time i did not document the process well enough...but this time will be a bit different.

Pat: interesting idea with the dup field. It sounds like part of that process would be manually checking each box if it's a duplicate.

What about using recordsets to check for duplicates? I remember reading somewhere that it's possible to do it...but then again, that requires the data being checked to be exact, which would not catch extra spaces, apostrophes, etc.

Well, since the first import was quite a while ago, it looks like this road is going to be well mapped this time around, lol.

 

[Uncle Gizmo]

What are some of the ways you guys have dealt with this?

I did a blog about the issues around transferring Excel data into MS Access.

The blog is called Excel in Access

There are text examples along with instructions. I also show video instructions along with a tool to help you shift your data from the flat file type often found in Excel and convert it to a format suitable for MS Access..