December 6, 2007 at 3:05 am
Hi,
I have a Excel Data File with column [ Stdcode, BoardLine1]
which contains Numeric data.
when I tried to copy to Table [TempDealer.Stdcode, TempDealer.BoardLine1] SQL 2005 Database, it is creating the column
with float Datatype
Ex:
=====
Excel Data : 222333444
DB Data : 222333444
But I wants to transfer TempDealer float data to original table "MainTable" Column "ContactNumber" which is NVarchar(MAX) Datatype.
Equation should be :
MainTable.ContactNumber = [TempDealer.Stdcode] + '-' + [TempDealer.BoardLine1]
Ex :
====
Stdcode = 223, BoardLine1 = 222333444
ContactNumber = 223-222333444 As varchar data.
SN: I have used Cast Function But no Result.
Here I am facing problem. small Numberic data (Stdcode : 223) converting to Nvarchar format but small Numberic data (BoardLine1 :222333444) data is converting like "222333e+002" format.
And I am working in online Database, Please help me how to do this??
Cheers!
Sandy.
--
December 6, 2007 at 12:44 pm
Sandy,
I was actually able to get this to work in SQL2005 as follows ;
Created a spreadsheet with data like yours, with 2 columns, StdCode and BoardLine1 (field names in 1rst row). I also used your values(223,222333444), plus . The values right justified themselves in the spreadsheet, so Excel thinks they are numeric. My Excel Data looks like this ;
StdCodeBoardLine1
223222333444
3343334455
55677788899934
5551234567890
7775.6789E+11
OK, so I then ran the following query ;
SELECT cast(StdCode as Bigint),cast(BoardLine1 as Bigint)
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\James\SSIS_Stuff\Scratch\test1.xls;Extended Properties=Excel 8.0;')...Sheet1$
The result set was ;
223222333444
3343334455
55677788899934
5551234567890
777567890123456
To make these into varchars, I let SQL2005 cast them internally. Here is the whole script ;
create table #temp(
StdCode varchar(32) not null,
BoardLine1 varchar(max) null
)
insert into #temp
SELECT cast(StdCode as Bigint),cast(BoardLine1 as Bigint)
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\James\SSIS_Stuff\Scratch\test1.xls;Extended Properties=Excel 8.0;')...Sheet1$
If I run the query ;
select StdCode+'-'+BoardLine1 from #temp
I get ;
223-222333444
334-3334455
556-77788899934
555-1234567890
777-567890123456
I think this is what you want. The explicit cast to bigint, plus the implicit cast for the insert into #temp seems to have worked.
JMuldoon
Sandy (12/6/2007)
Hi,I have a Excel Data File with column [ Stdcode, BoardLine1]
which contains Numeric data.
when I tried to copy to Table [TempDealer.Stdcode, TempDealer.BoardLine1] SQL 2005 Database, it is creating the column
with float Datatype
Ex:
=====
Excel Data : 222333444
DB Data : 222333444
But I wants to transfer TempDealer float data to original table "MainTable" Column "ContactNumber" which is NVarchar(MAX) Datatype.
Equation should be :
MainTable.ContactNumber = [TempDealer.Stdcode] + '-' + [TempDealer.BoardLine1]
Ex :
====
Stdcode = 223, BoardLine1 = 222333444
ContactNumber = 223-222333444 As varchar data.
SN: I have used Cast Function But no Result.
Here I am facing problem. small Numberic data (Stdcode : 223) converting to Nvarchar format but small Numberic data (BoardLine1 :222333444) data is converting like "222333e+002" format.
And I am working in online Database, Please help me how to do this??
Cheers!
Sandy.
December 6, 2007 at 10:45 pm
Hey jmuldoon,
Thanks for Nice Help.
I used following Query to solve this ..
SQL Query
===========
DECLARE @CntDell INT, @Count Int, @Result NVARCHAR(MAX)
SELECT @CntDell = Count(*) from TmpDell
select @CntDell
Select @Count = 1
WHILE @Count <= @CntDell
BEGIN
DECLARE @VAR INT, @VAR1 INT, @VAR2 NVARCHAR(MAX),@VAR3 NVARCHAR(MAX)
SELECT @VAR = CAST(STD AS INT), @VAR1 = CAST(Phone AS INT)
FROM TmpDell
WHERE RID = @Count and Phone Is Not NULL
SELECT @VAR2 = CAST(@VAR AS NVARCHAR(MAX))
SELECT @VAR3 = CAST(@VAR1 AS NVARCHAR(MAX))
SET @Result = @VAR2 + '-' + @VAR3
UPDATE TmpDell
Set Cnum = @Result
WHERE RID = @Count and Phone Is Not NULL
SET @Count = @Count + 1
END
And Its Working Fine,
Thanks for same again..(Quick Reply too)
This is the reason I Like "SQL Server Central Forum" Site.
"Simply, Best for SQL Server"
Cheers!
Sandy.
--
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply