Import Data from Excel

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

The first data record (row with data) in the worksheet DID/DOES contain a valid date.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Attached is sample dbase and excel file.

Reply | Quote

Attached is sample dbase and excel file.

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.

Reply | Quote

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.

Reply | Quote