Results 1 to 8 of 8
  1. #1
    Hav0cUK is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    4

    importing an excel spreadsheet using code and fixing field types

    Hi,




    I have successfully create a front end with a button to import an excel spreadsheet, the data contains a lot of fields (a mixture of short text and numeric values).

    I am currently getting import errors that are simply because access is assuming the data type "double" is valid for some fields when in fact short text is required. how do i get access to switch each field data type to short text?

    any help would be greatly appreciated.

    Many thanks.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you using TransferSpreadsheet? Have you created an Import Specification?
    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
    Hav0cUK is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    4
    Hi June7,

    Firstly, Thanks for the reply!

    The code i am using is below, i have not created an Import Specification

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, filename, True

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Never mind. Import Specification is available with TransferText.

    It can be difficult to control data types with TransferSpreadsheet. The utility determines datatype based on values in first few rows of spreadsheet.

    Try formatting columns in spreadsheet.

    Try setting a link to spreadsheet then use that link as source for an INSERT action to final table. Can convert data types in SQL statement.
    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
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    If you need to manipulate the data before adding it to your final Access table you can import it first into a temporary table in which you set all fields (or the ones that are giving you grief) to short text then you create a select query based on that in which you use type conversion functions (cInt,CDate, etc.) to get your desired data types.
    https://support.microsoft.com/en-us/...3-87ac8d1a2202

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Hav0cUK View Post
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, filename, True
    I think you might want to use
    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, tableName, filename, True
    See https://ss64.com/access/acspreadsheettype.html
    "acSpreadsheetTypeExcel12" is for Excel 2007 format

  7. #7
    Hav0cUK is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    4
    I have got around the data field type issue by simply formatting all columns to text in excel before importing.

    I have written the code to export the table using the file location used for the import source.

    This created an additional tab inside the original worksheet with a '1' appended to the name of the tab.

    I have 2 issues resulting from my export.

    1. the header row although at the top of the spreadsheet is not being treated as a header and if i manage to sort a column it also moves the header.

    2. if i format the spreadsheet as a table and tick the 'my table has headers' option to essentially fixed the top row as a header the option to sort fields is grey and unavailable.

    is there an alternative way of exporting the data so that i dont have these issues? i would like to end up with a normal xls or xlsx spreadsheet.

    Perhaps i should add that all i am doing is importing a spreadsheet into access, updating the imported table with a stock location from a lookup table using queries.

    Any help would be appreciated

  8. #8
    Hav0cUK is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    4

    I have solved my problem!

    Quote Originally Posted by Hav0cUK View Post
    I have got around the data field type issue by simply formatting all columns to text in excel before importing.

    I have written the code to export the table using the file location used for the import source.

    This created an additional tab inside the original worksheet with a '1' appended to the name of the tab.

    I have 2 issues resulting from my export.

    1. the header row although at the top of the spreadsheet is not being treated as a header and if i manage to sort a column it also moves the header.

    2. if i format the spreadsheet as a table and tick the 'my table has headers' option to essentially fixed the top row as a header the option to sort fields is grey and unavailable.

    is there an alternative way of exporting the data so that i dont have these issues? i would like to end up with a normal xls or xlsx spreadsheet.

    Perhaps i should add that all i am doing is importing a spreadsheet into access, updating the imported table with a stock location from a lookup table using queries.

    Any help would be appreciated

    i used the exportwithformatting macro that is built in to access and simply put it behind a button and it worked perfectly

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

Similar Threads

  1. Replies: 2
    Last Post: 04-20-2019, 11:38 AM
  2. Importing Excel Spreadsheet Programmatically
    By f15e in forum Programming
    Replies: 3
    Last Post: 02-06-2017, 04:08 AM
  3. Importing Excel spreadsheet
    By knarlyd in forum Access
    Replies: 2
    Last Post: 12-10-2014, 04:12 PM
  4. Importing Excel Spreadsheet
    By JayX in forum Access
    Replies: 2
    Last Post: 12-13-2011, 08:27 AM
  5. importing excel spreadsheet issues
    By majortool in forum Import/Export Data
    Replies: 1
    Last Post: 01-24-2008, 06:29 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