Type Conversion Errors Importing From Excel Workbook

[deekadelic]

I've read all the previous posts on this problem; but none of them seem to specifically address the problem I'm struggling with.

I'm trying to import an Excel Workbook to an Access database, which I've done thousands of times with no difficulty whatsoever (though never in Access 2016 until now); but no matter what I do I'm getting type conversion errors on certain fields.

I understand that Access tries to "guess" the field data type by looking at the first row of data in the spreadsheet, and that it can be wrong at times. I'm pre-defining the fields in question as "short text", rather than "number", because though both fields have a numeric-looking value in their first row, they both also have rows where the value needs a "short text" data type. I would figure that by pre-defining the field data types as "short text", that Access would adhere to this directive; but instead it just overrides my pre-programmed data type and replaces it with a "number" data type, and so the import hits conversion errors for those rows. This also begs the question "Why does Access bother having me instruct it on field data types if it's just going to override my decision anyway?"



The weird thing is, the rows for which the import is getting conversion errors actually contain strictly numeric values. Not one of the errors involves a text value trying to fit into a numeric field. So that makes no sense whatsoever.

Additionally, there are more than 20 other fields in the Excel spreadsheet that have the exact same setup (numeric value in the first row, but some textual values further down), and these fields are not getting conversion errors. For these rows, Access just accepts the "short text" data type I have pre-defined before running the import, and thus both the numeric and text values in those fields live together in perfect harmony. This makes the problem seem totally arbitrary.

If anyone can speak to this, please do. I'm on my second day of a new job; and the last time I used Access this was not an issue. I've spent the entire day battling what a few years ago would have been a two-minute task.

Thank you

- Deek

[ranman256]

ive had this problem many times. Access guesses at what the data type is and is not always correct.
So what I did was , before the import , i run a macro (usu in excel, but programming can do it from access)
This excel macro goes down the column converting the field to a hard string , by putting a single quote in the front of the text.
Then it always imports as string.

Sub Cvt2Text()
range("A1").select
While ActiveCell.Formula <> ""
   ActiveCell.Formula = "'" & ActiveCell.Formula
   ActiveCell.Offset(1, 0).Select
Wend
End Sub

[Micron]

I understand that Access tries to "guess" the field data type by looking at the first row of data in the spreadsheet...
(numeric value in the first row, but some textual values further down)

According to one of the Access gods (small g) it looks at 8 rows, as I recall, so that may be one thing that's messing you up. As for any Excel column that contains only numbers for which you end up inserting as text (if I understood your post) I have no idea.

[deekadelic]

The macro thing can work on a one-time basis; but the problem with that is that the Excel file is overwritten every day; and so as far as I can tell I would have to go and re-add the macro every time the file was overwritten. But of course that would ruin the whole point of trying to automate this process, which is the only reason why I'm even doing any of this.

I think it's also worth asking, though, if anyone can speculate: what possible reason could Microsoft have to set the import function up this way? I mean, it literally asks you what data type you want to import as, and then promptly disregards your choice and overwrites it with the data type it thinks is appropriate. So why bother asking in the first place? I'd like to think this serves some kind of business purpose, and isn't just a bug, after working perfectly for 15 years.

[Micron]

Presumably, if you're given the option to specify the data type and the source is compatible, then it should work. What would you have them do for when the source columns have mixed types (as you seem to have), or the viewed data is the result of a formula and not an actual value?
If you cannot/don't want to repeatedly link to a workbook sheet from Access, your only option may be automation. At least you can connect with a sheet and convert types before appending or updating tables. Not the easiest route to take, for sure.

[deekadelic]

That's what's weird: the data in question is absolutely compatible with the "Long Text" data type I'm trying to set up, although the first many records just so happen to have numeric values, which seems to be the reason for the switch to "Number". But numeric values have no reason to be incompatible with a "Long Text" data type- that is unless I know way less than I think I do. Therefore this remains a mystery and a frustration (not in that order).

However, I think I might be having a measure of workaround success with my latest idea: exporting the Excel Workbook as a semicolon-delimited text file, then importing that into Access. Yes, it's an annoying extra step, but it's better than getting nowhere.

[Micron]

AFAIK, long text is the new (improved???) version of the former Memo field type, which allowed for many more characters than the text type could hold (255). Regardless, text cannot store numeric data types, be they long or short, or text or memo. If Access can, it should import numeric characters as numbers so long as the destination field data type is text. If you're trying to put numeric data in a field with Long Text type, I'd say that's your problem. Unfortunately, I cannot test these new types - stuck in version 2007. Can't justify $100 to upgrade to 2016 since I've retired. Mmmm, maybe I could "gofundme"?

[deekadelic]

There must be some data type that will accommodate both text and numeric data. Right? If not, then what's the world coming to?

[Micron]

