Unable to import large numeric value (even as short text) - getting error conversion type

[AnibalJ]

Hi all,

I have an Excel file that contains a field that contains a value like this:

999486291000087109

In order for Excel to display this value properly we have changed its data type to Text (otherwise you get the dreaded 15-digit limit issue with Excel and the number becomes 999486291000087000).

I have to import this file into MS Access. The data type for this column is set to Short Text. However, when I import it I get an error (it's in French since the app is running on a French-installed machine) [La premiere ligne contient des donnees ne pouvant etre employees comme nom de champs Access. L'Assistant assignera automatiquement des noms de champs valides.]. And the Import Wizard still continues but then throws another error stating: "Le champ <F4> n'existe pas dans la table destination <table name>".

It turns out that having that large number is causing issues. I have other large numbers, but that not that large. The column in the table in Access is setup as Short Text with a length of 255 so it should work OK.

As a matter of fact, if I just copy that large value (999486291000087109) and paste it into the opened table in Access it works! But if in order for it to work, I have to paste the value itself, not the row containing the value.

I have tried different things:
1. Converting xlsx to csv and importing csv instead. In this case, the error disappears and the Import Wizard "works", but all the cells that have this large number end up as an empty field in MS Access.
2. Pasting directly from xlsx or csv into MS Access. I get the same issue as 1. above.
3. Changing the data type - still no dice

