Results 1 to 15 of 15
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    import date

    All, using access 2003; I am trying to import a xls spreadsheet into a temp table with a date format in the spreadsheet as text 20111212 to a date field in access table 12-12-2011. I get type mismatch. What can I do please.
    Thanks

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Can you do a Format Cells in Excel and make that 20111212 column a Date column?

    I ran into something like this a few years ago.
    What I decided to do [since my project required numerous automated imports every morning from Excel to Access] was just do an initial import from Excel into a new Table - and let Access decide what kind of field to create for each Excel column.
    After that - I created Macros that use delete queries to empty the tables each morning and import fresh data from Excel.

    Then - in Access - I do my type-conversions [or just use the Format() function] in queries or VBA to take what came in to the table and make it what the end-user needs.

    Hope this helps.

  3. #3
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    UM. The auto import is done by the user. Maybe I can try the to setup a specification and do the formating there. What do you think? I have to find how to do it. It's been a while since I have done the specification import. Thanks

  4. #4
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Having a problem with the import specification. Does not allow me to change data type. What I'm I doing wrong please. Seems like I did this import specification from excel a while back. Is this possible.

  5. #5
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I have had mixed fortunes with using the Import Specifications.
    They 'Work' . . . but are 'temperamental' and 'picky'.

    That's why I try not to depend on them.

    And that's why I was leaning away from messing with the Import process.

    It's a LOT easier to work with the data once it is in an Access table - regardless of what format it is in.

    Would working with the data in Access - not be a viable option for you?

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I'll try and duplicate your scenario if I get a chance today - but I'm working with Access 2010 - so there might be slight differences there too.

  7. #7
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    The user would be doing the import. Heres a scenrio: The user clicks a import button on a form: I have a spreadsheet going into a temp table before appending to perm table. I just need to clean up the data in the temp table before appending it to the perm table such as converting date from 20111212 to 12-12-2011 and zip from 237071495 to 23707 or 7398 to 07398 also to make caps where not like small to SMALL. How can I do this? Thanks

  8. #8
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Do you already have your append query?

  9. #9
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Yes; I do.
    Code:
    INSERT INTO tblSP_SHIPMENT_temp_TEST ( INVOICENUM, TRACKINGNUM, SHIPDATE, TRANSCHG, SALESORDNUM, SENDERNAME, SENDERCITY, SENDERSTATE, SENDERZIP, RECIPIENTNAME, RECIPIENTCITY, RECIPIENTSTATE, RECIPIENTZIP, WEIGHT, SERVICETYPE )
    SELECT SMALLPKG.[Invoice Number], SMALLPKG.[Express or Ground Tracking ID], SMALLPKG.[Shipment Date], SMALLPKG.[Net Charge Amount], SMALLPKG.[Original Customer Reference], SMALLPKG.[Shipper Company], SMALLPKG.[Shipper City], SMALLPKG.[Shipper State], SMALLPKG.[Shipper Zip Code], SMALLPKG.[Recipient Company], SMALLPKG.[Recipient City], SMALLPKG.[Recipient State], SMALLPKG.[Recipient Zip Code], SMALLPKG.[Actual Weight Amount], SMALLPKG.[Service Type]
    FROM SMALLPKG;
    The field in SMALLPKG.[Shipment Date] is formatted as text 20111209. The field in INSERT INTO tblSP_SHIPMENT_temp_TEST SHIPDATE is short date format.
    Thanks

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,641
    Does this work?

    INSERT...
    SELECT... DateSerial(Left([Shipment Date], 4), Mid([Shipment Date], 5, 2), Mid([Shipment Date], 7, 2))...

    Which assumes the text field is yyyymmdd.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    where do I put this? When I put it in the format field of the shipdate properties; it gets all weird and i can't change it
    Code:
    d"ate"s"erial(Left(["sh"ip"n\en"t "d"ate], 4), "m\id"(["sh"ip"n\en"t "d"ate], 5, 2), "m\id"(["sh"ip"n\en"t "d"ate], 7, 2))..."

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,641
    In the SELECT statement, like I showed.

    INSERT INTO tblSP_SHIPMENT_temp_TEST ( INVOICENUM, TRACKINGNUM, SHIPDATE, TRANSCHG, SALESORDNUM, SENDERNAME, SENDERCITY, SENDERSTATE, SENDERZIP, RECIPIENTNAME, RECIPIENTCITY, RECIPIENTSTATE, RECIPIENTZIP, WEIGHT, SERVICETYPE )
    SELECT SMALLPKG.[Invoice Number], SMALLPKG.[Express or Ground Tracking ID], DateSerial(Left([Shipment Date], 4), Mid([Shipment Date], 5, 2), Mid([Shipment Date], 7, 2)), SMALLPKG.[Net Charge Amount], SMALLPKG.[Original Customer Reference], SMALLPKG.[Shipper Company], SMALLPKG.[Shipper City], SMALLPKG.[Shipper State], SMALLPKG.[Shipper Zip Code], SMALLPKG.[Recipient Company], SMALLPKG.[Recipient City], SMALLPKG.[Recipient State], SMALLPKG.[Recipient Zip Code], SMALLPKG.[Actual Weight Amount], SMALLPKG.[Service Type]
    FROM SMALLPKG;
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I tried this. I get an error: Number of query values and destination fields are not the same.
    Thanks

  14. #14
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I had a field in the query twice. It worked! Thanks so much

  15. #15
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,641
    No problem!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 12
    Last Post: 04-26-2012, 04:01 AM
  2. Replies: 1
    Last Post: 12-12-2011, 06:32 AM
  3. Picking up Date on Import
    By DonL in forum Import/Export Data
    Replies: 4
    Last Post: 08-10-2011, 07:06 AM
  4. Import Excel and date formats
    By thart21 in forum Import/Export Data
    Replies: 1
    Last Post: 04-19-2011, 03:49 PM
  5. Replies: 1
    Last Post: 07-07-2010, 04:22 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