DTS data conversion

DTS data conversion

Post by Phil Jeffre » Sat, 26 Feb 2000 04:00:00



We have an Excel spreadsheet we are converting to a SQL table using DTS.
One column of the Excel spreadsheet has both Text and Numbers, however in
the SQL table we want the numbers to be converted to text.  The DTS package
defaults this column to nvarchar data type, which is OK for the text, but
the numbers are coming out as <Null>.  Does anyone know how to convert such
a column to all text values?

We would prefer not to change the cell format  in the Excel spreadsheet.

 
 
 

DTS data conversion

Post by Sue Hoegemeie » Wed, 01 Mar 2000 04:00:00


You can use an ActiveX transformation to convert the data
type to text.  Using VBScript, something like:
DTSDestination("YourTableField") =
 DTSSource(CStr("YourExcelField"))

--Sue

On Fri, 25 Feb 2000 15:43:04 +1100, "Phil Jeffrey"


>We have an Excel spreadsheet we are converting to a SQL table using DTS.
>One column of the Excel spreadsheet has both Text and Numbers, however in
>the SQL table we want the numbers to be converted to text.  The DTS package
>defaults this column to nvarchar data type, which is OK for the text, but
>the numbers are coming out as <Null>.  Does anyone know how to convert such
>a column to all text values?

>We would prefer not to change the cell format  in the Excel spreadsheet.


 
 
 

1. Problems with special characters / DTS / Data Conversion / SQL Server 7

I try to simply copy the contents of a SQL 7 database. If I could!

The target database is located on the same server. I use the DTS import
wizard (creating it by hand doesn't change anything). I chose the OLE DB
Driver for SQL Server, because it is easier to copy all the objects at
once, including Indexes and Constraints, than doing it by hand in SQL. I
have the newest MDAC Release installed from www.microsoft.com/data.
(but not yet SQL SP1, I found nothing about it in the fixlist)

The problem is: Characters with ASCII-Code >127 are converted in a wrong
way. A '?' for example is converted to a '"'. I tried to change this by
setting "Auto Translate" to 0 or 1, but it didn't do anything.

I know that conversion problems may occur if the two servers are installed
with different CodePages, but since source and target are on the same
server, this cannot be the problem. I also read that the codepage in the
Windows System Settings have to be the same, but they are.

I just don't know the answer. Maybe it's simply a bug, maybe someone can
help.

Greets

Clemens Marschner

--
Posted via CNET Help.com
http://www.help.com/

2. Viewpoint

3. DTS data conversion

4. STS for upgrade to Oracle8i

5. Data Conversion - DTS??? - Newbe

6. Interbase to Oracle

7. DTS Data Type Conversion Problem

8. What is needed for SQL server

9. Data Conversion From SQL Server to Excel File Using DTS

10. conversion of ODBC SQL data types to ODBC C data types

11. Disallowed implicit conversion from data type nvarchar to data type money

12. Data type conversion error when saving numeric data types

13. Data conversion problems handling DB2 Decimal data types