Copy the contents failure of 9 digit zip codes

#31

I modified the template as you stated. However, no matter what paste option
I use, that internal data type is not maintained when you create a new
workbook based on the template.

Therefore, you have to repeat these steps on the target spreadsheet each
time. Our work around is to Find/Replace the hyphens and Minus Signs with
nothing, execute the DTS page, and let the database reformat the zip codes.

WR

"Ed Enstrom" wrote:

Quote:

Formatting a column is for display. It does not change Excel's internal data type.

In the workbook, select the column, then go to Data - Text to Columns

In the wizard, define the data in the row as Text.

WhiskeyRomeo wrote:
I have a DTS package that copies contents from an Excel spreadsheet. In the
spreadsheet all columns are formatted as "text." One column consists of Zip
codes. They are a mixture of 5 and 9 digit zip codes. The 9 digit zip code
has the hyphen in it (12345-6789). The zip code is copied to a table in a
SQL Server 2000 database and the particular column is defined as varchar(15).

The column in the database ends up being null for 9 digit zip codes. If we
remove the hyphen for 9 digit zip codes in the source Excel file, the 9
digits copy just fine.

This is occurring in Excel in Office 2003 as well as Office 2007.

Can anyone help me with this problem?

Bill

#32

I modified the template as you stated. However, no matter what paste option
I use, that internal data type is not maintained when you create a new
workbook based on the template.

Therefore, you have to repeat these steps on the target spreadsheet each
time. Our work around is to Find/Replace the hyphens and Minus Signs with
nothing, execute the DTS page, and let the database reformat the zip codes.

WR

"Ed Enstrom" wrote:

Quote:

Formatting a column is for display. It does not change Excel's internal data type.

In the workbook, select the column, then go to Data - Text to Columns

In the wizard, define the data in the row as Text.

WhiskeyRomeo wrote:
I have a DTS package that copies contents from an Excel spreadsheet. In the
spreadsheet all columns are formatted as "text." One column consists of Zip
codes. They are a mixture of 5 and 9 digit zip codes. The 9 digit zip code
has the hyphen in it (12345-6789). The zip code is copied to a table in a
SQL Server 2000 database and the particular column is defined as varchar(15).

The column in the database ends up being null for 9 digit zip codes. If we
remove the hyphen for 9 digit zip codes in the source Excel file, the 9
digits copy just fine.

This is occurring in Excel in Office 2003 as well as Office 2007.

Can anyone help me with this problem?

Bill

#33

I modified the template as you stated. However, no matter what paste option
I use, that internal data type is not maintained when you create a new
workbook based on the template.

Therefore, you have to repeat these steps on the target spreadsheet each
time. Our work around is to Find/Replace the hyphens and Minus Signs with
nothing, execute the DTS page, and let the database reformat the zip codes.

WR

"Ed Enstrom" wrote:

Quote:

Formatting a column is for display. It does not change Excel's internal data type.

In the workbook, select the column, then go to Data - Text to Columns

In the wizard, define the data in the row as Text.

WhiskeyRomeo wrote:
I have a DTS package that copies contents from an Excel spreadsheet. In the
spreadsheet all columns are formatted as "text." One column consists of Zip
codes. They are a mixture of 5 and 9 digit zip codes. The 9 digit zip code
has the hyphen in it (12345-6789). The zip code is copied to a table in a
SQL Server 2000 database and the particular column is defined as varchar(15).

The column in the database ends up being null for 9 digit zip codes. If we
remove the hyphen for 9 digit zip codes in the source Excel file, the 9
digits copy just fine.

This is occurring in Excel in Office 2003 as well as Office 2007.

Can anyone help me with this problem?

Bill

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Copy the contents failure of 9 digit zip codes

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Re: Copy the contents failure of 9 digit zip codes - 05-22-2008 , 09:40 AM

Re: Copy the contents failure of 9 digit zip codes - 05-22-2008 , 09:40 AM

Re: Copy the contents failure of 9 digit zip codes - 05-22-2008 , 09:40 AM