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

Importing error - Conversion

P: 91
I am importing data from spreadsheet to access database. One of the column in the spreadsheet has data as:
a. Purely numbers
b. Purely alphabets
c. Alphanumeric

The same field in the access is set as "Text" data type.

On importing the spreadsheet, I am getting conversion error for this columns. It has only imported cells with purely numeric characters (point a above).

By nature, the cells will have data type as mentioned above (point a,b, and c).

What do I do to import all the data?

Kindly advise
Mar 12 '12 #1

✓ answered by NeoPa

Good question Mihail. I'm not so expert that I could say off the top of my head, so I did a test with the following data :
Expand|Select|Wrap|Line Numbers
  1. 1  1  ="1"  '1
  2. 2  2  ="2"  '2
  3. 3  3  ="3"  '3
Column A contains just the values and resulted in a column in the table of type Double.
Column B contains just the values but formatted as String and resulted in a column in the table of type Double.
Column C contains a string representation of the values using a formula and resulted in a column in the table of type Text.
Column D contains a string representation of the values using the special string introduction character (') and resulted in a column in the table of type Text.

I also tried the same test after replacing each of the formulas with the values of those same formulas (Edit | Paste | Special Values) and the results were unchanged.

@sg2808
That means either of the solutions from columns C or D should work for you. You should only need to do it for the top value (Row #1 - or #2 if using headers) as it will recognise that as meaning a numeric field will fail to hold the data.

Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,186
I'd need more precise details of how you're doing it to help towards a solution, but the reason is that DoCmd.TransferSpreadsheet works in a way that the coder has very limited control of how the data's imported. It will scan the spreadsheet and determine 'intelligently' what data it expects there. It will then use that format totally obliviously to the actual data in lower columns.

A way to fool it would be to include a dummy record at the top with alphabet characters in those columns where Text import is required.
Mar 12 '12 #2

100+
P: 759
From what NeoPa say I think that you need to first manage this in Excel by ensure that the cells in that column are all formated as Text.
Mar 13 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
I'm afraid not Mihail. That has no bearing on the result whatsoever :-( (It would be so much easier if did).

Actually, you need to ensure that at least one row near the top contains a value that cannot be interpreted as a number. That causes the import to realise it needs to be set up as a Text field.
Mar 13 '12 #4

100+
P: 759
You are the expert, NeoPa. So I must believe you :).
May I ask what happen if the first cell (in Excel) have a text format but contain a string like a number ? (i.e "132.45") How Access interpret that ? As string (text) or as number ?

Thank you !
Mar 13 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
Good question Mihail. I'm not so expert that I could say off the top of my head, so I did a test with the following data :
Expand|Select|Wrap|Line Numbers
  1. 1  1  ="1"  '1
  2. 2  2  ="2"  '2
  3. 3  3  ="3"  '3
Column A contains just the values and resulted in a column in the table of type Double.
Column B contains just the values but formatted as String and resulted in a column in the table of type Double.
Column C contains a string representation of the values using a formula and resulted in a column in the table of type Text.
Column D contains a string representation of the values using the special string introduction character (') and resulted in a column in the table of type Text.

I also tried the same test after replacing each of the formulas with the values of those same formulas (Edit | Paste | Special Values) and the results were unchanged.

@sg2808
That means either of the solutions from columns C or D should work for you. You should only need to do it for the top value (Row #1 - or #2 if using headers) as it will recognise that as meaning a numeric field will fail to hold the data.
Mar 13 '12 #6

P: 91
Brilliant. You are a guru !
Mar 14 '12 #7

NeoPa
Expert Mod 15k+
P: 31,186
No worries. I added a brief addendum to the earlier post to ensure the requirement was clear. You certainly don't need to 'fix' each row. Only one at or near the top of the data is required.
Mar 14 '12 #8

Post your reply

Sign in to post your reply or Sign up for a free account.