Importing error - Conversion

[sg2808]

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

[NeoPa]

I'd need more precise details of how you're doing it to help towards a solution, but the reason is that DoCmd.TransferS preadsheet 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.

[Mihail]

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.

[NeoPa]

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.

[Mihail]

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 !

[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 :

1  1  ="1"  '1
2  2  ="2"  '2
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.

[sg2808]

Brilliant. You are a guru !

[NeoPa]

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.