Importing From Excel - Access Create Wrong Data Type

  • Thread starter Thread starter Alan B. Densky
  • Start date Start date
A

Alan B. Densky

In Excel, any cell can contain any data, and the data may or may not be
formatted in a particular way. In Access, by contrast, every field has a
fixed data type and can only contain a value of that type ( double,
text, etc.). When Access imports from Excel to a new table, it guesses
the field types to use by examining the data in the first few rows of
the Excel table - and often gets it wrong. This can cause Access to bomb
out during the import.

The "Fix" for this - according to another post, is to massage the data in
the import. That is riciculous.

Does anyone have a way to fix this problem the right way?

Alan
 
Alan said:
In Excel, any cell can contain any data, and the data may or may not
be formatted in a particular way. In Access, by contrast, every field
has a fixed data type and can only contain a value of that type (
double, text, etc.). When Access imports from Excel to a new table,
it guesses the field types to use by examining the data in the first
few rows of the Excel table - and often gets it wrong. This can
cause Access to bomb out during the import.

The "Fix" for this - according to another post, is to massage the
data in the import. That is riciculous.

Does anyone have a way to fix this problem the right way?
Click to expand...

Whenever I am importing from a source that does not have explicit DataTypes I
always first import into a table having all text fields. Then I can run an
append query from that table into the final one using the appropriate formulas
to convert the data. At least then you are in total control and can examine the
rows that have problems to see what is going on.
 
Hi Rick,

All of the fields in my Access DB are already text fields.

My problem is that Access, in it's infinate wisdom, is assigning data types
to the columns in the linked spreadsheet based on the data in the first few
records. So, for instance, if I have a zip code column in the Excel spread
sheet, and the first several records don't have a hypnen and zip + 4, and
several rows later some of the zips have a hyphen 33063-1234, the import
bombs because Access has assigned the data type of Number to the link on
that column. And what you see in the link is #num.

In addition, if the zip code has a leading zero (01234), because it's a
number column, it strips out the leading zero. I have similar problems on
several other fields.

So I need some way to tell Access to force all of the columns in the link to
be Text data type.

Alan
 
Alan said:
Hi Rick,

All of the fields in my Access DB are already text fields.

My problem is that Access, in it's infinate wisdom, is assigning data
types to the columns in the linked spreadsheet based on the data in
the first few records. So, for instance, if I have a zip code column
in the Excel spread sheet, and the first several records don't have a
hypnen and zip + 4, and several rows later some of the zips have a
hyphen 33063-1234, the import bombs because Access has assigned the
data type of Number to the link on that column. And what you see in
the link is #num.
In addition, if the zip code has a leading zero (01234), because it's
a number column, it strips out the leading zero. I have similar
problems on several other fields.

So I need some way to tell Access to force all of the columns in the
link to be Text data type.
Click to expand...

I never link to Excel. Only import. For exactly this reason.
 
Hi John,

Thanks for the effort. I had the link to www.disks-blog and went through
that information a while back. It didn't help because it just didn't make
any difference when I edited the registry.

As far a Acess 2007 - I have no interest in spending the money, upgrading,
or going through the learning curve.

My database is based on DAO, so the ADO isn't going to help me, and I don't
want to go through the learning curve. I quit programming for money a couple
of years ago, and now only program for my own business and a few old
customers. I don't have the time, energy, or desire to play the Microsoft
"learn to do it our way, and we'll make make your learning investment
obsolete whenever we feel like it" game any longer. Especially since so many
people are sending their work to Rent-A-Coder people for $5.00 a day.

Is there any other way around this issue? Maybe I'm just going to have to
break down and do an import of a CSV file in a temp table?? It sounds like
my only work-around that I don't have to go through a learning curve to
accomplish. Unless you know of some other way to trick Access to look at all
of my linked columns for this particular table as Text columns.

Thanks again,

Alan
 
Have you tried using IMEX=1 (which seems to work equally in DAO and
ADO)? E.g.

SELECT * FROM
[Excel 8.0;HDR=Yes;IMEX=1;database=C:\folder\file.xls;].[sheet1$];

Hi John,

Thanks for the effort. I had the link to www.disks-blog and went through
that information a while back. It didn't help because it just didn't make
any difference when I edited the registry.

As far a Acess 2007 - I have no interest in spending the money, upgrading,
or going through the learning curve.

My database is based on DAO, so the ADO isn't going to help me, and I don't
want to go through the learning curve. I quit programming for money a couple
of years ago, and now only program for my own business and a few old
customers. I don't have the time, energy, or desire to play the Microsoft
"learn to do it our way, and we'll make make your learning investment
obsolete whenever we feel like it" game any longer. Especially since so many
people are sending their work to Rent-A-Coder people for $5.00 a day.

Is there any other way around this issue? Maybe I'm just going to have to
break down and do an import of a CSV file in a temp table?? It sounds like
my only work-around that I don't have to go through a learning curve to
accomplish. Unless you know of some other way to trick Access to look at all
of my linked columns for this particular table as Text columns.

Thanks again,

Alan
Click to expand...
 
Hi John,

How would I go about using that? Currently I'm using TransferSpreadsheet to
create my link to my spreadsheet.

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "tblLeads",
YesDB, False

Alan

John Nurick said:
Have you tried using IMEX=1 (which seems to work equally in DAO and
ADO)? E.g.

