Results 1 to 3 of 3
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Data Type when importing from Excel (Access 2003)

    I need to do an import from Excel on a regular basis. At the moment I do it with Get External Data...Import, but I need to code it so that users can do it from a form. I put the data into a new temp table, because I have to create records in a few different tables.

    One column in Excel (Teaching Period) contains a mainly numerical data, but also has some alpha data, usually not present in the first 25 rows. When importing this field is created as a double, then all the rows containing an alpha Teaching Period are rejected. I can get around this by sorting the Excel spreadsheet to put alpha data at the top, but when I automate the import I don't want to have to rely on this.

    So my question is: is there any way to force this column to be treated as text in the import?
    Regards
    John



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

    Re: Data Type when importing from Excel (Access 2003)

    There is no good way to do this without modifying the Excel data. If you create a table with Teaching Period as a text field, and import into this table instead of into a new one, you'd expect the text values to be accepted, but the import process will still discard the text values. If you link the Excel table, the text values will show as #Number.
    You could use Automation to either sort the Excel worksheet, or insert a dummy row with data of the correct type (and later remove the dummy row from the imported table).

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Data Type when importing from Excel (Access 2003)

    Thanks Hans.

    I thought I would have to something like that.

    I could also use automation to sort the teaching period, or replace the text values (they will be A or S) with a number.
    Regards
    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
  •