Importing from Excel problem

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I am trying to import data from an Excel file to Access
table by using an update query (Excel file is linked).
Everything is going right except one column.
In Excel it contains numbers from 1 To 9999 and sometimes
a letter + 2 digits (like D32).

Destination field properties in Access table are: Text;
maximum 15 characters; no any restrictions; no masks.
The table is not in relation with other tables.

So, after the import process is finished I do not get any
error messages. All the records are imported. But in that
very column the number-values are there and letter-values
are not (just empty cell).
What is wrong? Any ideas?
 
I am trying to import data from an Excel file to Access
table by using an update query (Excel file is linked).
Everything is going right except one column.
In Excel it contains numbers from 1 To 9999 and sometimes
a letter + 2 digits (like D32).

Destination field properties in Access table are: Text;
maximum 15 characters; no any restrictions; no masks.
The table is not in relation with other tables.

So, after the import process is finished I do not get any
error messages. All the records are imported. But in that
very column the number-values are there and letter-values
are not (just empty cell).
What is wrong? Any ideas?
Click to expand...

I hope i got this right...

You have an Excel table like

A B C... AA... AAA... AAAA...
1
2
3
4
5
6
....

D32 would be a cell in column D, row 32. After import the destination cell
in access is empty. If it was a type mismatch Access would have shown an
error message, so are you sure this cell has any value?

couldn't make more of the discription, maybe you could post more specific
datails on your problem.

___
mirco
 
Hi Mike,

When you look at the linked table, are the values in the "mixed" column
displayed properly? My guess is that they are not: when *linking* to a
column with a mix of numeric and non-numeric values, Access links it as
a *number* column if there is even one numeric value in the first dozen
or so rows.

If you *import* from Excel instead, the rule is different, and a single
*text* value in the first rows will force the column to be imported as
text, which is what you need.

Another approach is to modify the values in the worksheet so Excel
itself treats them as text. Prefixing each cell's contents with an
apostrophe will do this; the little procedures below can handle this.

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub
 
I had exactly the same problem and this solution looks to
be just what I need - Cheers John and good luck Mike.

-----Original Message-----
Hi Mike,

When you look at the linked table, are the values in the "mixed" column
displayed properly? My guess is that they are not: when *linking* to a
column with a mix of numeric and non-numeric values, Access links it as
a *number* column if there is even one numeric value in the first dozen
or so rows.

If you *import* from Excel instead, the rule is different, and a single
*text* value in the first rows will force the column to be imported as
text, which is what you need.

Another approach is to modify the values in the worksheet so Excel
itself treats them as text. Prefixing each cell's contents with an
apostrophe will do this; the little procedures below can handle this.

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub


I am trying to import data from an Excel file to Access
table by using an update query (Excel file is linked).
Everything is going right except one column.
In Excel it contains numbers from 1 To 9999 and sometimes
a letter + 2 digits (like D32).

Destination field properties in Access table are: Text;
maximum 15 characters; no any restrictions; no masks.
The table is not in relation with other tables.

So, after the import process is finished I do not get any
error messages. All the records are imported. But in that
very column the number-values are there and letter- values
are not (just empty cell).
What is wrong? Any ideas?
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
I'm having a SIMILAR problem, however my tables have to be
linked not imported. I need the data to be real-time due
to changes made to them on a daily basis from users
accross our network.
Again, I have numeric and date formatted cells that
sometimes have text entered in them...i need both types of
data to show up.... how do i accomplish this ???

helpppppp

-----Original Message-----
I had exactly the same problem and this solution looks to
be just what I need - Cheers John and good luck Mike.

-----Original Message-----
Hi Mike,

When you look at the linked table, are the values in the "mixed" column
displayed properly? My guess is that they are not: when *linking* to a
column with a mix of numeric and non-numeric values, Access links it as
a *number* column if there is even one numeric value in the first dozen
or so rows.

If you *import* from Excel instead, the rule is different, and a single
*text* value in the first rows will force the column to be imported as
text, which is what you need.

Another approach is to modify the values in the
Click to expand...
worksheet
so Excel
itself treats them as text. Prefixing each cell's contents with an
apostrophe will do this; the little procedures below can handle this.

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub


I am trying to import data from an Excel file to Access
table by using an update query (Excel file is linked).
Everything is going right except one column.
In Excel it contains numbers from 1 To 9999 and sometimes
a letter + 2 digits (like D32).

Destination field properties in Access table are: Text;
maximum 15 characters; no any restrictions; no masks.
The table is not in relation with other tables.

So, after the import process is finished I do not get any
error messages. All the records are imported. But in that
very column the number-values are there and letter- values
are not (just empty cell).
What is wrong? Any ideas?
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
.
Click to expand...
 
Hi Tiffany,

This is not a good situation to be in. If you have columns in Excel that
contain mixed text and numeric (or date) values, you have to force
Access to treat them as text columns, and the only reliable ways to do
that are the ones I described in my previous post.

Both these, however, have implications for the functionality of the
Excel workbook, and - particularly if you have multiple users
promiscuously updating the worksheet - it is also *very* difficult to
set up data validation in Excel to ensure that users can't modify the
worksheet in a way that screws up the link to Access.

So basically a far better approach is to store the data in Access -
which has proper multi-user capability and data validation built in -
and modify the Excel application to retrieve and update the Access data
as required.

I'm having a SIMILAR problem, however my tables have to be
linked not imported. I need the data to be real-time due
to changes made to them on a daily basis from users
accross our network.
Again, I have numeric and date formatted cells that
sometimes have text entered in them...i need both types of
data to show up.... how do i accomplish this ???

helpppppp

-----Original Message-----
I had exactly the same problem and this solution looks to
be just what I need - Cheers John and good luck Mike.

-----Original Message-----
Hi Mike,

When you look at the linked table, are the values in the "mixed" column
displayed properly? My guess is that they are not: when *linking* to a
column with a mix of numeric and non-numeric values, Access links it as
a *number* column if there is even one numeric value in the first dozen
or so rows.

If you *import* from Excel instead, the rule is different, and a single
*text* value in the first rows will force the column to be imported as
text, which is what you need.

Another approach is to modify the values in the
Click to expand...
worksheet
so Excel
itself treats them as text. Prefixing each cell's contents with an
apostrophe will do this; the little procedures below can handle this.

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub


On Thu, 13 Nov 2003 04:36:41 -0800, "Mike"

I am trying to import data from an Excel file to Access
table by using an update query (Excel file is linked).
Everything is going right except one column.
In Excel it contains numbers from 1 To 9999 and sometimes
a letter + 2 digits (like D32).

Destination field properties in Access table are: Text;
maximum 15 characters; no any restrictions; no masks.
The table is not in relation with other tables.

So, after the import process is finished I do not get any
error messages. All the records are imported. But in that
very column the number-values are there and letter- values
are not (just empty cell).
What is wrong? Any ideas?

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top