[dba-SQLServer] Excel zip imports

Hi John:

When moving data into the MS SQL application I use the DTS module to import
the data into a raw table and then convert the data into the finished table.

If you are using a single table with 2 fields then this would be a simple
answer.

Update MyTable
Set myCharacterField = right('0000000000'+cast(MyNumericField as
varchar(10)),10)

The above example will take a field with a numeric value of 1234 and deposit
0000001234 in a character field. Just use CAST to set data type and RIGHT to
pad results.

HTH
Jim 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Saturday, January 13, 2007 6:02 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Excel zip imports

Update - in the end it was easier (for me) to just go back to the
spreadsheet, format the columns in question correctly (there is a "zip
special format for example) and then export it to a CSV file, then import
the CSV file.  Worked without a hitch and I had it done within about 20
minutes for two Excel files.

I'd still like to find an answer within SQL Server though.  It seems
something like the format(data,"format info") should be available but I
cannot find anything like it.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Saturday, January 13, 2007 1:07 AM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Excel zip imports

I imported a zip database into SQL Server from Excel.  The data out in Excel
is numeric (float according to the imported table).  As a result the leading
zeros were stripped off.  Two questions, is it possible to format fields
such as this with leading zeros as you import them (and if so how), and now
that I have it in the database, how can I get the leading zeros back.  I
 
John W. Colby
Colby Consulting
www.ColbyConsulting.com
 
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com