#  Other Applications & Softwares  > Access Tables & Databases >  >  Need to import Excel files with 2 rows of column headings into Microsoft Access

## rob_ford16

I'm just getting into Microsoft Access and I need help trying to make sense of data, so sorry for any dumb questions. My problem is this: I have an excel spreadsheet with 2 rows of column headings: one row is the year and the other row lists esimated values and error % (RSE) (see the attached example). My ultimate goal is to import this information into ArcGIS and map but I need to put it into Access and convert to dbf table first. I have about 30 other Excel files with the exact same data but for different areas and they need to be combined. It is confusing to look. I feel like I should reverse Column A values with the column headings, and make column A values the headings. Any help is very much appreciated!

----------


## ConneXionLost

It will depend on what you want to do with the data once it is in Access, but you normally wouldn't make individual columns/fields for each year.  A suggested table would have the following field headings:

RSE_ID
Region
Units
Year
Estimate
RSE

Hope this helps.

----------


## alansidman

I concur with ConneXionLost.  To help you understand this, look at this presentation of relational databases.

http://www.deeptraining.com/litwin/d...aseDesign.aspx

----------


## rob_ford16

So I was correct in assuming I should take the years out of the headings? How would this work? I have multiple years each with their own "estimate" value for different "attributes". If I were to use your example ConneXionLost, I would have to make 11 rows for, say, year 2010 to accommodate all "attributes", "estimates", and "RSE". This would also require extensive manual change to many many spreadsheets which would take time. I am simply wondering if there is anything Access can do to import the data logically, or am I better off just keeping everything in Excel format. I've attached an example spreadsheet with two different formats that I have been playing with, one of them being ConneXion's example. If I have 30 spreadsheets to combine, and each one has 11 rows for each year, I would have almost 2,000 rows of data to enter manually.

----------


## alansidman

Look at this example of normalizing data.  You might be able to adapt something similar.

http://www.datawright.com.au/access_..._using_VBA.htm

----------


## rob_ford16

Thanks again alansidman for the help. Does the attached files' format look like it would work? Thanks again for everyone's help!

----------


## alansidman

Yes, it appears to be normalized.  I would suggest you change the field name "year" to something else.  That term is a reserved word in Access and may cause you some issues later.  Perhaps fYear or EventYear.  Something that is descriptive and makes sense to you.

FYI--Here is a listing of reserved words and characters.

http://office.microsoft.com/en-us/ac...010030643.aspx

----------


## rob_ford16

Thanks again! Also for you people who need to transpose multiple rows into a single column as I did, read this post: 

http://chandoo.org/forums/topic/tran...nto-one-column

This helped me immensely and saved tons of time. Thanks again to all!

----------

