Excel to Access (1 Viewer)

mjdemaris

Working on it...
Local time
Today, 10:23
Joined
Jul 9, 2015
Messages
424
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

Super Moderator
Staff member
Local time
Today, 17:23
Joined
Feb 19, 2013
Messages
16,553
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:23
Joined
Feb 19, 2002
Messages
42,971
I would do this one workbook at a time. As soon as the data has been imported, I would move the spreadsheet to an archive folder which is read only because you don't want people accidentally updating the spreadsheet once the data resides in Access.

I would always link to the spreadsheet and use append queries do do the clean up as the data is being appended to the permanent tables. If you need to manually review it or use VBA to fix up the data, then append to temp tables, clean up there and then append to the permanent tables. Again, I caution you to lock down the spreadsheet as soon as you start working on importing any spreadsheet. Hopefully you can get each one imported in a short amount of time so updates are not held up.

As you refine your methods and queries, each subsequent sheet should be easier.
 

mjdemaris

Working on it...
Local time
Today, 10:23
Joined
Jul 9, 2015
Messages
424
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.
 

mjdemaris

Working on it...
Local time
Today, 10:23
Joined
Jul 9, 2015
Messages
424
Yeah, I can see why companies charge so much for data migration!
 

plog

Banishment Pending
Local time
Today, 12:23
Joined
May 11, 2011
Messages
11,611
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:23
Joined
Feb 19, 2002
Messages
42,971
When converting data, the way to eliminate duplicates is to create the proper unique indexes on the target tables. Then sort the data in the append query so that the "newest" or "biggest" or whatever sorts first. That row gets appended. The rest are discarded as duplicates. Of course if you have to examine each record manually, that's a big job. Put the data into a temp table and add a dup column. In your append query, ignore the rows with dup = true.
 

mjdemaris

Working on it...
Local time
Today, 10:23
Joined
Jul 9, 2015
Messages
424
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

Nifty Access Guy
Staff member
Local time
Today, 17:23
Joined
Jul 9, 2003
Messages
16,244
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..
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:23
Joined
Feb 19, 2002
Messages
42,971
The problem with queries that identify duplicates is that the set includes the original as well as all the copies. To get ONE of the duplicates out, you can do what I suggested by adding proper indexes to the target table if you don't care which row is selected or if you can sort by something that will make the row you want "first". If you "care" what row is selected, you either have to mark it manually or come up with some standard criteria that can be used to isolate it using a query.
 

Users who are viewing this thread

Top Bottom