Gteeting ridor error file created on importing an Excel spreadsheet

[Lou_Reed]

In the attached file is a db which when I import a Excel file, which is also attached creates a list of errors.



My question is how do I get rid of the errors?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[John_G]

A couple of points.

First, even though a number of columns in the spreadsheet are hidden, Access still will import them. Hiding rows or columns is for users' convenience - Access ignores those settings.

Second (and more important) - in order for a spreadsheet to import properly, the row format must be consistant - yours is not. It is designed for human consumption, not Access', and it contains a mix of header rows, data and summary information. Only rarely will a spreadsheet like that import properly with the TransferSpreadsheet method.

When Access imports a spreadsheet into a new table, it determines the data type of each field by looking at the data in the first few entries in the corresponding spreadsheet column, excluding the first row if it contain the field names. If the first values found are numeric, it will make that table field a numeric, and then if text is found in a subsequent row, it generates a conversion error.

Open and look at your spreadsheet. Thr values in first two columns (Count and Count) of rows 2 - 9 are numeric (or blank, which doesn't matter), so Access sets those fields in the table to numeric type. Now scroll down the spreadsheet to row 53. The data in column 1 of that row is "Project" - a text value. Access cannot convert "Project" to a numeric value, so it gives you a conversion error. The row is still imported, but with no value in that field.

I think every single one of those import errors is due to the same problem.

The easiest fix for the moment is to edit the spreadsheet to remove all the extra headers (leave the first one for field names) and the summary information at the bottom.

That will take care of most of the errors, though there will still be a few caused by data in the hidden columns.

Whether the data in the resulting Access table will be useful or not, I can't say. If distinguishing between the three groups in the spreadsheet is important, then you'll have to find another way of doing that because the headers are gone.

[Lou_Reed]

I very much appreciate your answer. I was unaware as to how MS Access treats the cells thank you.

I prepare my Excel spreadsheet before it is imported into MS Access. Initially, I have column names that take up several rows. I eliminate all the rows except the bottom row (a single row) with the remaining column names.

This occurs before I ever import it into the MS access db and create a table.

I may have forgotten how to do that, but I think I did it here. So my request is what do I do when I get errors after that?

You say that Excel determines the format of a row from the first entries in the cells of that column. So what do I do when it determines a row
to be numeric (from the first few cells in that row) and then the cells after the first few start containing non-numeric data?

That is probably my biggest cause of error - after failing to get rid the multiline column names.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I am sorry to have to ask this question again. I see the errors that you are talking about, but I do not know what to do about them. Is there another way to import the Excel spreadsheet into Access?

One that does not type the field based on the values of the first few values in the column's field's.

I already remove, as I said in an earlier post, the all the rows except the bottom one in preparing my Excel Sheet for import to Access.

That leaves only one row for the column titles. That should work in MS Access.

I apparently need to remove even more fields because of the errors generated after I import the current prepared Excel spreadsheet.

But then again there may be another way to do this which was my first question in this post.

Either way, I am open to suggestions.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

On further inspection, I believe that I see your point. The spreadsheet is made up of several sections, with each section having its own set of titles. Thus getting rid of the top titles is not enough. One must get rid of the titles on each section and leave only one line for the titles at the top of the spreadsheet.

That would be effective. I just do not know how to do it. I can eliminate the titles with some simple VBA code.

But when it comes to putting them back in when the table is exported back to Excel, well that is something else.

I am just not sure what to do here.

Any help appreciated.

Thanks in advance.

Respectfully,

Lou Reed