link table from excel - data type problem

  • Thread starter Thread starter יריב החביב
  • Start date Start date
×

יריב החביב

Hello,

We use table from excel wich we link to access (get external data).

The problem is that one of the the row's in excel has two type's

of data : number and text .

If we format the row (in excel) as 'text', we don't see (in access) the data

of 'number' type, and if we format the row (in excel) as 'number', we don't
see

(in access) the data 'text' type.

What can we do ?
 
Hi

You will need to give more information to get an accurate answer

ie.
If the excel cell(s) - column contains
ABCD 9
ABCD 9&8
ABCD 9-8
ABCD 12345
That is the text string is always the same length and your numbers are
always the same place (in this case after the space) then you could use
something like =""&RIGHT(CellRef,LEN(CellRef)-FIND(" ",CellRef)) and copy
down to extra the text (change cell ref 1st of course) but if there is
numbers 1st or a post/zip code etc you need if different method. In this
case you would extract the text into a column or the numbers into another -
import these into 2 fields in access and then (iof you want to re-join) you
can concenctae or just leave them as 2 fields.

I can't understand why your column formated as text will not import into a
field (formated as text) in access - maybe there is something else in the
column / cells that is stopping this but you didn't give enough information

What is the format of the excel cell/columns - can you give a samle.
 
???? ????? said:
Hello,

We use table from excel wich we link to access (get external data).

The problem is that one of the the row's in excel has two type's

of data : number and text .

If we format the row (in excel) as 'text', we don't see (in access)
the data

of 'number' type, and if we format the row (in excel) as 'number', we
don't
see

(in access) the data 'text' type.

What can we do ?
Click to expand...


Take a look at this knowledgebase article.

Excel stores the data type with each cell and Access Import ' Link uses
the data type stored with the cell, not the data type applied to the
Excel column or the default dataype in the linked Access table.

This article gives VBA code to run from Excel to insure that all cells
are in fact a text data type; then you can link into a text fileld and
manipulate the data as needed from within Access:


(815277) - Explains the "Numeric Field Overflow" error message that
occurs when you query a table that is linked to an Excel spreadsheet.
This article provides a workaround to resolve this problem. Requires
basic macro, coding, and interoperability skills.
http://support.microsoft.com/kb/815277/en-us
 
Thank's,

Here is a sample

999242070
999242100
999242120
999242160
999242210
999242211
999242250
3L6416000
4L5339000
5L9854000
7L0749000
7L1039000
9L0959000
DD9230000
I4005
I4218
I4222
I4243
I4458
I4463
I4475
I4524
I4547
I4549
T43235001
T43235002
T45339001
T45339002
T45378001
T45383001
 
Back
Top