TransferSpreadsheet changes data type

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet that contains a column of invoice numbers. A sample
would be 21001320. Occassonally we apened a letter to the end of the invoice
number, for example, 21001320A. I want to import that data into an Access
table, but it fails because it the method assumes the colums is numeric. In
excel I specified the column as text, I've specified the field in the table
is text, yet transferspreadsheet insists on converting it to a number.
Altenatively I've tried opening a linked table, same problem. I've also
tried uising an ADO recordset connection - no good. The final thing I tried
was to sort the column in excel (something I really don't want the user
doing) so that the invoice numbers with the suffix appeared first - same
problem, excpet that it now only imports the items with a suffix while
ignoring the rest of the invoice number.

Does anybody have a solution???? How can I force a data type?
 
Jet looks at the first "x" number of rows (actual number is based on a
setting in a registry key -- default usually is 25, but can be as small as
8) to see the data that are present, and then decides what the data are for
the import. In your case, I'm guessing that the nonumeric values are in a
row farther down than the 25th row.

Can you put in the first row the "nonnumeric" value so that ACCESS sees it
and knows that not all values are numbers.

Or can you go into EXCEL and insert a ' character in front of each of the
values in the column? That will tell ACCESS to treat the value as a number?

You could try using an append query to read the data directly from the EXCEL
worksheet (EXCEL file must be closed when you do this):

INSERT INTO DestinationTableName ( Field1, Field2, Field3 )
SELECT ColA AS Col1, ColB AS Col2, ColC AS Col3
FROM [Excel 11.0;Database=C:\MyFolder\MyWorkbook.xls;].[MySheet$]


If nothing else works, you can use Automation (via VBA code) to open the
EXCEL file and read the spreadsheet cell by cell and write the values into a
recordset that is based on the target table.
 
Thanks, but...

