importing Excel worksheet problem

[Jeff]

I'm importing an Excel 2002 worksheet into an Access 2002
table using the menus. The Excel spreadsheet has 4
columns all formatted as text, and one text column is all
numbers. Access SHOULD import this as text and it does,
but for some reason it converts the text numbers to E-
notation first, then converts back to text so I now have a
bunch of E-notation numbers in a text field in the Access
table. Anybody know how to fix this without having to pad
the text number column with alphabetic characters before
importing?

Jeff

 

[Ken Snell]

Insert a quotation mark ( ' ) at the beginning of each cell's value in the
number column. That will tell ACCESS to import the value as text.

 

[Joe Fallon]

Add a dummy row of data to row 1.
For the column that is all numbers use alphabetioc data for the dummy row.
e.g. "ABC"
When Access looks at the first 8 rows and "guesses" the datatype it will
guess Text for that column.

 

[Jamie Collins]

...

When Access looks at the first 8 rows and "guesses" the datatype it will
guess Text for that column.

Note it is Jet, rather than MS Access, that scans the rows. Making the
correct distinction in this case would help the OP locate the relevant
settings. For more details see:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

Jamie.

--

 

[Joe Fallon]

I am aware of the difference.
IMO it does not make a whit of difference to the OPs problem.

 

[Joe Fallon]

"Yours is not to question why. Yours is but to do or die."

I already explained why it ignores it.
Becasue they are two differnet programs and one has to "guess" what the
other means.
The guess is based on the data in the first 8 rows or so of data in Excel.
(can be changed in the registry though).

So if you had simply tried my suggestion you would have seen that it works.

 

[Jeff]

I just confused as to why Access converts the numbers when the cells' data
types are defined as text. Why does is the text data type ignored during
the import?

Jeff

 

[Jamie Collins]

I just confused as to why Access converts the numbers when the cells' data
types are defined as text. Why does is the text data type ignored during
the import?

Did you read:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

"Data type is determined column by column. 'Majority Type' means a
certain number of rows ... in each column are scanned
and the data types are counted. Both a cell's value and format are
used to determine data type. The majority data type (i.e. the one with
the most rows) decides the overall data type for the entire column.
There's a bias in favor [of] numeric in the event of a tie."

Jamie.

--

 

[Jamie Collins]

I am aware of the difference.
IMO it does not make a whit of difference to the OPs problem.

This is why I think it matters:

1) Excel is a Jet data source. This is all about how the Excel data
presents itself via Jet. The fact that MS Access also uses Jet is
merely a coincidence in this case.

2) Knowing it is a Jet process would give the OP a chance of finding
the relevant registry settings. Look in the registry under MS Access
and they'll never be found.

3) By saying 'Access looks at the first 8 rows', it makes it look like
you actually think it is MS Access doing the scanning. People in these
ngs put a lot of faith in MVPs; it's important you get the correct
technical information across, rather than not caring a whit when you
get it wrong. If you truly are aware of the distinction, then make it
clear in your posts.

Jamie.

--

 

[Jeff]

Ok, so if all the cells are in the text format, why would ANY of the cells
be converted to E-notation? No one single cell in the Excel spreadsheet is
defined as a number.

I just confused as to why Access converts the numbers when the cells' data
types are defined as text. Why does is the text data type ignored during
the import?

Did you read:

http://www.dicks-blog.com/excel/2004/06/external_data_m.html

"Data type is determined column by column. 'Majority Type' means a
certain number of rows ... in each column are scanned
and the data types are counted. Both a cell's value and format are
used to determine data type. The majority data type (i.e. the one with
the most rows) decides the overall data type for the entire column.
There's a bias in favor [of] numeric in the event of a tie."

Jamie.

--

 

[Jamie Collins]

Ok, so if all the cells are in the text format, why would ANY of the cells
be converted to E-notation? No one single cell in the Excel spreadsheet is
defined as a number.

I think there must be something else going on e.g. your columns are
not formatted Text.

Here's some steps to reproduce (execute individually from any blank
query):

CREATE TABLE
[Excel 8.0;HDR=Yes,Database=C:\Tempo\db;].ENotation
(
MyDataCol VARCHAR(35)
)
;

INSERT INTO
[Excel 8.0;HDR=Yes,Database=C:\Tempo\db;].ENotation
(MyDataCol) VALUES ('12345678901234567890')
;

INSERT INTO
[Excel 8.0;HDR=Yes,Database=C:\Tempo\db;].ENotation
(MyDataCol) VALUES ('0.1234567890123456789')
;

SELECT
MIN(LEN(MyDataCol)) AS shortest_value
FROM
[Excel 8.0;HDR=Yes,Database=C:\Tempo\db;].ENotation
;

I get shortest_value = 20, proving the value was not converted to
e-notation and has not been curtailed.

Jamie.

--