Import trouble...

  • Thread starter Thread starter Antonio Medina
  • Start date Start date
A

Antonio Medina

I am attempting to import data from a MS Excel spread
sheet into Access for the first time. This particular
spread sheet(s) changes 10 different hands before it is
complete. There is header/spreadsheet ID information in
the first 7 rows of the spreadsheet, then Row 8 contains
the field names of the 22 individual data fields. Row 9
and on contains the data.
Initially, I removed the Header information and Access
made a table (with an access created auto number as
primary key). Three cells created an error table due to
conversion troubles. I changed the data types in access
and saved the table.
Here is my problem, after pasting/adjusting additional
data (from different spread sheets of the same exact
nature as the first) to the original spread sheet so that
the format is all the same for import, Access gives me an
error that states "An error occurred trying to import
file 'C/Documents and Settings/760 Softmed3/My
Documents/RoundingBillConversion2.xls' The file was not
imported"
But, if i import the data into and ask access to make
another "new" table, it imports the data with...(also the
three same conversion errors as before)
I have never done this before, I am upset and would
appreciate any help at all..

One thing of note, some of the cells in the Excel
Spreadsheet have a small, green, triangle in the upper
right hand corner that states the number has been saved as
text...I change the format of the cell(s) to text (as some
of the numbers have a letter preceeding the number) and
hit save....but the green triangles dont go away...is this
a problem??
 
Hi Antonio,

In Excel, any cell can contain any data, and the data may or may not be
formatted in a particular way. In Access, by contrast, every field has a
fixed data type and can only contain a value of that type ( double,
text, etc.). When Access imports from Excel to a new table, it guesses
the field types to use by examining the data in the first few rows of
the Excel table - and often gets it wrong.

One or more of the following is likely to make importing work better:

1) As far as possible, ensure that the Excel data is consistent. For
instance, if a column in Excel is meant to contain numbers, make sure
that there are no non-numeric values in it (e.g. as "-" or "N/A").

2) ensure that at least one row in the first few rows contains
"characteristic" data for the field types you want. For instance, if one
of the columns in Excel contains a mix of numbers and text, make sure
that there is at least value in the first few cells of this column that
cannot be interpreted as anything except a string. (It must be an actual
string: formatting a cell as Text doesn't do the job. To force a number
to be interpreted as a string, precede it with an apostrophe, e.g.
'77012. The apostrophe does not appear in the worksheet and isn't
imported into Access, but it guarantees that the cell contents will be
treated as a string even if they look like a number.)

3) Instead of importing to a new Access table, create the table manually
using the field types you want and then import the Excel data to that
table. The field names in the table must be the same as the column
headings in Excel.

By the way, you don't need to delete the other data from the worksheet.
Instead, you can use Excel's Insert|Name|Define command to define the
table (headings and data) as a named range. Access can then import it by
name.

As for the green triangles: these warn you that Excel has detected
something unusual about those cells. If it's numbers formatted as text,
I'd expect these to cause problems (as in (1) above) if they are in
columns that you want to be imported to numeric fields; if they are in
columns that you want to import to text fields they shouldn't do any
harm.


I am attempting to import data from a MS Excel spread
sheet into Access for the first time. This particular
spread sheet(s) changes 10 different hands before it is
complete. There is header/spreadsheet ID information in
the first 7 rows of the spreadsheet, then Row 8 contains
the field names of the 22 individual data fields. Row 9
and on contains the data.
Initially, I removed the Header information and Access
made a table (with an access created auto number as
primary key). Three cells created an error table due to
conversion troubles. I changed the data types in access
and saved the table.
Here is my problem, after pasting/adjusting additional
data (from different spread sheets of the same exact
nature as the first) to the original spread sheet so that
the format is all the same for import, Access gives me an
error that states "An error occurred trying to import
file 'C/Documents and Settings/760 Softmed3/My
Documents/RoundingBillConversion2.xls' The file was not
imported"
But, if i import the data into and ask access to make
another "new" table, it imports the data with...(also the
three same conversion errors as before)
I have never done this before, I am upset and would
appreciate any help at all..

One thing of note, some of the cells in the Excel
Spreadsheet have a small, green, triangle in the upper
right hand corner that states the number has been saved as
text...I change the format of the cell(s) to text (as some
of the numbers have a letter preceeding the number) and
hit save....but the green triangles dont go away...is this
a problem??
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top