SELECT * FROM
[Excel 8.0;HDR=Yes;IMEX=1;database=C:\folder\file.xls;].[sheet1$];
Click to expand...
 
Just create a new query, switch to SQL View, and paste the SELECT...
statement into the window. Then modify it to suit your filename and
sheet name so it delivers the data you need, and save it. After that you
can use it pretty much as if it were a linked table.

Hi John,

How would I go about using that? Currently I'm using TransferSpreadsheet to
create my link to my spreadsheet.

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "tblLeads",
YesDB, False

Alan

John Nurick said:
Have you tried using IMEX=1 (which seems to work equally in DAO and
ADO)? E.g.

SELECT * FROM
[Excel 8.0;HDR=Yes;IMEX=1;database=C:\folder\file.xls;].[sheet1$];

Hi John,

Thanks for the effort. I had the link to www.disks-blog and went through
that information a while back. It didn't help because it just didn't make
any difference when I edited the registry.

As far a Acess 2007 - I have no interest in spending the money, upgrading,
or going through the learning curve.

My database is based on DAO, so the ADO isn't going to help me, and I
don't
want to go through the learning curve. I quit programming for money a
couple
of years ago, and now only program for my own business and a few old
customers. I don't have the time, energy, or desire to play the Microsoft
"learn to do it our way, and we'll make make your learning investment
obsolete whenever we feel like it" game any longer. Especially since so
many
people are sending their work to Rent-A-Coder people for $5.00 a day.

Is there any other way around this issue? Maybe I'm just going to have to
break down and do an import of a CSV file in a temp table?? It sounds like
my only work-around that I don't have to go through a learning curve to
accomplish. Unless you know of some other way to trick Access to look at
all
of my linked columns for this particular table as Text columns.

Thanks again,

Alan

Hi Alan,

See
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
and http://support.microsoft.com/?id=257819
and
http://msdn.microsoft.com/library/d...ns_programmatically_for_the_access_driver.asp
and http://office.microsoft.com/en-us/access/hp010950951033.aspx


Also, consider upgrading to Access 2007, which offers more direct
control.


On Thu, 8 Mar 2007 20:27:31 -0500, "Alan B. Densky"

In Excel, any cell can contain any data, and the data may or may not be
formatted in a particular way. In Access, by contrast, every field has a
fixed data type and can only contain a value of that type ( double,
text, etc.). When Access imports from Excel to a new table, it guesses
the field types to use by examining the data in the first few rows of
the Excel table - and often gets it wrong. This can cause Access to
bomb
out during the import.

The "Fix" for this - according to another post, is to massage the data
in
the import. That is riciculous.

Does anyone have a way to fix this problem the right way?

Alan
Click to expand...
Click to expand...
Click to expand...
 
Thanks John.
John Nurick said:
Just create a new query, switch to SQL View, and paste the SELECT...
statement into the window. Then modify it to suit your filename and
sheet name so it delivers the data you need, and save it. After that you
can use it pretty much as if it were a linked table.

Hi John,

How would I go about using that? Currently I'm using TransferSpreadsheet
to
create my link to my spreadsheet.

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, "tblLeads",
YesDB, False

Alan

John Nurick said:
Have you tried using IMEX=1 (which seems to work equally in DAO and
ADO)? E.g.

SELECT * FROM
[Excel 8.0;HDR=Yes;IMEX=1;database=C:\folder\file.xls;].[sheet1$];

On Wed, 14 Mar 2007 21:07:24 -0400, "Alan B. Densky"

Hi John,

Thanks for the effort. I had the link to www.disks-blog and went through
that information a while back. It didn't help because it just didn't
make
any difference when I edited the registry.

As far a Acess 2007 - I have no interest in spending the money,
upgrading,
or going through the learning curve.

My database is based on DAO, so the ADO isn't going to help me, and I
don't
want to go through the learning curve. I quit programming for money a
couple
of years ago, and now only program for my own business and a few old
customers. I don't have the time, energy, or desire to play the
Microsoft
"learn to do it our way, and we'll make make your learning investment
obsolete whenever we feel like it" game any longer. Especially since so
many
people are sending their work to Rent-A-Coder people for $5.00 a day.

Is there any other way around this issue? Maybe I'm just going to have
to
break down and do an import of a CSV file in a temp table?? It sounds
like
my only work-around that I don't have to go through a learning curve to
accomplish. Unless you know of some other way to trick Access to look at
all
of my linked columns for this particular table as Text columns.

Thanks again,

Alan

Hi Alan,

See
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
and http://support.microsoft.com/?id=257819
and
http://msdn.microsoft.com/library/d...ns_programmatically_for_the_access_driver.asp
and http://office.microsoft.com/en-us/access/hp010950951033.aspx


Also, consider upgrading to Access 2007, which offers more direct
control.


On Thu, 8 Mar 2007 20:27:31 -0500, "Alan B. Densky"

In Excel, any cell can contain any data, and the data may or may not
be
formatted in a particular way. In Access, by contrast, every field has
a
fixed data type and can only contain a value of that type ( double,
text, etc.). When Access imports from Excel to a new table, it guesses
the field types to use by examining the data in the first few rows of
the Excel table - and often gets it wrong. This can cause Access to
bomb
out during the import.

The "Fix" for this - according to another post, is to massage the data
in
the import. That is riciculous.

Does anyone have a way to fix this problem the right way?

Alan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top