The value of this column is not used as a number really, but it's used to key into another table, so it can't be Long Text (since I can't use Long Text to do a join).

Help!!

 

[Joe4]

Welcome to the Board!

In order for Excel to display this value properly we have changed its data type to Text (otherwise you get the dreaded 15-digit limit issue with Excel and the number becomes 999486291000087000).

Are you changing the column format to Text BEFORE data is entered, or after the data already exists?
If doing it after the data already exists, I don't think that will convert all the entries to Text unless you re-enter them again (numbers entered as text in Excel will have a little green triangle in the upper right corner of the cell - if you do not see this, it probably is not being recognized as Text).

La premiere ligne contient des donnees ne pouvant etre employees comme nom de champs Access. L'Assistant assignera automatiquement des noms de champs valides

Note that translates to:
"The first line contains data that cannot be used as the name of Access fields. The wizard will automatically assign valid field names."
That is telling you that there is an issue with your title row, not your data (there may be other data issues, but that is not what this is telling you).

Le champ <F4> n'existe pas dans la table destination <table name>

This translates to:
"Field <F4> does not exist in destination table <table name>"
Sounds like you do not have a perfect match between your Title row in Excel and Table field names in Access. Note that they must be named the same (extra characters could mess it up), and the need to be the same data types.

Converting xlsx to csv and importing csv instead. In this case, the error disappears and the Import Wizard "works", but all the cells that have this large number end up as an empty field in MS Access.

Importing Excel files to Access is often problematic, as Access and Excel try to talk to each other and Access "guesses" at the data type, and often guesses wrong. I believe it looks at the first 10 rows of data to make its "guess". So I often prefer to export to CSV, where I can import myself and invoke the import wizard to control the data type for each field instead of letting Excel-Access try to guess it.

 

[AnibalJ]

Welcome to the Board!


Are you changing the column format to Text BEFORE data is entered, or after the data already exists?
If doing it after the data already exists, I don't think that will convert all the entries to Text unless you re-enter them again (numbers entered as text in Excel will have a little green triangle in the upper right corner of the cell - if you do not see this, it probably is not being recognized as Text).

Thanks for the thorough reply. Let me go over more details.
The Text is changed in Excel after the data is entered into the spreadsheet. There's a little green triangle in the upper right corner of the cell. Is that the correct procedure?

Note that translates to:
"The first line contains data that cannot be used as the name of Access fields. The wizard will automatically assign valid field names."
That is telling you that there is an issue with your title row, not your data (there may be other data issues, but that is not what this is telling you).

Exactly, that's what I thought, but there's nothing else in the first line. There are no special characters or anything. As a matter of fact, when I export to CSV I can clearly see that the first row just has the three column headers and that's it. So, to me, that error is bogus.

I have uploaded the xlsx file and it's available here if you want to try it: tests_allergenes_alimentaire_2021-06-02.xlsx

This translates to:
"Field <F4> does not exist in destination table <table name>"
Sounds like you do not have a perfect match between your Title row in Excel and Table field names in Access. Note that they must be named the same (extra characters could mess it up), and the need to be the same data types.

They're the same though. Look at the xlsx file that I made available via google drive. You'll see the first row has three columns: Source, Code, Valeurs; and they match what I have in MS Access. See screenshots attached.

Importing Excel files to Access is often problematic, as Access and Excel try to talk to each other and Access "guesses" at the data type, and often guesses wrong. I believe it looks at the first 10 rows of data to make its "guess". So I often prefer to export to CSV, where I can import myself and invoke the import wizard to control the data type for each field instead of letting Excel-Access try to guess it.

Right, what you're stating about type casting and what Access does is what I found out by looking at other posts online. So, I changed the procedure to export to CSV and then import into Access. However, that's not working either. When I go with CSV look at what happens (see image access_with_csv.jpg). You can see the contents of the CSV in csv.jpg.

So, if I stay with Excel it doesn't work, since I can't import the xlsx file with Text. If I go with CSV the import works, but the field in MS Access is empty. There must be a way to get around this problem.

Thanks a lot for your help. If you want to have access to the DB itself let me know. The only thing is that it's quite large (5MB) since there's other data in it - I guess I could trim it all the way to just show the issue. If that's needed let me know and I'll go through that exercise.

 

[Joe4]

Yes,it might be helpful if you could upload a copy of your Access database too.
I would recommend making a copy of it, deleting ALL of the data out of it, then running a "Compact & Repair" on the database.
This will purge all the deleted data, and make the size of the database much smaller.
We do not really need to see the existing data to test importing new data.

One other thing I should note. Are you using a saved Import Specification when importing the CSV?
You may want to try creating a new Import Specification with the Import Wizard.
That has settings that it remembers too, and it is often difficult to change some of those things on an existing specification.
I find it typically works better to create a new one from scratch.

 

[AnibalJ]

Hi Joe,

Well, I finally found the issue and how to fix it. I had a call with another user of this procedure and noticed that she was able to apply the procedure even though she had long numbers in Excel!!!

So, we had two issues that I described in this post:
1. Error when importing, and the error talking about the first line
2. Error about long integers

The first one was indeed some hidden stuff in Excel. I still don't know what the heck it was, because I couldn't see it. But to fix it, I selected all the columns from the 4th to the right, and deleted everything (even though there was nothing showing). That fixed the first error. So, as you had mentioned, something was hidden in there.

For the second (and most interesting issue to me) the procedure that I now found to work all the time, is to select the whole column and change the whole column to Text in Excel. In my xlsx file I had Numbers and Text and that caused issues and lost data. But by changing the whole column to Text I assume that Access is now doing the full import as Text and that works! I have the long numbers in Access as Text and everything runs OK!

Thanks Joe again for helping out though. With this conversation I was able to follow the whole procedure much better and it helped find the solution. I wanted to post it here for some other people that could potentially run into the same issue.

Cheers!

-Anibal

 

[Joe4]

Excellent! I am glad you got it figured out. And thanks for posting the details, as it ,may help some other user down the road.

For the first issue, it appears then that the error message it was returning was accurate (or at least truly indicative of what is going on), as there were extra columns messing things up.
It is odd to me that we do not see those extra columns in the CSV file image you posted.

For the second issue, I should have probably confirmed that with you. I didn't realize that you only converted some of the values to text, and not the whole column. Access definitely does not like that. It does not like "mixed" data types in the same column.