Type Conversion Failure With Excel Data Import

[OasisUnknown]

Hello All,

I am having an issue with a type conversion error With some of my data on an excel import.

All of my fields in all of my columns on the source data for excel are set to a format type of text.
In the cells is a date in military format (ie 20160809) So excel wants to treat it as a number even though I have told the cell to treat it as text so it does give that little green icon that says there is a error in this cell.

but I want the data to just be read as text.

When I import the data into Access it gives a type conversion error on this field even though I have my data type set to short text.


The weird thing is it does not give the type conversion error on all the fields and intermittently will give the error on different columns.

I have about 7 columns that the data is this exact same set up and it will give the error on one or two of the columns but not all seven.

Any one have any ideas about what could be going on. I just want to take the data as is from excel and store it as text. I convert it to a normal date format with queries after the fact but I cant do that if the cells don't get imported.

I should also note that today it did it with a field that is text and not just a date. the data in the field looks like (38mo 14dys)


When I pull the data from the system I save it into an excel workbook with 4 other tabs (total of 5) and how I have been getting it to my database because I only need 1 of the 5 tabs is I right click on the tab and copy and move it to a brand new worksheet to break out the tab that I need and I import that new workbook into my database.

 

[chadcollings]

Re: Type Conversion Faile With Excel Data Import

Can you attach the workbook with the data?

 

[OasisUnknown]

Re: Type Conversion Faile With Excel Data Import

The data itself is of a sensitive nature so not the actual workbook but I can put some fake information in.

But you have to tell me how to attach it to this forum because I looked the other day and could not figure our where the attach button was.

 

[james_lankford]

Re: Type Conversion Faile With Excel Data Import

pretty sure access doesn't care what the destination table looks like

I think Access looks at the first 8 rows of the source data and tries to figure out what the destination data type is based on that

so if your first 8 rows look like a number
20160809
then it thinks the destination should be a number

then if row 9 looks like
38mo 14dys
it fails

I know that's not how you're current table looks, but that's just an example

 

[Joe4]

Re: Type Conversion Faile With Excel Data Import

I think Access looks at the first 8 rows of the source data and tries to figure out what the destination data type is based on that

Yeah, it does this, though I am not sure if the number is 8 or 10.

This is often problematic, as Access tries to "guess" which data type to use based on the data, and doesn't really allow you to override that. So importing directly from Excel to Access can be problematic and maddening.
What I often do is export my Excel file to a Tab-Delimited file, then import that Tab-Delimited file into Access, which allows you to set you own Import Specification (can use the Import Wizard to build it). Then you have control over the process, and it goes much smoother and as expected.

 

[OasisUnknown]

Re: Type Conversion Faile With Excel Data Import

Ok I am understanding what is wrong with my import process.

I import from access to a temporary table that is built on the fly based off of the base table in my database. So to start whole the temp table does not exist and then when a file is chosen for import a temp table is created from the excel data.

I pass the temp table through what I call the gauntlet error check.

It checks to see if the number of columns matches what is expected in the access table. (Ie if 13 columns are in the excel file it checks to see if 13 cols is required on the access table. If the numbers does not match the import fails and the temp table is thrown away)

Then it checks the column names. And ensures a 1 to 1 match to ensure every column is in the correct order for the import.

If the temp table passes both of these checks then all of the data is deleted from the access table and all of the data from the temp table is passed to the access table and then the temp table is thrown away ready for the next import.

I did it this way so I could use the same vba code for all my error checking Reguardless of what data is being imported. I just have to tell it what table to look at for its error checks.



My issue is coming from what you guys said.

When the data is imported to the temp table. Access is defining the military date fields as numbers. And the type conversion error happens when the temp table data is transfered to my perm table.

To fix this I just need access to always import the data as a short text field on the temp table. Any ways to do this?

 

[Joe4]

Re: Type Conversion Faile With Excel Data Import

Here is what you can try.

Instead of recreating your Temp Table each time, and being at the mercy of how Access determines it should be set up, set-up the Temp Table the way you want.
A good way would be to copy the Structure of your Final table, so your Temp Table has all the same Data Types, key fields, and Indexes as your Final Table.
Then, after each time you do this, just delete the data in your Temp Table (and not delete the actual Temp Table).

If it does not allow you to import the Excel data into the properly formatted Temp Table, then I am afraid you may need to use the method I proposed and export your data to a text file, and import that into Access (instead of the Excel file).

 

[OasisUnknown]

Re: Type Conversion Faile With Excel Data Import

The reason why I did not do it this way is because I would need 8-10 different temp tables. 1 for each of the source data I need to pull into my database.

However. I wonder if it's possible to just copy a table , delete all the data off the copy, import, after error checks update the data and throw away the copy.

This is all overly complicated for a normal database. But this particular database is unique. I am combining a lot of different information from many different sources. And if the data is not right then everything will break. So.i need my error checks as a safe guard. I won't be around to maintain this database. So I need to be able to just hand it off and it do its best to not break.

 

[Joe4]

Re: Type Conversion Faile With Excel Data Import

If they are all Excel files with these sort of issues, you are not going to be able to import it like you want. The only other way I have seen is to write some complex VBA code that writes the Excel data directly to the Access tables. I think that is a highly advanced method, one that I have never tried before.

The method I proposed doesn't really need to be that much more complicated than what you are already trying to do. You can pretty easily write Excel Macros to export Excel files to tab-delimited text files. And the steps after that are pretty much the same as what you are trying to do now. If you export your Excel files to the same location with the same file names each time, you can easily use an Access Macro to import the files to the Temp Tables, run your Append Queries, then delete the data from the Temp tables. So your users can run all those steps with a single click. Even if you wanted to make it more dynamic (in regards to file names), you can convert the Macros to VBA code, make it more dynamic, and just call/run that VBA code with a single click.

The only other thing I can think of is instead of importing your Excel files, trying linking them instead, and see what those linked files look like in Access. You can run queries off of linked Tables the same way you can off of native Access Tables. If the data isn't quite right, but can be manipulated to be right with a query calculation, you could do that right in your Append Query.
And if you use the same file names each time, you can overwrite the existing linked Excel files with your new ones each time, and the new data will be reflected in your Access linked table.

 

[Joe4]

Re: Type Conversion Faile With Excel Data Import

I just remembered there is one trick I have seen some members do.
Since Access uses the first 8 or 10 rows to determine the data type of each field, you may be able to "trick" Access in to doing what you want in some cases by having a dummy row of data in your first data row. For example, if your entries for a field look like "20160809", and you want them to come in as Text, have a dummy row of data at the top and have something like "Dog" in this cell. Access will see "Dog", and make that field Text.

Then, you can ignore/delete any record that has the word "Dog" in this field, to keep it out of your final Access table (if you are importing to a temp table first, just have your Append Query select records where that field is not equal to "Dog" - if importing the data to your final table directly, just run a Delete Query to delete any records where that field equals "Dog").

Depending on which fields are causing issues that may or may not work for you.