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.

 

[Micron]

Re: Type Conversion Faile With Excel Data Import

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.

Not sure this will help if we're looking at 8 rows (the only reference I ever read on that was by Allen Browne, and I believe the number was 8). I believe you are close with the trick though. Some would start every cell value in a field that must be text but may contain numbers, with an apostrophe ('). Excel does not display this character but coerces the data type to text, and Access (or so I've read) accepts the cell data as a text value only. Of course, these tricks do not help if you are trying to ensure the transported data is numeric.

 

[OasisUnknown]

Re: Type Conversion Faile With Excel Data Import

Okay so I have done some research on this problem and honestly it is wide spread.

What I have tried so far.

I have created a copy of the table structure and then tried importing the data to that new table and that failed.

I have tried to create a dummy record on the new table with all strings "AAA" in each field and then import the data
and that failed.

I cant really figure out linking excel to my database but that wont work for my situation anyways because the excel file may not always be in the same location for every situation.

If I could link the excel file and then transfer it permanently into the database and break the link.
I would be willing to try that but someone would have to help me through the process.


All of this leads to the conclusion that the error comes at the moment when the data is transferred from excel to access because of the reasons stated above that Access is looking at the first few rows and trying to determine that its smart and determine the format of the values.

It really should just take what ever the cell format is set to and if its set to general then try and figure out what the format should be.



However, one workaround that does exist is to add a dummy row of strings ("AAA") to the excel file before importing. and then access looks at this first record and makes all the formatting strings.


The part that does not work for me is that my end user will have to add this dummy record every single time before importing and it will be almost impossible to make sure that they do it and do it correctly.

I would much rather add this dummy record myself just before importing through VBA.
but I don't know where to start.

Is it possible to add a new record to excel from access before it gets transferred to the database

I would assume the process would go something like this.

link the excel file
add the dummy record
transfer the data
delete the dummy file from the record.


if anyone has any advice or suggestions I would be very interested.

 

[Micron]

Re: Type Conversion Faile With Excel Data Import

I cant really figure out linking excel to my database but that wont work for my situation anyways because the excel file may not always be in the same location for every situation.
If I could link the excel file and then transfer it permanently into the database and break the link.
I would be willing to try that but someone would have to help me through the process.

To link or import manually, you choose Excel from the Get Data tab of the ribbon. You can import, link or append the records and it doesn't matter if the file is not in the same place each time if you don't mind navigating to it each time. You can save the import specification, but it's not much use if the location will change frequently. You can also copy from Excel and Paste Append into Access - I can't recall if this causes data transfer issues or still causes truncation on memo (long text) fields.

Is it possible to add a new record to excel from access before it gets transferred to the database

Yes, but if the location varies, you'd need a way to pass the location to Access. Typically this is done via the MSOFileDialogFilePicker, which is coded in vba. Regardless, I see that your belief is that Access only considers one record in determining the data type, yet we've been saying that it seems to take 8 rows, so are you saying your experience tells you otherwise?

Yes the problem is wide spread and fortunately for me, I've not had to contend with it much.

 

[OasisUnknown]

Re: Type Conversion Faile With Excel Data Import

I see that your belief is that Access only considers one record in determining the data type, yet we've been saying that it seems to take 8 rows, so are you saying your experience tells you otherwise?

Yes the problem is wide spread and fortunately for me, I've not had to contend with it much.

Yes I have tested it. If I add 1 dummy record of text to the excel file before importing access imports the data correctly as I expect it to.

I have read many places that it checks the first 8 rows but for what ever reason 1 dummy record fixes the issue as a work around.

I am already selecting the file with the dialog picker so I just need help with adding the 1 row of dummy text after the file has been selected. I will post my code tomorrow because it's late and I need my 3 hours of sleep before work tomorrow.

 

[Joe4]

Re: Type Conversion Faile With Excel Data Import

if anyone has any advice or suggestions I would be very interested.

So, I take you aren't up to using my advice of exporting the Excel file to a Text file?
I have encountered this issue you talk about, and this method has gotten me around the issue every time. And you can even create an Excel macro to export the data so it is not a manual process.

 

[OasisUnknown]

Re: Type Conversion Faile With Excel Data Import

Thanks for your advice joe4. I have considered it but in all honesty my end user is probably not that hip on excel and when I am gone I won't be there to walk them through how to use a macro. If something breaks they will be out of luck and the system I have spent 4 months building will not be able to be used. I am just trying to avoid any extra steps and handle the entire process through access that way I have complete control over how things get imported. I am sure your system would work I just don't trust my users to have the knowledge base to do it correctly when I am gone.

 

[xenou]

Re: Type Conversion Faile With Excel Data Import

I think it will just as easy (if not easier) to code exporting to a text file, as it code adding a dummy row. Either way, you write the code as Excel Vba code. You can run the Excel VBA code from Access by opening the workbook (using code) in Access and automating Excel from Access. For help writing an Excel routine you can create a thread in Excel (or search for Excel vba tips to figure it out). There's qualified people in this forum, but fewer of us so not able to help so quickly with Excel code.

 

[Joe4]

Re: Type Conversion Faile With Excel Data Import

I have considered it but in all honesty my end user is probably not that hip on excel and when I am gone I won't be there to walk them through how to use a macro.

You can make it as simple as clicking a button. Or opening a file. Or even scheduling it to run automatically.
Really can't make it much more simple than that.
You can even have your Access database run the Excel VBA code as part of the import process.

I am just trying to avoid any extra steps and handle the entire process through access that way I have complete control over how things get imported.

That is just the thing we are trying to get around. The issue is this - if you import an Excel file directly into Access, you have no control over how it is going to import it (at least in importing in that manner - I believe I have seen some people in the past write some complex VBA code to import Excel data record-by-record). The method I suggested gives you that control you desire.

If something breaks they will be out of luck and the system I have spent 4 months building will not be able to be used.

If you have been working out it for 4 months, I imagine that you must have a fair amount of programming already built-in to it. So, why the hesitation to add a little more to get the job done?

 

[OasisUnknown]

Re: Type Conversion Faile With Excel Data Import

No hesitation. I just have never written code in access to execute something in an excel process. So I just did not know where to begin. But you guys have pointed me in the right direction to do more research. My biggest issue is always I know I can do something but I don't always know how to execute it until I do more research.

Thanks for your help xenon I appriciate every bit.

I have tried to give back to this forum the best I can in answering at least one other question for each one that I ask myself. But this has been my goto place when I have been searching for information.

 

[Joe4]

Re: Type Conversion Faile With Excel Data Import

I just have never written code in access to execute something in an excel process.

Take a look here: http://www.mrexcel.com/forum/micros...ss-visual-basic-applications.html#post1506053