importing data from Excel

[Guest]

I am importing data from Excel and even if I change the datatype in Excel of
the data from General to Text- when I import it into an Access db it strips
the leading zeros off the data which I need. I have already tried importing
into an existing table where the field is already text. I am using MS Access
2003 and Excel 2003. Any suggestions?

I know if I import the data into Excel manually and choose it text that way
- it imports into Access with the leading zeros. However, I need to use an
Excel spreadsheet that is created automatically by a download process from
our application..and with this copy of the spreadsheet - even after changing
that field to text...it strips off the leading zeros upon import into access
table.

Thanks!

 

[Jeff Boyce]

The only way I'm familiar with for having leading zero's stripped is when
the data is numeric. It sounds like you've covered the Access side by
importing into a field defined as text.

The only other side I can imagine is the Excel side. Is there a chance that
the data that is displayed in Excel is actually numeric, but simply
formatted to appear with leading zeros?

Regards

Jeff Boyce
<Office/Access MVP>

 

[Ken Snell [MVP]]

A "bad" feature of ACCESS 2003 and earlier versions...

The problem is that Jet is seeing numbers in the first 25 or so rows of your
EXCEL spreadsheet, so it assigns a number data type to that field, and then,
if it finds a text value in the field in a later row, it errors and won't
import that value.

You can work around this by adding apostrophes to the beginning of the
number values in the EXCEL cells (which tells Jet that the numbers in fact
are text), or you can put a text value (such as what you posted) in the
first row in the EXCEL sheet.

 

[peregenem]

A "bad" feature of ACCESS 2003 and earlier versions...

This is one of those times when knowing the difference between Access
and Jet (which without doubt Ken does) is important. The issue is with
Jet; not Jet on the Access side but Jet on the Excel side.

The problem is that Jet is seeing numbers in the first 25 or so rows of your
EXCEL spreadsheet, so it assigns a number data type to that field, and then,
if it finds a text value in the field in a later row, it errors and won't
import that value.

The number of rows to scan is determined by a local machine registry
key (usually found to be 16). If you set this to zero, it forces all
rows to be scanned. Another registry key tells Jet (on the Excel side)
what to do in a 'mixed types' situation: the default (and most useful)
is to import as text, meaning NVARCHAR(255).

You can work around this by adding apostrophes to the beginning of the
number values in the EXCEL cells (which tells Jet that the numbers in fact
are text), or you can put a text value (such as what you posted) in the
first row in the EXCEL sheet.

Changing the data may not be desirable or possible, If the local
machine's settings can be changed, I'd recommend doing so to allow all
rows to be scanned. Details at:

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

 

[Guest]

Putting a text value into the first row in the EXCEL spreadsheet has worked
for what I needed to do. Much thanks to all who posted solutions!