Importing from Excel - Cannot maintain Cell type

[jason.teen]

Hi,

I am having trouble importing a spreadsheet from Excel into an Access
Database.

I have noticed that even specifying the Cell Type of the Excel Data
Cell, When I import it, still get Import Errors. Like Type Mismatch.

The funny thing is that its all done automatically via the Wizard, so I
dont even specify what the field type should be in my database, but it
seems to for eg. select "Number" as the type, but then later down, it
finds some text, then therefore causes the error.

Any ideas?

Thanks in advance

[Smartin]

ja********@gmai l.com wrote:

Hi,

I am having trouble importing a spreadsheet from Excel into an Access
Database.

I have noticed that even specifying the Cell Type of the Excel Data
Cell, When I import it, still get Import Errors. Like Type Mismatch.

The funny thing is that its all done automatically via the Wizard, so I
dont even specify what the field type should be in my database, but it
seems to for eg. select "Number" as the type, but then later down, it
finds some text, then therefore causes the error.

Any ideas?

Thanks in advance

IIRC the import wizard looks at the first few rows to make a guess about
the data type for that column. I don't think it pays attention to
formats you establish in Excel.

The import wizard gives you the opportunity to change the assumed data
types.

--
Smartin

[Alan Carpenter]

ja********@gmai l.com wrote in
news:11******** **************@ i3g2000cwc.goog legroups.com:

i,

I am having trouble importing a spreadsheet from Excel into an Access
Database.

I have noticed that even specifying the Cell Type of the Excel Data
Cell, When I import it, still get Import Errors. Like Type Mismatch.

The funny thing is that its all done automatically via the Wizard, so I
dont even specify what the field type should be in my database, but it
seems to for eg. select "Number" as the type, but then later down, it
finds some text, then therefore causes the error.

Any ideas?

Thanks in advance

For what it's worth, Jason, I'm currently converting a few very messy
spreadsheets into Access97. Many columns have numbers in the first few
hundred rows (e.g. 1001, 1002, 1003) followed by a mixture (e.g. 1001A,
1002D, 1003Q). Like you, I find my Access gets miffed and says, "If you're
going to try to trick me, I just won't do anything."

I lowered my stress level by increasing the tedium level and going back to
old-fashioned methods. The steps were:

1. From Excel, Save As Text.
2. From Access, Import, changing all Fields (Numbers, Dates ... ALL) to
Text.
3. After Import, change Field Types to something more logical.

Usually a change in Table Design was immediately accepted on Saving.
If I had an error, I'd check manually. Usually an expression in a Query
would help - for example Expr1: CDbl([MyNumberField]). If the Query opened
a Datasheet, I'd sort that column both ascending and descending, glancing
over the side by side columns, comparing. This technique catches a
surprising number of oddities in an amateur spreadsheet. If I saw #error#.
I could see the Text and work out what was required.

Slow, boring, tedious; yes,

The advantage - It helped me gain a feel for the data which was of great
help at Table design time.

As I said - for what it's worth.

Cheers,
Alan Carpenter