Results 1 to 5 of 5

Problems on Importing an Excel spreadsheet into an Access Table

  1. #1
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664

    Problems on Importing an Excel spreadsheet into an Access Table

    In the two attached files are an Excel spreadsheet and an MS access db with the Excel spreadsheet imported as a table.



    The table is named:

    Production Tracker-xlsm5.

    Now I am not sure what happened during the import of the spreadsheet to the MS Access table, but I see several column names (filed names) with months on
    them.

    They are things like September, October, November , etc.


    There is no equivalent value or column name on the spreadsheet.


    What exactly happened here?


    Any help appreciated. Thanks in advance.


    Respectfully,


    Lou Reed
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,754
    the excel sheet is not really layed out to do imports correctly.

  3. #3
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I am aware of that, but again my sponsor will almost certainly ask the question about the columns with months' names in the row 1 location. It, the software, did not complain about any of this (i.e. no error table) so I assume it must have had a reason for doing this - adding the months September through July - with August omitted.

    Why were they put in, in the first place?

    To take that further there is some sparse data in them with and where did that come from?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,486
    Quote Originally Posted by Lou_Reed View Post
    <snip> so I assume it must have had a reason for doing this - adding the months September through July - with August omitted.
    Access didn't/can't randomly add fields. The spreadsheet has to have them..... keep reading


    Quote Originally Posted by Lou_Reed View Post
    Why were they put in, in the first place?
    Because you (or whoever designed the spreadsheet) put the columns there.


    OK, open the table Access table "Production Tracker_xlsm5" in design view. Ever wonder why there is a field named "F1"? Open the Excel spreadsheet. Look at "A1". Notice it is blank/empty/NULL - what ever you want to call it. Now there can not be a field with no name, so Access gives it a name - "F1". How did Access come up with a name like "F1"? Well, in this case the "F" refers to "Field". When Access imports a spreadsheet, the values in the first row become the table field names. So the first empty field name is "F1" (Field1) because spreadsheet cell "A1" is empty. The next cell in row 1 that does not have an entry will be named "F2" in Access, the next .... well you get the idea.


    This is where it is important to be able to sing the "ABC" song. Looking at the spreadsheet, here we go... begin! A, B, C,....., K, ... oops! When you get to "K", the next should be "L". But where is it???
    Yes, this is a test. Did you figure it out?
    Yep! Good guess - column "L" is hidden. Select the entire columns "K" and "M", right click and select "Unhide" at the bottom of the dialog box.


    Keep singing until you get to "Z", then start over, but put an "A" in front. When you get to "AZ", start over again with "BA". Uh-oh..... there is no "BB", "BC", "BD".....
    Where are they? Ah-ha!! They are also hidden. Select the entire columns of "BA" and "CN", right click and select "Unhide".
    TA-DA!!! Now you can see the month names and know why they showed up in the Access table.


    But wait!! What's up with the access fields names "F65" to "F161???
    That is how wide the spreadsheet is. To find the last cell used (at some point in time), click in cell "A1". Press the "F5" key, then click on the "Advanced" button at the bottom left of the dialog box. Click on "Last cell", then click OK.

    The used area of this spreadsheet is from "A1" to "FE130".

    If you select the columns from "CO" to "FF" ("CN" has data at row 128) then right click and select "DELETE", those columns will be gone. Save and close the workbook, then open it again.
    Click in cell "A1". Press the "F5" key, then click on the "Advanced" button at the bottom left of the dialog box. Click on "Last cell", then click OK.
    The last column should be "CO".



    Easy-Peasy, ..... don't you think???
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    Join Date
    Apr 2017
    Posts
    1,002
    In Excel, you have a lot of columns hidden at end of the table. There are columns September ... July, and some without headers, but having formatting in some cells (having cells formatted is enough for Access to read column as part of table). And there is a 3-cell entry too even further to right and at bottom of datarange

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing via VBA from Excel Spreadsheet into MS Access 2013
    By cdixon102419607 in forum Import/Export Data
    Replies: 2
    Last Post: 08-17-2017, 09:28 PM
  2. Replies: 1
    Last Post: 04-10-2017, 10:06 AM
  3. Importing Excel spreadsheet and appending it to an existing table
    By Access_Novice in forum Database Design
    Replies: 3
    Last Post: 08-21-2014, 07:01 AM
  4. Replies: 1
    Last Post: 05-28-2014, 10:59 PM
  5. Importing Excel 2007 spreadsheet into Access 2002
    By jhjr in forum Import/Export Data
    Replies: 1
    Last Post: 06-17-2010, 02:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums