Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Excel Sheets (2k)

    I'm populating my database by importing an excel spreadsheet with 1200 rows of data and 5 columns. i've noticed that it only adds 256 rows of data though. my final observation was that the only records that were added were rows that had data in every column... but that isn't required for them to be in the database. Why doesn't it add the records with blank cells?? and how can i get it to?

    Things tried: Setting required feild to 'No', turning off any excel filters, and clearing the table first before importing any data.

    thanks,
    -os
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Excel Sheets (2k)

    Access will stop importing when it encounters a row that contains ONLY blanks, because it thinks the table ends there. It shouldn't have problems with rows that contain SOME blanks. If it did, you would get an error message, and a table named ImportErrors would be created with information about which rows couldn't be imported.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel Sheets (2k)

    Well i went through the excel file again, and there are no rows that are only blanks, the first feild always contains a value, just not necessarily all the rest. i tried importing again, and i still hve the same problem... any other ideas?

    -os
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel Sheets (2k)

    the problem was fixed... my feilds were set to Allow Zero Length = no, so i changed it to yes and it seems to work... although there are 7 records that couldn't be imported, and not sure why, but hey 7 is better then 1000
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Excel Sheets (2k)

    Apparently, your spreadsheet contains cells that look blank, but aren't really empty. This frequently happens if the spreadsheet has been imported from another application; it can also be the result of formulas.

    The ImportErrors table should give you at least an indication where to look for the 7 rows that couldn't be imported.

  6. #6
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel Sheets (2k)

    ahhh this is giving me even more problems... my original table was 422 rows of data, and the form that goes through all of the records still displays 422 records present... but i just updated the table in the backend side of the file and updated the links to the .be tables. why would it show the old 422 records and not the 1252 i have??

    -os
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel Sheets (2k)

    i think its because i changed the properties of my back end tables, but the table links are still the same as the original properties. would that cause them to 'filter out' the info from the backend tables? and how would i fix that?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Excel Sheets (2k)

    That is strange. A linked table should display all records in the backend table. Are you sure the frontend link points to the correct backend table?

  9. #9
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel Sheets (2k)

    how can i check that? i mean the links were created automatically when i split the database. but before i split the database there were input masks and Allow Zero Length settings in place that i later deleted from the .be file to fix the problems i was having with importing data. can you un-split (merge) the 2 files? and is there a link viewer at all like the relationships viewer that i could use?

    Really confused now
    -os
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Excel Sheets (2k)

    Tools | Database Utilities | Linked Table Manager lets you view and update linked tables. (Tick the check boxes for links you want to refresh, then click OK.)

    You can also manually delete the links in the frontend. This will not affect the backend, only remove the links. You can re-link by selecting File | Get External Data | Link Tables...

  11. #11
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel Sheets (2k)

    im pretty sure i would be able to solve my problem by doing that, but how do you manually delete the links to tables from the front end?

    -os
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  12. #12
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Excel Sheets (2k)

    Hans i love you. i got it, thanks so much!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Excel Sheets (2k)

    Just select a table in the database window in the frontend, and press Delete, or click the Delete button in the toolbar of the database window. You'll have to confirm the deletion (see screenshot).
    Attached Images Attached Images
    • File Type: png x.png (6.8 KB, 0 views)

Posting Permissions

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