Read a formatted excel file into MS access 2010 table

Question

A task is assigned to me to read the data from multiple same formatted excel files and save the data in the MS Access as a table. I would like to store these data in database format, is there anyone who had the experience and can provide some idea? I'm thinking to flag each role with ID, then read one by one, a lot of work.....Help!

Answer 1

go to the ribbon's External tab, use the excel icon - and then the wizard will open to step you thru the import of excel into a table.

note you should prep the excel first - but making it a clean grid with the headers in row 1 (if there are column names) and no extraneous rows....  make it a neat grid of data and remove extraneous dialog and empty rows.

extra columns are not an issue they will be ignored

Answer 2

Thanks for your reply. The current issue is that we cannot change the source file format, the users submit it on a monthly basic. The multiple excel reports are submitted in a same format. We need to load every single cell which contains the data into MS Access in a dataset format. I have attached one sample. I'm thinking that either we find a way to clean up the source files and put them all together, or we can have mechanism to load into access by programming somehow.

Answer 3

You show how the Excel looks but not how you want your Access tables to be organized.

---

Build a little, test a little

Answer 4

Are you allowed to make a COPY of the Excel file?

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 5

Anything can be done given enough time and effort but there comes a point where you have to ask yourself WHY?

What exactly are you trying to achieve? What is the end product of this process?

Answer 6

You don't mention your VBA skill.

The Excel object model has great programmability to strip apart Excel data by cell. For non-tabular source like this, it's a better place to start from. VBA existing in Excel can also operate Access programmatically at the same time.

Were this my problem, I'd start with a new Excel workbook/VBA module, write some code to find/open the source workbooks iteratively. Then, for each relevant worksheet, use the Excel object model to seek out the data rows/columns/cells according to the known Excel format. Once each row is captured in VBA, I'd either a) use VBA to save the data into a new tabular worksheet that Access would be a lot happier importing or linking to, or b) go the full Monty and just open Access and the target database via VBA and insert rows into the target table. (There is no particular reason you couldn't keep the VBA solution entirely within Access and open Excel and the source workbooks from there. I'd tend to start on whichever side I thought was where the heavy-lifting of the problem needed done. My reaction to your not very tabular data is why I said I'd start in Excel.)

This is not for the first time Office VBA programmer. Unless they are willing to learn a lot. But it is thoroughly do-able and well within Office VBA's strike zone. Google is your friend. I'd start somewhere like "excel vba iterate workbooks", "excel vba get data from worksheet range"  and "access vba insert records".

Answer 7

if they are excel files sent to you; then copy them and prep them as per my first post so they can be imported.....

but you have data structure issues, not uncommon in the transform from excel to a database (a generic issue not specific to Access)

you have side-by-side layout where a database works with stacked rows; in a database there would be a single Ratio and single CAD$ field/column - and then the data would be entered as rows with the quarter 1 and quarter II stacked - you would have a field for the Time Period that would differentiate quarters....

you then either have to transform them back to a cross tab type display - or work with more normalized stacked display in order to do the variance calculations.....

Answer 8

Basically, what I like to achieve is to convert this format to dataset, so that I can import into any database entity. This format is uncommon data source. I'm trying to find an easier way to either get it converted in excel or program it done at database level. Any idea is appreciated. Yes, we can copy the file.

Dick, thanks for your suggestion, I know a little bit VBA, but not much. Would you be able to provide some sample code that related to this case? Thank you

Answer 9

 I'm trying to find an easier way to either get it converted in excel or program it done at database level.

I repeat -- You show how the Excel looks but not how you want your Access tables to be organized.

---

Build a little, test a little

Answer 10

Why not create an Access app and issue that to the people who are entering the data?

---

peter n roth - http://PNR1.com, Maybe some useful stuff

Answer 11

Thanks for your reply. The current issue is that we cannot change the source file format, the users submit it on a monthly basic. The multiple excel reports are submitted in a same format. We need to load every single cell which contains the data into MS Access in a dataset format. I have attached one sample. I'm thinking that either we find a way to clean up the source files and put them all together, or we can have mechanism to load into access by programming somehow.

This is how you would expect data in a Report to be viewed however, this doesn't really indicate what the true dataset is. One could conjecture that A - H are Record ID's and each would be a different dataset. If this is the case then the Records in a dataset would be more like...

Record ID	Ratio	CAD$	Ratio	CAD$
1	0.5	9	0.2	4
2	0	0	0	0
3	0	0	0	0
4	0	0	0	0
5	0.5	9	0.2	4
6	0	0	0	0
7	0.5	0	0.2	0
8	0	0	0	0

This doesn't really look right but it isn't easy getting an understanding of your real world model for a database. We will need additional clarity before we can really begin to help you properly import your data into a database.

Since this does look like a report, I will venture a guess that the data in this Report in Excel actually is filled from another are of the spreadsheet or even from another spreadsheet. Look at the cell values and see if those are really containing the values that you see or formulas or references to other locations in the workbook or even another data source. As it is you would be better served to manually enter the data than manipulate what you see into something importable.

Having said that, you can import specified areas of a spreadsheet.

Is the data displayed the extent of the data example? Does it really model what you need to achieve. There is another issue with the layout of your data if it is the "dataset". Normalization. Study up on CODDs rules of Normalization.

Hth

---

Just takes a click to give thanks for a helpful post or answer.
Please vote “Helpful” or Mark as “Answer” as appropriate.
Chris Ward
Microsoft Community Contributor 2012

Answer 12

This excel file is just one row of a record (item), if it is stored in database and should be like this in the dataset

Record ID	A-IRatio	A-ICAD$	A-IIRatio	A-IICAD$	B-IRatio	B-ICAD$	B-IIRatio	B-IICAD$	C-IRatio	C-ICAD$	C-IIRatio	C-IICAD$	D-IRatio	D-ICAD$	D-IIRatio	D-IICAD$
Item xxx	0.5	9	0.2	4	0	0	0	0	0	0	0	0	0	0	0	0	……

I guess I either need to do clean up before import or normalize it during the import.... challenge..

Any good idea?

Answer 13

I would expect the Access table to look like this --

Item       IRatio    ICAD$    IIRatio   IICAD$

A             0.5         9             0.2         4            

B             0             0             0             0            

C             0             0             0             0

---

Build a little, test a little