Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am attempting to import data from an Excel spreadsheet into a new table in Access. However, one date field (mm-dd-yyyy) is not importing. I keep getting Type Conversion Error.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Make sure that the first data record (row with data) in the worksheet contains a valid date. If the first few records don't contain dates, the import routine can't correct the data type correctly.

    PS It would be nice if you provided some feedback to the answers that you receive to ypur questions. Otherwise neither the person who replied, nor other Loungers reading it know whether it was helpful.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The first data record (row with data) in the worksheet DID/DOES contain a valid date.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Would it be possible to attach a stripped down and zipped copy of the database to a reply?
    • Make a copy of the database and work with that.
    • Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    • In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    • Remove or modify data of a confidential nature.
    • Perform a compact and repair (Tools/Database Utilities).
    • Make a zip file containing the database.
    • If you have difficulties getting the zip file reasonably small, save the database in Access 97 format and then zip it.
    • Attach the zip file to a reply.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [attachment=85318:InvoicesDbase.zip]Each month I download a monthly statement from a vendorís website into an Excel spreadsheet containing a minimum of 400 lines of data that I will authorize for payment however over the next several months a different payment source may come about and I will then provide into to the vendor to bill the other payment source and to credit me back for payment previously made. So, I need to keep looking at the prior monthly statements until I consider each resolved which may take several months.
    Currently, I have 6 active spreadsheets w/ unresolved items. Iím getting a bit dizzy look at teach spreadsheet to research if any other payment source has come about. I think importing each monthís Excel spreadsheet into one comprehensive access database would work better for me.
    The column heading in red is what is downloaded from the vendorís site. The green column headings are added by me after download.
    Attached is the making of a sample database that I think will work for me. However, Iím unsure of how much work I have to do to the Excel spreadsheet before I can import additional information into the database.
    1. Appending each monthís subsequent client services into tblClientService
    2. How to extract the new unique ACCNs from the spreadsheet and append them to tblACCN
    3. Avoid type conversion failure errors involving the DOS and Inv Date fields. The problem seems to involve these dateís format/separator (- vs /). When I changed the Ė to / then clicked out of the cell the dates adjusted. Id did this for rows 1-19 which is why rows 1-19 do not show up on the import error table. However I canít do this each month for 400+ lines of information.
    Any suggestions appreciated.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Your database contains several queries and reports, none of which work. Without these, the database can be compacted to 412 KB - less than 1/10th of the original size!

    I have no idea what to do with the tables. It might help if you also posted a sample worksheet.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Attached is sample dbase and excel file.
    Attached Files Attached Files

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='bbrown5' post='791763' date='02-Sep-2009 15:31']Attached is sample dbase and excel file.[/quote]
    There are 2 date fields and both are either left justified or right justified.
    Maybe import those fields into a text field then convert them later.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sorry about the late reply, I was away on vacation. I don't know whether you still need it, but here goes.

    As patt indicated, the data in columns E and H in the worksheet are inconsistent (those in column J are OK). The first rows contain real date values, but from row 20 on, they are text values that look like dates. You can either correct this in Excel before importing into Access, or import into text fields and convert them to date fields later on.

Posting Permissions

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