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

    Excel Spreadsheets subheadings not importing

    In the attached file there is an Excel sheet. When I try to import this into Excel with the Access Import Wizard - well things happen. Most of them are good and some are not.

    It refuses to import what i would call on the Excel spreadsheet "subheadings". These are heading that are not at the top of the spreadsheet but below that main heading. I have included two snippet shots of the subheadings. Why is Excel not importing these along with everything else?

    The are still text files albeit with a different background color. They just are left out for some reason.



    Every cell has an apostrophe in the first space of each Excel cell. This make them text files. I must do that in preparation to import into MS Access. If I do not I get a bunch of errors.

    My question is why are they left out?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The spreadsheet shows data thru row 130 (including header in row 1). The code imports 135 records.

    I inserted a new column A and enter sequential numbers from row 2 to the end (1 - 129). Ran the import and that generated an import error table for row 127.

    The rows did not import in the order of this sequential ID so I sorted the imported table on this value. Six blank rows sorted to the top - these must be 'blank' rows from the end of the worksheet - there must actually be an empty string in at least one cell of each row. I deleted a bunch of rows after the end of data in worksheet. Reran the import and those 'blank' rows do not appear. Sorted records and everything is there. However, sometimes get the import error on row 127, field F92. Dates in rows 127-129 not converted to date string but instead importing as "43009", etc.

    Some of your 'subheadings' are split into multiple cells, why not just 1 cell? Why are each word of "Project Count" and "Site Count" in separate cells?

    Row 1 has two column headers called "Count". Should that be "Project Count" and "Site Count"?

    Oh, and those hidden columns in the worksheet had me confused for a second when I saw those fields in the table and wondered where they came from.

    A bunch of fields named like F1, F2, etc are imported. Might want to add headers to those with data and then delete other columns from the worksheet to eliminate the 'blanks'.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Thanks for your answer. I am as you recommended yesterday using the MS Access 2016 Import Wizard. It is best than using VBA.

    But you lost me on the steps to do this. I need them to be carefully enumerated. I am not as experienced with Access and Excel as you are.

    Please enumerate the steps. i am lost from reading your most recent post.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did some edits on previous post, probably after you read it.

    Exactly what do you not understand about how I modified the worksheet? How to insert a column? How to populate with sequential number? How to delete rows or columns?

    This time I used your code to do the import and it works just fine.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    Okay, here are my questions.

    How did you get an import error table for row 127 when you imported the modified Excel spreadsheet? It seems random.

    Also, did you put an apostrophe in front of each cell so the numeric cells would be considered text? After the import was over the apostrophes were removed?

    What do you mean six blank rows at the top?

    Also, what you mean that you deleted a bunch of rows at the end of the data in the worksheet? I am not sure exactly where.

    In the end sentence you say that you sorted records and everything was there; well what did you sort on and if everything is there then were they in the correct layout, order?

    These are my questions sorry as I said I am not as familiar with importing Excel worksheet as you are.

    Any help appreciated. Thanks in advance.

    respectfully,

    Lou Reed

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The error always referenced row 127.

    Did NOT put an apostrophe in front of each value in the new sequential ID column. They import as double type but that doesn't matter. I want numbers so they will sort properly.

    Sorting the table resulted in 6 records with absolutely no data at the top. After row 130 of the worksheet I selected about 10 rows and deleted them. The subsequent import test did not have any blank records.

    You might want to do something about those dates in row 127-129 of the worksheet. Now they are formatted as "Oct 2017" although the actual value is 10/1/2017. They do not have the apostrophe and import as "43009", etc.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    It refuses to import what i would call on the Excel spreadsheet "subheadings". These are heading that are not at the top of the spreadsheet but below that main heading. I have included two snippet shots of the subheadings. Why is Excel not importing these along with everything else?
    They are being imported - but they are not in the places you expect them to be. See my post #20 here: https://www.accessforums.net/showthr...t=69494&page=2
    for an explanation. If you want to be able to view the imported data in the proper order, follow the steps June has suggested above to put in an 'ID' column you can sort on.

  8. #8
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I will try what you said. I am sorry, I was just confused when I initially read your Post #2. I am a little confused in a previous thread you posted for me on Monday, when I said that putting apostrophes in front of every numerical cell had no effect on importation into Access so do not do it, you implied in an earlier thread that all you did was to put apostrophes in front of cells with numbers and then imported by import wizard.

    That thread is located here.

    https://www.accessforums.net/showthread.php?t=69494

    I just was confused by some of the things that you wrote and that is why I posted my reply. I am really appreciative of the response.

    It seems like adding a numerical column and filling it or numbering from 1 sequentially will make the system more ordered. That seems

    logical.

    Also, I want to check again you did not turn all Excel worksheet cells with numbers to text, by adding an apostrophe? I just want to make sure I understand what you did.

    Also, the actual importation of the modified Excel file was done using the VBA code in the form importfromexcel, instead of the import wizard.

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed
    a good idea. I just did not think of that. I will try what you said and see what happens.

  9. #9
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    You inserted a new column for column A and started counting the rows at row 2? Is that correct? Is that because the first row is for columns titles? If not what other reasons do you have for starting at row 2 and not row 1?

    Any help appreciated. Thanks in advance.

    Respectfully,

    Lou Reed

  10. #10
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    You say that dates in the original Excel sheet did not import. Mine imported with no problem. But then again it was my first try and you did specify that they occasional converted to 43009 when imported - not always, but occasionally. The word that you used were "sometimes".

    Now my biggest worry about importing is the hidden columns can I somehow make them visible before importing. In other words make them visible in MS Excel.

    Again thanks for all of you help it is really appreciated.
    Just to satisfy my curiosity why were the row imported randomly? That seems a terrible idea and Microsoft should change that.

    Respectfully,

    Lou Reed

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, that is how I created the sequential ID. And yes, because first row is field names. If you numbered from row 1 then the number 1 would become a fieldname and the record ID would start at 2.

    Dates did import (as text in format mm/dd/yyyy) except for the rows 127-129. I said only rows 127-129 have the issue.

    Why do you need to make columns visible? They imported even though hidden.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Lou_Reed is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2015
    Posts
    1,664
    I have got this now automated with VBA code. My question is I do not know how to add sequential numbers (starting at row 2 in column A) and proceeding for 129 numbers to 130.

    Also, I created a button to remove rows and it does,but it says some cryptic thing like Button20. I want to change that caption. How do I do it?

    Respectfully,


    Lou Reed

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You can either type number in every cell or enter 1 in the first cell then 'grab and drag' the bottom right corner of cell. It's a little tricky - hold the Ctrl key down to make sure it shows the extra tiny '+' symbol so it will automatically increment.

    Is it the same kind of button as the one for the apostrophes? Is it an ActiveX control? I right clicked on the button to select it then left click on button and started typing and the caption changed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Comparing 2 excel spreadsheets for changes
    By gbiondo in forum Access
    Replies: 1
    Last Post: 02-11-2014, 11:18 AM
  2. Replies: 1
    Last Post: 07-31-2013, 06:39 PM
  3. Replies: 0
    Last Post: 03-06-2012, 11:55 PM
  4. Replies: 1
    Last Post: 03-20-2011, 05:59 PM
  5. Replies: 1
    Last Post: 06-16-2010, 11:54 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
  •  
Other Forums: Microsoft Office Forums