Results 1 to 15 of 15
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    linking an Excel file (Access 2002)

    I have had an Excel file linked for some months with no problems. Suddenly, one of the Excel fields with a "General" format has had numbers as data as well as A/N data . The linked file shows #NUM as an error where the numbers should be.
    Formatting the Excel field as "Text" and relinking the file makes no difference.
    If the Excel column is sorted so the numbers come first and relinked, the numbers appear correctly but the A/N data appears as #NUM??????
    It seems to me that the Excel import wizard is looking at the initial rows of data and formatting the field according to what it sees, not on all the possibilities in the column!!
    Is there any way to include a "specification" like you do when importing a csv file.

    Many thanks for anticipated clarity on this.

    John

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: linking an Excel file (Access 2002)

    Do you actually have a linked table that reads from an Excel workbook, or are you importing the data from excel into an Access table?

    If it's the latter, the import wizard does base it's field types on the first few rows of data. To avoid that problem, import the data into an existing table - empty if you wish - where the fields are defined as text.
    Wendell

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking an Excel file (Access 2002)

    Thanks Wendell
    It is a linked table, but during the linking the wizard appears to ask if the first row is field names or not.
    JOhn

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

    Re: linking an Excel file (Access 2002)

    You are correct in your assumption that Access only looks at the first rows of the Excel table to determine field types. So if the first few entries in a column are numbers, Access will create a number field, and subsequent alphanumeric entries will not be imported/linked correctly.

    If you want to link, you might insert an easily identifiable dummy data row in the Excel worksheet in which each field has a value of the desired type. This will force Access to use the correct field type. You'll have to eliminate the dummy record from query results.

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking an Excel file (Access 2002)

    Thanks for your responses. They did not work! I don't want to be cruel in this blatant statement so let me explain.
    The data is coming from an export routine from another system. This creates a csv file. We created an Excel file from this and the link to Access worked.
    Then the data changed from A/N to A/N and Numeric. Changing the column format in Excel and relinking did not fix the problem. Making the first row A/N and the second row Numeric and relinking did not fix the problem. It was only when we changed the data type to Text in the wizard as we created the Excel file from the csv file , and then relinking, that the problem was solved.
    I obviously don't understand how the wizards work well enough to explain why this worked but maybe it will jerk an experts braincells out there somewhere to help us all understand.
    I have sweated over this problem off and on for over two years now and never really worked out a definitive means of solving it, but this also solved another situation I had put on the back burner some time ago.
    Regards
    John

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

    Re: linking an Excel file (Access 2002)

    I'm glad you got it solved.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: linking an Excel file (Access 2002)

    Is there a reason for taking the file to Excel first? Access can import CSV files directly, but perhaps you are doing some editing and sanity checking in Excel first?
    Wendell

  8. #8
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking an Excel file (Access 2002)

    The reason for going via Excel is that we have the same importing problem with respect to Data Types if we use the Access linking Wizard direct from csv. The csv to xls wizard seems to set up the data types correctly whereas the csv or xls wizard to Access does not.

    John

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: linking an Excel file (Access 2002)

    Ah, but if you set up the table with the right data types to begin with, then your data will come in directly from a CSV as long as it has been properly formatted. If it doesn't come in cleanly, you get an error table created, and you can use that to determine which records are incorrectly formatted (for example, a quote, a comma, and something other than a quote). You can use a delete query to delete all the records in the table just before you import, or you can simply copy a template TableDef entry after deleting the import table. In my experience, where we were importing CSV files from some 50 or 60 external organizations one or more times a week, that was the cleanest approach.
    Wendell

  10. #10
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking an Excel file (Access 2002)

    Wendell,
    The data in the csv file may not be correctly formatted and I have no control over that. Hence my ability to format it with the csv to xls wizard.
    For clarity, in my own words, are you suggesting that instead of linking the csv file (File, Get external data, Link tables) that I use the Transfer Text function and import into a previously defined table? (Eliminating the linking wizard ?)
    This raises another question for me. Where are these specifications stored which are created when you do the import for the first time. The only way I have found so far is to do a File - Import first and create and save a specification. To modify the spec I have to repeat that process! Is there another way?

    Thanks for you help so far.
    John

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: linking an Excel file (Access 2002)

    Yes, that's what I'm suggesting. In the transfer situation where you are going to an existing table, there is no specification needed since the file is .CSV. Actually that's not quite true, as you may or may not have " marks surrounding fields depending on the source and how closely the app that produced the file complies with the specification. But the existing table determines how fields should be formatted, so you eliminate that error possibility. Of course if the file is not formatted correctly, you will get errors when you try to import. Then you can take it into Excel and try to fix it.
    Wendell

  12. #12
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking an Excel file (Access 2002)

    Thanks Wendell, I'll give it a try.
    However, this raises another question for me. Where are these specifications stored which are created when you do the import for the first time. The only way I have found so far is to do a File - Import first and create and save a specification. To modify the spec I have to repeat that process! Is there another way?
    John

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

    Re: linking an Excel file (Access 2002)

    I don't think that import/export specifications are exposed in the Access object model. They are stored in two system tables. To see them, check System Objects in the View tab of Tools | Options...

    One table is MSysIMEXSpecs. This table stores general information about import/export specifications. The other one is MSysIMEXColumns. It is linked to MSysIMEXSpecs by SpecID, and it contains the settings for the individual columns (fields) - one record for each column for each specification.

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: linking an Excel file (Access 2002)

    It sounds as if you don't know the format of the file until you get it - is that true? If it is then you must do an import or look at it in Excel, or determine the format in some other way.

    I was assuming that you had a format which is fixed and doesn't change from one file to the next. In that case, you can create an empty table in Access using the table designer, specify the fields the way they should be, and then import into that table. In that situation, no import specification is required.
    Wendell

  15. #15
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking an Excel file (Access 2002)

    Thank you, Hans and Wendell
    I believe you have taught me enough that I can solve these problems myself now. Also with the knowledge of MySYS .... tables makes things much more transparent.
    Thanks again.

    John

Posting Permissions

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