Had to review some of your posts to get back on track. Yes, text type should allow numbers and text. I should have said you'd experience issues trying to use numeric data as numbers if it was converted to text, not that your issue was importing numbers as text. I agree the conversion should not override the designed destination data type, and to safeguard against the less able users by automatically making that decision seems like a poor choice. One experiment you could try is to use dummy data in the first 10 rows which is of the data type you need, just to see if the import still fails (i.e. test the 8 rows I read about before). If it works, maybe your users could have a spreadsheet template where the first 10 rows of dummy data are locked, and you don't import that part. A delimited file is also another way, as is using a make table query against your spreadsheet (one time) to see which fields might be an issue, assuming the data sample is representative of future data. The process would then be to import, then run code to Update the values using conversion functions against them. No matter what the solution, it seems real stupid that you have to employ any means other than an import.

[deekadelic]

The operation works fine when I start with 8 rows of dummy data that conforms to the data types; which is annoying, because it confirms that this ridiculous situation is in fact what it appears to be. Unfortunately, I have no ability to change anything about how the Excel files are generated, as I do not have access to the back end of the web interface that pushes these reports to Excel in the first place. My ability to affect this situation begins in Excel. Pretty limiting, I know.

Incidentally, saving the Excel file as a delimited file does not work- I tried it several times already. The problem there is that Access wants to append the column headers as data records; and the column headers in many cases do not adhere to the field data types (duh); so the append fails for that reason when I try to load text files into Access.

As for importing then running code to Update the values with conversion functions, the problem there is that I cannot get all the data into a table in any format, even using the most permissive of data types.

[Micron]

What method are you using to import? Is the first row of data the headers? The TransferText method allows for that.
Maybe a csv sample and/or zipped copy of the db if you need help...
Or from Access you can insert 8 rows of static data via automation (or manually as you suggest). The csv route is the one I'd try to get working.

[deekadelic]

I am absolutely blown away that at this point in the history of MS Office I cannot simply establish table data types and expect incoming values from Excel, CSV, or wherever to adhere to them. I simply cannot believe this is so difficult to do, especially considering how easy it's been for the past 15 or 20 years.

Okay, so I am attaching here two Excel files, each with 50 records of sample data, and both with identical structures. Between the two Excel files, all data type/formatting issues of concern are represented; so to get these two files to load into an Access 2016 table with their data types and formats intact would constitute success. I tried to zip and attach a blank Access 2016 DB here as well, but the upload mechanism wouldn't take the attachment; so I'm not sure how to handle that. Assuming most of y'all probably have Access 2016, maybe if anyone has any luck with this, we can do a Google Drive exchange or something.

NOTE:

All of the fields must be able to accept duplicates, and must conform to the SHORT TEXT data type, except:

[Shipment Created Date] - must show a date formatted as DD/MM/YYYY

[Original Weight] & [Final Weight] - must be able to conform to data type DOUBLE, to 3 decimal digits,

and [Total Invoiced] - must function as a numeric currency field (I'm figuring DOUBLE, to 2 decimal places.



What I need to be able to do is this:
A) Import one of the files into the Access DB as a new table, with data formatting intact, and
B) Then, import/append the second file into the same table, without messing up any data or changing any field data types (the idea being that from there I could simply continue to append new files into that table as needed).



If this can be done without having to go through the step of converting the Excel files to CSV, all the better- less work for me come automation time; but more important than saving me labor is getting the results I need.





My VBA skills are a little dated at this point; but I plan to dust them off and bring them back up to speed, if necessary, if I can just get through this task, and with it the first few weeks of my new job; but for now, I have exhausted every idea I have.




If anyone can get this to work for me, I will send you a case of the beer of your choice, if you're into that kind of thing.


This is urgent. If I can't solve this problem by Wednesday, I won't be able to keep this new job.


Thank you!!

[Micron]

While there is the option to 'Get External Data' I imagine you have to have a more automated solution. I can tell you right off that a major issue is the text in a column you want to import as double - not possible. See 05_06 row 29. There are about 9 others like it. What would be the approach, assuming I or anyone else could come up with a vba solution to coerce the value to something else (maybe 0?)?

NOTE TO ALL WHO MIGHT HELP: I'M USING 2007 AND CANNOT CREATE THE LONG TEXT TYPE AND TEST IT, SO DON'T ASSUME I'VE GOT THIS ONE. I'D BE WILLING TO COLLABORATE WITH ANYONE IF NEED BE.
THANKS.

[orange]

I'm not an Excel person.
I started with an Access 2010 blank database and did a GetExternalData Excel.
I did take your excel files and imported the first (20160617_2015_07_08) into Access with no import errors.
Then tried to append (20160617_2015_05_06) and received 9 import errors (all in the delivery number field)

Error
Field
Row

Type Conversion Failure
Delivery Number
29

Type Conversion Failure
Delivery Number
30

Type Conversion Failure
Delivery Number
31

Type Conversion Failure
Delivery Number
33

Type Conversion Failure
Delivery Number
35

Type Conversion Failure
Delivery Number
36

Type Conversion Failure
Delivery Number
37

Type Conversion Failure
Delivery Number
39

Type Conversion Failure
Delivery Number
41

I have attached the database. Hope it's helpful.

Good luck.

[deekadelic]

That's the same problem I'm having. No idea why, huh?

How weird, and how infuriating about how difficult this is. There must be hundreds of thousand of people around the world having this same problem. What was MS thinking?

Anyway, I appreciate you taking a crack at it. Thanks