Importing Excel spreadsheet and getting Type Conversion Failure error.

[Lou_Reed]

I am having trouble when I import an Excel spreadsheet into Access. The error is "Type conversion Failure".

The thread on this was started here:

https://www.accessforums.net/showthread.php?t=69439

I know that Access ignores the first row of the spreadsheet If you tell it to) and read the values in the second row of cells to determine what type of data
is in the column. Once it selects a data type and if sees an inconsistency (another data type) then it registers a:

Type Conversion Failure

The spreadsheet that I am working is in that situation now. I know that one trick is to convert all the data in Excel into text and then import to Access and then change the actual numerical data (which is now text) back to numbers.

That seems logical.I am looking for a way to program this. I have found a way to convert all cell values (either text or numeric) to text.

That is the first step.

I already have a program to import the Excel spreadsheet.

That is the second step.



I am just confused about third step.

Once all of this has been imported into Access and is in a table as text, how do i go through and change what was initially numeric data
and was converted to text data back to numeric data.

I can do it one at time, but that could get tedious.

There must be a way to program and automate this.

Any help appreciated. thanks in advance.

Respectfully,

Lou Reed

[Minty]

Normally you would do this with a staging table (all text) in access, then use an append query to take that data - convert as necessary to the correct format into your final destination table.
Does that help?

[Lou_Reed]

I very much appreciate your answer. However, I do not understand the last part of your reply.

"then use an append query to take that data - convert as necessary to the correct format into your final destination table.
Does that help"

Please elaborate.

Respectfully,

Lou Reed

[Minty]

Start with a select query - Assume you have a date field called StartDate in your staging table. Bear in mid that although it "looks" like a date it is in fact text.
In the Select query you would create a calculated field along the lines of
ConvertedStartDate: CDate([StartDate])
You would repeat this for each field except the actual text ones - so a long Number field CLng([YourLargeIntegerField) etc.
In other words convert each field to it's correct data format.

Once this is pulling through what looks like all the correctly formatted data you would change the query to an append query, selecting your final Target table as the destination.
Then in the query designer under each field select the field you want to add the data to in your target table.

Although setting up and testing this query will take a while once done you don't need to do it again.
You may have to do some extra work to accommodate any null values, but they should become obvious at testing.

[Lou_Reed]

I am beginning to understand. Do you have an example or at least a website that you can refer me to?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed