Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Import Spreadsheet problem

    I have a spreadsheet that I am trying to import into an Access 2000 table. One of the columns in the spreadsheet is called 'House Number'. Here are some examples of the contents:

    22A
    1B
    33
    13A/4

    Now, no matter how I approach the import process, Access always creates a Type Conversion Failure with the '13A/4'. I have tried importing into an existing table where the 'House Number' field is a Memo type, but no joy. I have tried creating the table at the point of importing, but still no luck. Anyone know a way round this?

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Have you tried saving the Excel sheet as a text file, then importing that?
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Yes, I tried saving as a comma delimited csv file, but got the same error.

  4. #4
    Join Date
    Oct 2003
    Location
    New York
    Posts
    23
    try saving as a csv file with " " text qualifiers..

  5. #5
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    How do I do that in Excel?

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If there are only a couple of instances, you can put an apostrophe (') before the 13A/4. In the cell it will look like '13A/4, but when you are done editting the cell it will only show 13A/4.

    The ' forces Excel to ignore the contents not try to interpret them. When you import it into Access, Access will ignore them. I got a spreadsheet of data and the data in each field was preceded by the ' and it worked perfectly.

  7. #7
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    If I go through the import process manually (File > Import) I can get all the data in OK (by placing a ' in front of the HouseNumbers in the Excel sheet first then specifying that the ' char is a text qualifier). However, I need to automate this process using a macro or code. Any ideas how I can do this? There doesn't seen to be a qualifier option when making a TransferText macro.

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You shouldn't have to tell Access that it is a text qualifier. As far as automating goes. During the Import process (manual) there is an Advanced button that will allow you to setup the delimiters and qualifiers and then save it. It's call a File Specification, it tells Access how to interpret the file. Setup the specification and then save it. For the TransferText you can tell it which specification to use.

Posting Permissions

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