By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,763 Members | 1,609 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,763 IT Pros & Developers. It's quick & easy.

Importing from Excel - Cannot maintain Cell type

P: n/a
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

Sep 4 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
ja********@gmail.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
Sep 5 '06 #2

P: n/a
ja********@gmail.com wrote in
news:11**********************@i3g2000cwc.googlegro ups.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


Sep 5 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.