I've tried sorting the spreadsheet to have the "text" values first. When I
do this the transferspreadsheet treats the entire column and text and I get
#NUM for all of the invoice numbers that do not have a character appended.
This is the exact opposite as when the "numbers" appear first (the result is
#Error).

I also tried your suggestion below and with the same result. I tried
Transferspreadsheet after that failed.

The non-numerics are randomly disbursed throught the file and represent only
abou 1% of the total values. I am reluctant to write code that will add an '
before each of the values simply because the spreadsheet comes from outside
of our office and I don't want to alter the original. Yes, I know I could
use automation to automatically create a copy and work with it. I just
didn't expect that it would be this difficult to force a mixed data type to a
string.

This proplem did not start to occur until we upgraded from Office 2000 to
Office XP (2002). I am in great fear of August when we upgrade to 2003.
(Not sure why we've decided to do that mind you - maybe I can stop it!)

Again, any suggestions short of a major re-write of the code to get this to
work?

Thank you in advance
Gord

Ken Snell said:
Jet looks at the first "x" number of rows (actual number is based on a
setting in a registry key -- default usually is 25, but can be as small as
8) to see the data that are present, and then decides what the data are for
the import. In your case, I'm guessing that the nonumeric values are in a
row farther down than the 25th row.

Can you put in the first row the "nonnumeric" value so that ACCESS sees it
and knows that not all values are numbers.

Or can you go into EXCEL and insert a ' character in front of each of the
values in the column? That will tell ACCESS to treat the value as a number?

You could try using an append query to read the data directly from the EXCEL
worksheet (EXCEL file must be closed when you do this):

INSERT INTO DestinationTableName ( Field1, Field2, Field3 )
SELECT ColA AS Col1, ColB AS Col2, ColC AS Col3
FROM [Excel 11.0;Database=C:\MyFolder\MyWorkbook.xls;].[MySheet$]


If nothing else works, you can use Automation (via VBA code) to open the
EXCEL file and read the spreadsheet cell by cell and write the values into a
recordset that is based on the target table.

--
Ken Snell
<MS ACCESS MVP>

Gord said:
I have a spreadsheet that contains a column of invoice numbers. A sample
would be 21001320. Occassonally we apened a letter to the end of the
invoice
number, for example, 21001320A. I want to import that data into an Access
table, but it fails because it the method assumes the colums is numeric.
In
excel I specified the column as text, I've specified the field in the
table
is text, yet transferspreadsheet insists on converting it to a number.
Altenatively I've tried opening a linked table, same problem. I've also
tried uising an ADO recordset connection - no good. The final thing I
tried
was to sort the column in excel (something I really don't want the user
doing) so that the invoice numbers with the suffix appeared first - same
problem, excpet that it now only imports the items with a suffix while
ignoring the rest of the invoice number.

Does anybody have a solution???? How can I force a data type?
Click to expand...
Click to expand...
 
The #NUM error is not what I see when I put text strings at top of a column
that also contains numbers. But it appears that you're importing into an
existing table? Try letting TransferSpreadsheet import into a new table; is
that better?
--

Ken Snell
<MS ACCESS MVP>




Gord said:
Thanks, but...

I've tried sorting the spreadsheet to have the "text" values first. When
I
do this the transferspreadsheet treats the entire column and text and I
get
#NUM for all of the invoice numbers that do not have a character appended.
This is the exact opposite as when the "numbers" appear first (the result
is
#Error).

I also tried your suggestion below and with the same result. I tried
Transferspreadsheet after that failed.

The non-numerics are randomly disbursed throught the file and represent
only
abou 1% of the total values. I am reluctant to write code that will add
an '
before each of the values simply because the spreadsheet comes from
outside
of our office and I don't want to alter the original. Yes, I know I could
use automation to automatically create a copy and work with it. I just
didn't expect that it would be this difficult to force a mixed data type
to a
string.

This proplem did not start to occur until we upgraded from Office 2000 to
Office XP (2002). I am in great fear of August when we upgrade to 2003.
(Not sure why we've decided to do that mind you - maybe I can stop it!)

Again, any suggestions short of a major re-write of the code to get this
to
work?

Thank you in advance
Gord

Ken Snell said:
Jet looks at the first "x" number of rows (actual number is based on a
setting in a registry key -- default usually is 25, but can be as small
as
8) to see the data that are present, and then decides what the data are
for
the import. In your case, I'm guessing that the nonumeric values are in a
row farther down than the 25th row.

Can you put in the first row the "nonnumeric" value so that ACCESS sees
it
and knows that not all values are numbers.

Or can you go into EXCEL and insert a ' character in front of each of the
values in the column? That will tell ACCESS to treat the value as a
number?

You could try using an append query to read the data directly from the
EXCEL
worksheet (EXCEL file must be closed when you do this):

INSERT INTO DestinationTableName ( Field1, Field2, Field3 )
SELECT ColA AS Col1, ColB AS Col2, ColC AS Col3
FROM [Excel 11.0;Database=C:\MyFolder\MyWorkbook.xls;].[MySheet$]


If nothing else works, you can use Automation (via VBA code) to open the
EXCEL file and read the spreadsheet cell by cell and write the values
into a
recordset that is based on the target table.

--
Ken Snell
<MS ACCESS MVP>

Gord said:
I have a spreadsheet that contains a column of invoice numbers. A
sample
would be 21001320. Occassonally we apened a letter to the end of the
invoice
number, for example, 21001320A. I want to import that data into an
Access
table, but it fails because it the method assumes the colums is
numeric.
In
excel I specified the column as text, I've specified the field in the
table
is text, yet transferspreadsheet insists on converting it to a number.
Altenatively I've tried opening a linked table, same problem. I've
also
tried uising an ADO recordset connection - no good. The final thing I
tried
was to sort the column in excel (something I really don't want the user
doing) so that the invoice numbers with the suffix appeared first -
same
problem, excpet that it now only imports the items with a suffix while
ignoring the rest of the invoice number.

Does anybody have a solution???? How can I force a data type?
Click to expand...
Click to expand...
Click to expand...
 
Gord said:
I have a spreadsheet that contains a column of invoice numbers. A sample
would be 21001320. Occassonally we apened a letter to the end of the invoice
number, for example, 21001320A. I want to import that data into an Access
table, but it fails because it the method assumes the colums is
Click to expand...
numeric.

The following notes may help you:

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

In summary, try changing the registry setting TypeGuessRows to equal 0
(zero).

Jamie.

--
 
Back
Top