DTS Vs Excel Numeric Conversion
Jul 20, 2005
I am having a problem importing an Excel spreadsheet. I have a column in
an Excel sheet with alphanumeric text and some of the cells are numeric.
Some of the cells contain numbers like 12345.6 and when DTS is done
importing it into a field that is nvarchar the results are
"12345.600000000001". I have tried:
1. Changing the format of the Excel column to text
2. Using the formula =text(a1,0) which only truncates the .6
3. Using the formula =t(a1) which will remove some numeric representations
4. Exporting the sheet to CSV or TXT first which will not enclose the cell
contents with ""
5. Beating the computer with a nine iron
None of these options work. Any idea anyone?
Don VonderBurg
View 3 Replies
ADVERTISEMENT
Dec 13, 2007
Hi guys, how do i convert a decimal to numeric data type?
View 3 Replies
View Related
Jun 20, 2007
Hi ,
I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).
I tried all the ways i can and still i get the same error..:
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E07. An OLE
DB record is available. Source: "Microsoft OLE DB Provider
for SQL Server" Hresult: 0x80040E07 Description: "Error
converting data type nvarchar to numeric.".
Can we actually do it...?? any help would be appreciated.
thanks
ravi
Nothing much that i can do..!!
View 2 Replies
View Related
Nov 22, 2007
Hello All,
I am querying from a table which has all varchar values. I want to display the result in graph and hence would need the varchar to be converted to Decimal. But I am getting the error : Error converting data type varchar to Numeric. Here is the SELECT code: Can you please help me do this? I'll post the script in the next post in 10 minutes....
View 3 Replies
View Related
Jun 3, 1999
Hi,
Does anybody know how to get the next row using two numeic keys like this:
select * from cfg_trkgrp_t where (convert(varchar,column1 )+(convert(varchar,column2)) > (convert(varchar,0) + convert(varchar,1000 )))
note: colum1 and column2 are both numeric type, column 1 has value 0, column2
has value 1000
Seems like it does not work for me for different digits of values.
Thanks.
View 1 Replies
View Related
Jan 31, 2007
I have some data which I am trying to put into a DM where I can use it as part of a cube (my first!!)
I have hit a small problem with dates, I get it from the ERP system as a numeric field, and I need to convert it to a date format. The intension is to use this converted data with Named Calculations to derive Year, month Day ect.
However I cannot seem to be able to convert and store (in SQL) this column can anyone advise
Thanks
View 14 Replies
View Related
Oct 2, 2007
I'm getting some data from a flat file with a SSIS Package, it comes a integer but I would like to converted to a decimal with a 3 scale.
Example:
Flat File: 2070015000950011800
In the data conversion I had it with a 3 scale, but what I got was this:20700.00015000.0009500.00011800.000But what I want is something like this:20.70015.0009.50011.800
I dont know if you guys get the idea. But I will apreciate if anyone can help me.
Thanks,
Erick
View 2 Replies
View Related
Jul 12, 2000
I would like to know how to translate a numeric value = 3 to a text or char value = 03.
Any input would be appreciated.
Thanks,
Joel
View 1 Replies
View Related
Oct 26, 2006
Hi All,
i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.
the problem is when this numeric field is > 0 it looses the precision on its decimal value.
example, if numeric value is 0.32
after converting this to a string, the new value will be : .32
it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.
when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.
can any one help on this?
View 7 Replies
View Related
Sep 18, 2007
Hi guys/ladies I'm still having some trouble formatting a select statement correctly.
I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database.
Here is my select statement cut down to the most basic elements.
SELECT commentFROM informix.ipr_stucomWHERE (comment > 70)
The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error
"Character to numeric conversion error"
How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values?
I have tried case with cast and isnumeric... I don't think that I have the formating correct.
I have also used:
WHERE (NOT (comment = ' I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = ' P' OR comment = ' F'))
This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.
View 2 Replies
View Related
Sep 6, 2007
Hi i am working on sending data from a dat file to table in sql server Database and i am using the Data conversion transformation in ssis to convert string of fixed length into numeric (11,5) which is the datatype for the price field in the table and its returning an error saying that status vale 6 and error text as Conersion Failed sue to overflow of specific type ... Can anyone let me know how to overcome this error.
View 6 Replies
View Related
Jul 20, 2006
I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
Thanks,
Manisha
View 5 Replies
View Related
Sep 28, 2007
Hello,
I have a table in SQL Server 2005 that has an NVarchar(50) column called AcctCode. I am trying to populate this column from an Excel source. I am doing this from the Import/Export Wizrds in Management Studio. When I attempt the import from the Excel file, all the the AcctCode data that is alpha numeric or just alpha will import, but the all numeric data comes in as NULL.
If I make AcctCode NOT NULL, I get an integrity contraint error from the wizard. The rest of the records import. I've tried to make the column in the Excel file type 'text', and have tried it with no formatting on this column.
Why won't the all numeric Acctcodes import?
Thank you for your help!
cdun2
View 3 Replies
View Related
Sep 4, 2015
I am developing a SSIS package with VS2013 to send data from SQL Server 2014 to an Excel Destination. But in the SSIS package, from the excel destination advanced editor, when I set the format of the excel destination external columns to double precision float DT_R8, it is returned to DT_WSTR automatically.Due to that, data sent to Excel are not processed as numeric but as text and formatted as such. I need the column to be created as numeric.
View 7 Replies
View Related
Dec 13, 2007
Hi All,
Xl file containts column A, B, C and D
A B C D
1 L1 100 200
1A L2 250 350
1B L3 150 887
2 L4 100 200
2A L5 250 350
3 L6 100 200
3A L7 250 350
3B L8 150 887
XL Source i have write a query Select f1,f2,f3,f4 from [sheet1$a18]
In preview i am getting the following records :
A B C D
Null L1 100 200
1A L2 250 350
1B L3 150 887
Null L4 100 200
2A L5 250 350
Null L6 100 200
3A L7 250 350
3B L8 150 887
The issue is here why the XL source returning Null value against the numeric value pls. somebody help me to sort out this issue.
Thanks in advance,
Syed
View 1 Replies
View Related
Nov 3, 2006
Hi,
I have found a problem with my SSIS package. The package takes a Excel File using the Excel Source/Excel connection manager objects. There's a Data Conversion Object which sets all the fields to be DT_STR and then I import the data into a Holding table using a OLEDB Destination Object.
My Excel file contains a list of Code/Description pairs. The problem I have is that I can have data such as the following
CODE
A101
A102
A103
12
1.2i
What seems to be happening is that when the data is imported into the holding table and the first rows are Alphanumeric then the fields where there's a code such as 12 get converted to NULL for some reason. If all the codes are the same type (either Alphanumeric or numericit doesn't matter, as long as they are all the same) the import works perfectly well.
I need to be able to import all of these codes without SSIS converting some to NULL. Has anyone come across this problem before? If so, is there something I am doing wrong or a workaround?
Many thanks in advance
Rob
View 3 Replies
View Related
Oct 26, 2006
i've been reading some problems with excel source data being force as
numeric type when there are string/numeric type in the data, but adding
IMEX=1 to the extended properties will fix this problem...this is true
but not in my case...
say my excel file have about 40 rows..if row 1-39 in column A are all
NULL and row 40 has a string in it, the string in row 40 will not be
converted and the excel source is forcing this column A data type to be
numeric..having IMEX=1 in there does not work..however..if i add a
string anywhere in row 1-8 in column A, the the string in row 40 will
be converted because the external data type now is a string..
anyone know how to solve this problem?
View 3 Replies
View Related
Mar 4, 2008
File Structure:
--**************************************************************************************************************************
USE [Test]
GO
/****** Object: Table [dbo].[tbl_Test] Script Date: 03/04/2008 09:51:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Test](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[TestValue] [numeric](18, 2) NOT NULL CONSTRAINT [DF_tbl_Test_TestValue] DEFAULT ((0)),
CONSTRAINT [PK_tbl_Test] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--**************************************************************************************************************************
Stored Procedure:
--**************************************************************************************************************************
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[proc_Ins_Test] Script Date: 03/04/2008 09:52:23 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Name: proc_Ins_Test
*/
ALTER PROCEDURE [dbo].[proc_Ins_Test]
(
@TestValue numeric(18,2)=0
)
AS
BEGIN TRAN
BEGIN
INSERT INTO tbl_Test (TestValue) VALUES (@TestValue)
END
COMMIT TRAN
SELECT MAX(TestID) AS MaxValue FROM tbl_Test
RETURN 1
--**************************************************************************************************************************
(I have it returning a value through SELECT to simulate returning a key value from the newly inserted record. I also use the RETURN value to fetch any error codes.)
'***************************************************************************************************************************
Public Sub PSInsertTest(cnConn As ADODB.Connection)
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim stProcName As String 'Stored Procedure name
Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command
'Defines the stored procedure commands
stProcName = "dbo.proc_Ins_Test" 'Define name of Stored Procedure to execute.
cmd.CommandType = adCmdStoredProc 'Define the ADODB command
cmd.ActiveConnection = cnConn 'Set the command connection string
cmd.CommandText = stProcName 'Define Stored Procedure to run
'Append Parameters
With cmd
.Parameters.Append .CreateParameter("@return_value", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@TestValue", adNumeric, adParamInput)
.Parameters.Item("@TestValue").NumericScale = 2
.Parameters.Item("@TestValue").Precision = 18
.Parameters.Item("@TestValue").Value = 123.45
End With
'Execute stored procedure and return to a recordset
Set rst = cmd.Execute
If Not rst.EOF Then
rst.MoveFirst
MsgBox (rst.Fields("MaxValue").Value)
Else
MsgBox ("No Value")
End If
'Close database connection and clean up
If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
End Sub
'***************************************************************************************************************************
It goes ahead and inserts the record, but gets an error when I try to retrieve the "SELECT"ed value. I get the same results with the following statement:
rst.Open cmd, , adOpenStatic, adLockReadOnly
The error is: Operation is not allowed when the object is closed
If I use the following command, I get the error below:
rst.Open cmd.Execute
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Any insights would be helpful
Bob
View 19 Replies
View Related
Apr 24, 2008
Good afternoon,
I have an issue with an ssis variable datatype.
The scenario is as follows:
I have a stored procedure:
PROCEDURE [dbo].[sp_newTransaction]
@sourceSystem varchar(50),
@txOut NUMERIC(18,0) OUTPUT
AS
insert into scn_transaction (sourceSystemName) values(@sourceSystem);
SELECT @txOut = @@identity
Whose purpose is to perform an insert into a table and return me the identity value of the inserted record, which I'll then use throughout the rest of my package. The identity column in the inserted table is numeric(18,0).
I execute the stored proc with the following sql with an OLE DB connection manager:
exec sp_newTransaction ?, ?
The first parameter is a string variable from earlier in the package, and the second is the output parameter. I have the following parameter mappings to the execute sql task:
User:ystxId output numeric 1 -1
User:ourceSys input varchar 0 -1
The proc is correctly called, and the row insesrted, however I get a type conversion error when SSIS attempts to map the return parameter to my package variable... I've tried all sorts of combonations, and can't seem to get it to execute.
At one point I wasn't returning a numeric, but rather an int from the stored proc, and all was well until I went to use the variable in a derived column later in the package, and the type was converted quite incorrectly (a 1 was 77799789080 or some such), indicating a type conversion error likely related to the encoding of the number.
I'd like to keep the datatypes as numeric and make ssis use those - any pointers are greatly appreciated as to what type my package variable should be to allow proper assignment of a sql server numeric type to it.
Thanks much,
B
View 6 Replies
View Related
May 8, 2009
I'm using SSIS 2005 Enterprise edition, I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server). When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.
The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it. URL... but it only works when the first row of data has a number beginning with zero on this column. How to get the data imported correctly?
View 15 Replies
View Related
Dec 3, 2007
Hi i've 2 questions.
Q1 . I'd like to know if i have a data conversion before i export my data out to excel will it hold the data types in excel.
Q2. If i sort the data before i export my data out to excel will it hold the sort
View 1 Replies
View Related
Feb 5, 2008
Hopefully this is an easy one.
But in both the importexport utility and from SSIS. "#" become "." (aka pound to period) in column headers. So for example [# of errors] becomes [. of errors]. Now I truly do not ever remember having this issue before but then again I have don't remember having to construct field names with a various assortment of characters that are randomly strewn about as though someone discovered the symbol picker recently and had decided to employ it to make the a spreadsheet a more interesting place. Hence after a good day of time wasted. (as I considered doing an alter table after the sheets were put out.. and then thought better of it.) I seek guidence here. Thanks in advance.
View 2 Replies
View Related
Aug 31, 2004
I have to import data from Excel file to an SQL Server Database.
One of the Excel Worksheet columns it's number (with max value of 4550204008914630000), I will import the column to a char 21 database field. Using a DTS to do the work, when I import that column it will convert the data in something like 4.5502041E+18.
Can you give me some help for the DTS.
Thanks,
Paulo
View 3 Replies
View Related
Feb 8, 2006
In my SSIS project I am populating an Excel spreadsheet with several worksheets. When I define a worksheet in Excel Destination Editor, I use Create Table SQL statement to create a worksheet. Most of the fields are defined as numbers, however when job runs my numeric fields appear as text in Excel with yellow warning sign - 'The number in this cell is formatted as text or preceeded by an apostrophe'. I need my numbers to appear as numbers. Is there a workaround for this?
View 3 Replies
View Related
Jan 9, 2007
I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.
I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.
But its gives the following error.
[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".
[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.
Can someone please tell me what should I do to make it work?
Thanks,
View 22 Replies
View Related
Aug 28, 2006
Recently installed Sql Server 2005 client and am now attempting to import data from a spreadsheet into an existing table. This works fine with Sql Server 2000 but I am getting data conversion truncation errors that stop the process when this runs using import utility in Sql Server 2005.
Any help would be appreciated.
View 1 Replies
View Related
Jun 10, 2014
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
View 2 Replies
View Related
Mar 21, 2006
Guys
I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
Any ideas ??
View 6 Replies
View Related
Oct 24, 2007
Hi,
I have one column in which i have Alpha-numeric data like
COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.
I want to remove Non-numeric characters from above (space,'(',')',+,........)
i want to write something generic (suppose some function to which i pass the column)
thanks in advance,
Mandip
View 18 Replies
View Related
Jul 20, 2006
I need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
View 1 Replies
View Related
Aug 18, 2006
Hi,
I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.
Wildcard
Meaning
%
Any string of zero or more characters.
_
Any single character.
[ ]
Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).
Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.
In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:
WHERE
Gift_Date NOT LIKE "####*"
After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:
WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'
using single quotes and the % wildcard instead of Access' double quotes and * wildcard.
Just putting this out there for anybody else that is new to SQL, like me.
Regards,
Patrick Briggs,
Pasadena, CA
View 1 Replies
View Related
Jul 25, 2015
Trying to upload excel in server where excel is not installed. BIDs was there in the server, when i am trying to craete Excel source I am not able.what the workround for this.. How to upload excel without excel installed on the server.
View 4 Replies
View Related
Nov 19, 2007
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
Thanks,
Manisha
View 4 Replies
View Related
