+ Reply to Thread
Results 1 to 8 of 8

Need to import Excel files with 2 rows of column headings into Microsoft Access

  1. #1
    Registered User
    Join Date
    06-04-2012
    Location
    Ohio
    MS-Off Ver
    MS Office 2003, 2007, 2010
    Posts
    14

    Need to import Excel files with 2 rows of column headings into Microsoft Access

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Need to import Excel files with 2 rows of column headings into Microsoft Access

    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.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Need to import Excel files with 2 rows of column headings into Microsoft Access

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

    http://www.deeptraining.com/litwin/d...aseDesign.aspx
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    06-04-2012
    Location
    Ohio
    MS-Off Ver
    MS Office 2003, 2007, 2010
    Posts
    14

    Re: Need to import Excel files with 2 rows of column headings into Microsoft Access

    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.
    Attached Files Attached Files

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Need to import Excel files with 2 rows of column headings into Microsoft Access

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

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

  6. #6
    Registered User
    Join Date
    06-04-2012
    Location
    Ohio
    MS-Off Ver
    MS Office 2003, 2007, 2010
    Posts
    14

    Re: Need to import Excel files with 2 rows of column headings into Microsoft Access

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

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Need to import Excel files with 2 rows of column headings into Microsoft Access

    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

  8. #8
    Registered User
    Join Date
    06-04-2012
    Location
    Ohio
    MS-Off Ver
    MS Office 2003, 2007, 2010
    Posts
    14

    Re: Need to import Excel files with 2 rows of column headings into Microsoft Access

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1