Leading Zeros are lost when importing spreadsheet data

[bstauffer]

I am importing data from an Excel spreadsheet into a .Net DataTable
using the following code:

// conn string
Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended
Properties="Excel 8.0"

OleDbConnection conn = new
OleDbConnection(String.Format(this.ExcelConnString, ofd.FileName));
OleDbCommand cmd = new OleDbCommand(this.SpreadsheetSelect, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable dtAHMOrders = new DataTable();
da.Fill(dtAHMOrders);

The data comes across fine except for zip codes with leading zeros in
which case the leading zeros are lost. I have tried every exampe I've
found with the following results:

When formatting the column as Special>ZipCode the data displays fine
in the spreadsheet but the leading zeros are stripped.

When prepending the zip code with an apostrophe the data displays fine
in the spreadsheet but the leading zeros are stripped.

What is completely frustrating me is that I have a different
spreadsheet which also has leading zeros on zip codes and they import
perfectly!!! When inspecting the Cell Format of this spreadsheet the
cells are formatted as simply General. When looking at all of the
cells show the green wedge in the upper left corner of the cell and
the error says Storing Number as Text. Uppon closer inspection of the
data I find no apostrophes, or spaces, or carrats, or ANYTHING! If I
open a blank spreadsheet and enter 00401 into a cell, which is
formatted as General by default, the leading zeros are lost as soon as
I leave the cell.

Can ANYONE shed some light on this? Thank you.

 

[Bob Flanagan]

Try pre-formatting the column for the zip codes as Text. That should hold
the leading zeros.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Pete_UK]

There may well have been a formula in the other sheets like
=TEXT(cell,format) which effectively turned the zip code into a text
value with leading zeros (but the cell could still have been formatted
as General), and then the values were fixed by means of Edit | Paste
Special.

You could do the same in your file - I'm sorry I can't suggest a
formula directly as I don't know the format of a zip code, but
hopefully someone else will be able to suggest this for you.

Hope this helps.

Pete

 

[bstauffer]

Try pre-formatting the column for the zip codes as Text. That should hold
the leading zeros.

Bob Flanagan
Macro Systemshttp://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel













- Show quoted text -

Is there a difference between "Pre format" and selecting the cells
once data is in them and applying a format? I'm not clear no this.
Thanks for all information so far!

 

[Gord Dibben]

Big difference.

Pre-format means the cells are formatted as Text prior to entering the numbers
with leading zeros.

If you format as Text after the fact, the zeros are already gone.

You could in that case Custom Format as 000000 or similar.

But that would be for appearance only, the actual value would be anumber with no
leading zeros.


Gord Dibben MS Excel MVP

 

[bstauffer]

Big difference.

Pre-format means the cells are formatted as Text prior to entering the numbers
with leading zeros.

If you format as Text after the fact, the zeros are already gone.

You could in that case Custom Format as 000000 or similar.

But that would be for appearance only, the actual value would be anumber with no
leading zeros.

Gord Dibben MS Excel MVP



- Show quoted text -

So, in order to Pre-format, you start with a blank spreadsheet? Or
can you clear a column of cells, then apply the format, then enter the
data?

Thanks again for all of this very useful information.

 

[Gord Dibben]

Just clear contents on a column of data then format that column as Text prior to
entering new data.

New worksheets would default to General so you would have to pre-format the
cells to Text also.


Gord

 

[bstauffer]

Just clear contents on a column of data then format that column as Text prior to
entering new data.

New worksheets would default to General so you would have to pre-format the
cells to Text also.

Gord




- Show quoted text -

Thank you everyone who responded! I finally understand and my project
is moving forward.