DTS import Excel to SQL Server, keep leading zeroes

Hi,
I have a few DTS packages where I am importing data to SQL Server 2000
tables. There are a couple of fields in the Excel sheet where data has
leading zeroes (zip code fields). It seems that somewhere during the
DTS import, the leading zeroes are lost and the data is inserted to the
tables without those zeroes. I have the Excel sheet fields set up as
"Special" cell type and selected the "Zip Code" type (which keeps the
leading zeroes). Is there a way to keep those leading zeroes somehow
during the import?
I thought of saving the Excel file as .csv file, but would that make
any difference? I actually tried that already and it seems that the
leading zeroes are lost already in the Excel saving phase, when I
re-open the .csv file in Excel, the leading zeroes are gone.
Any tips are greatly appreciated.
Thanks,
Tuomas
Tuomas
How have you stored the data in Excel file?
I did some testing as 00013' data to be transfered into SQL Server table
(varchar(10) column). I works fine.
"Tuomas" <tuomaskesti@.hotmail.com> wrote in message
news:1121084389.185595.217280@.g44g2000cwa.googlegr oups.com...
> Hi,
> I have a few DTS packages where I am importing data to SQL Server 2000
> tables. There are a couple of fields in the Excel sheet where data has
> leading zeroes (zip code fields). It seems that somewhere during the
> DTS import, the leading zeroes are lost and the data is inserted to the
> tables without those zeroes. I have the Excel sheet fields set up as
> "Special" cell type and selected the "Zip Code" type (which keeps the
> leading zeroes). Is there a way to keep those leading zeroes somehow
> during the import?
> I thought of saving the Excel file as .csv file, but would that make
> any difference? I actually tried that already and it seems that the
> leading zeroes are lost already in the Excel saving phase, when I
> re-open the .csv file in Excel, the leading zeroes are gone.
> Any tips are greatly appreciated.
> Thanks,
> Tuomas
>
|||Hi,
The data in Excel is in "Special" cell type (if you right click a cell
and select "Format cells") and under "Special" there is a "Type" "Zip
code". Did you try that in your tests or how did you store the data in
your cell? If I just open a new Excel sheet and type 00013 in a cell
and then move away from that cell, the leading zeroes are gone right
away.
I did some more testing myself and had three different columns in an
Excel sheet, all where I stored the same zip code 00013. But in the
first column, I left the cell type ("General") as it is when I created
the new sheet. For the second column, I changed the cell type to
"Special -> zip code" as I mention above. And for the third column, I
left the cell type ("General") as it was when I created the new sheet
but I put a quotation mark before the data as in: '00013. I generated a
new DTS package and ran it. The import went through without any
problems. In the destination table I have three columns, all the same
data type (varchar(10)). For the first two columns, the leading zeroes
disappeared, but for the third, where the quotation mark is before the
data in the cell, the leading zeroes were in the SQL Server table after
the import. So, it seems that the "Special -> zip code" does not mean
anything in terms of keeping the zeroes during the import even though
the zeroes are shown fine in Excel. I tried also the "Custom" cell type
in Excel and forced the leading zeroe(s) for the zip codes where zero
was the first number so that the zip code would always be a five digit
number. But in this case also, the zeroes disappeared during the
import.
Now, if this is the case that the leading zeroes are only kept if the
data in Excel is stored with the quotation mark before the actual data,
I guess I need to do an Excel function to put the quotation mark before
all the zip code data or something like that...
If you have any further info, please let me know.
Thanks,
Tuomas