Convert/Cast from Varchar to decimal

guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
i had been using a Bulk Insert to a dummy table where all columns are varcha
r.
then selecting that table and running it thru a for each loop in an asp.net
application and attempting the conversion there just using Cdbl(datarow.Item
(0))
or Cdec(datarow.Item(0)).
is there a better way to do this right on the database itself?
because it seems like somewhere an implicit rounding is occuring so i'll get
55.00 where 55.50 should be.
the problem i think is the inconsistant values, but, i thought i'd ask the r
eal experts.
as some of the values in the .csv (formerly .xls) file are 33.02, some are w
hole numbers 234 and even others are 55.5.
ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data t
ype numeric"
if anyone has any suggestions, i'd really appreciate it.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Did you try altering that column in the dummy table to numeric(5, 2) instead
varchar?
AMB
"rik butcher" wrote:

> guys, i've got a quick question on uploading a .txt or .csv file to a tabl
e in sql server 2000.
> i had been using a Bulk Insert to a dummy table where all columns are varc
har.
> then selecting that table and running it thru a for each loop in an asp.ne
t
> application and attempting the conversion there just using Cdbl(datarow.It
em(0))
> or Cdec(datarow.Item(0)).
> is there a better way to do this right on the database itself?
> because it seems like somewhere an implicit rounding is occuring so i'll g
et 55.00 where 55.50 should be.
> the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some ar
e whole numbers 234 and even others are 55.5.
> ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and th
e consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
> if anyone has any suggestions, i'd really appreciate it.
> thanks again
> rik
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||Why are all the columns of your dummy table all varchar? Assuming that
the data is clean, you can create the target table with a column of the
desired decimal type and have nothing else to do about conversion.
As far as the error you're seeing with CAST, it indicates that there is
a value in the [Amount] column that represents a value too big for
decimal(5,2), in other words, greater than 999.99 or less than -999.99.
Again assuming that the data is clean, CAST should work if the target
type can hold the values. You'll get a different error if you have non-
numeric strings, like 'abc':
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
I don't know the specifications of the Cdbl and Cdec functions, so I won't
comment on why some values seem to be changed more than rounding
should cause.
Steve Kass
Drew University
rbutch@.coair.com wrote:

>guys, i've got a quick question on uploading a .txt or .csv file to a table
in sql server 2000.
>i had been using a Bulk Insert to a dummy table where all columns are varch
ar.
>then selecting that table and running it thru a for each loop in an asp.net
>application and attempting the conversion there just using Cdbl(datarow.Ite
m(0))
>or Cdec(datarow.Item(0)).
>is there a better way to do this right on the database itself?
>because it seems like somewhere an implicit rounding is occuring so i'll ge
t 55.00 where 55.50 should be.
>the problem i think is the inconsistant values, but, i thought i'd ask the
real experts.
> as some of the values in the .csv (formerly .xls) file are 33.02, some are
whole numbers 234 and even others are 55.5.
>ive tried using Cast(Amount as Decimal(5,2)) in an insert statement and the
consistant error i get is "Arithmentic overflow converting numeric to data
type numeric"
>if anyone has any suggestions, i'd really appreciate it.
>thanks again
>rik
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...
>|||thanks guys. you both were right. using numeric(5,2) worked but i still had
to widen that column again [that's what the overflow message was referring t
o] - so, i dont have to use a dummy table. and bcp bulk insert is working li
ke a charm.
i just had to look thru the actual values and there it was.
sometimes its the simplest things - and i appreciate you guys setting me str
aight on this.
thanks again
rik
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Convert integer to date

Hi,
I have a couple of csv files with a date column like '20061231' or
'20050521'. What is the easiest way to convert this column into a date in th
e
SSIS pipeline?
The connection manager doesn't accept just defining the column as a date.
Also, converting the integer into a date using the Convert transformation
does not work well.
Can anyone help me out'
Kind regards,
Michel MolsHi Michel,
I can propose you a way to solve that problem. I've already get the
same problem in a project and I used to create a dervied column with
that expression to get my date in that format DD/MM/YYYY:
(DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)D
ATE),7,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),1,4))
YYYY/MM/DD: (DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)D
ATE),1,4) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),7,2))
I am not sure that is the best way to do that but it is at least one.
Michel a =E9crit :

> Hi,
> I have a couple of csv files with a date column like '20061231' or
> '20050521'. What is the easiest way to convert this column into a date in=
the
> SSIS pipeline?
> The connection manager doesn't accept just defining the column as a date.
> Also, converting the integer into a date using the Convert transformation
> does not work well.
>=20
> Can anyone help me out'
>=20
> Kind regards,
>=20
> Michel Mols

Convert integer to date

Hi,
I have a couple of csv files with a date column like '20061231' or
'20050521'. What is the easiest way to convert this column into a date in the
SSIS pipeline?
The connection manager doesn't accept just defining the column as a date.
Also, converting the integer into a date using the Convert transformation
does not work well.
Can anyone help me out?
Kind regards,
Michel Mols
Hi Michel,
I can propose you a way to solve that problem. I've already get the
same problem in a project and I used to create a dervied column with
that expression to get my date in that format DD/MM/YYYY:
(DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)DATE),7,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),1,4))
YYYY/MM/DD: (DT_DBTIMESTAMP)(SUBSTRING(((DT_WSTR,8)DATE),1,4) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),5,2) + "/" +
SUBSTRING(((DT_WSTR,8)DATE),7,2))
I am not sure that is the best way to do that but it is at least one.
Michel a crit :

> Hi,
> I have a couple of csv files with a date column like '20061231' or
> '20050521'. What is the easiest way to convert this column into a date inthe
> SSIS pipeline?
> The connection manager doesn't accept just defining the column as a date.
> Also, converting the integer into a date using the Convert transformation
> does not work well.
> Can anyone help me out?
> Kind regards,
> Michel Mols

convert data file type from unix to pc using stored procedure?

Hi, I have a script written in ASP to load data file (.csv) to ms sql. In the script, I have a portion of script looks like taht :

....

Do While NOT oInFile.AtEndOfStream
oOutFile.WriteLine Replace(oInFile.Readline, chr(13), vbcrlf)
Loop

....

After that, I will use a BULK INSERT to input data to ms sql.

I am wondering how do I convert each row (data) to vbcrlf in Stored Procedure? Coz' I did not compose the convertion part, and I got no error when running BULK INSERT, but no rows are inserted :( HELP!!!!

I guess it's because the file is not being converted into a correct format??

Can you give more information? e.g.: sample text used in BULK INSERT, BULK INSERT command you're using, and the schema of the destination table.

convert comma separated values into rows withou using UDF

Hi,
I'm looking for a way to turn csv like 4,5,6,3 into rows without using
UDF, so it can be used in pure T-SQL only.
I want to use it in a code generated sql string, that will use these
values with an IN statement.
for example:
if column VAL contains 1,2,3
I would like to write something like:
SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
work.
I also don't want to use CHARINDEX since this query should be as
efficient as possible.
I hope this can be done at all.
Thanks,
Eran.I think this is what you want:
DECLARE @.X NVARCHAR(4000)
SELECT @.X = 'SELECT columnList FROM x WHERE v IN (' + VAL + ')'
FROM tableName
EXEC(@.X)
But had the database been designed correctly, you wouldn't have to hope.
You could have simply used a join and avoided using dynamic SQL at all.
(1) You should never use SELECT * in production code.
(2) A column in a row (cell) should never have more than one value. In
other words, you should never store comma-separated values in a cell.
99.999% of the time, the presence of either of these is a sure sign of the
laziness, short-sightedness, and incompetence of the database designer or
developer.
"EranS" <eransevi@.yahoo.com> wrote in message
news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>|||thanks to brian heres a sample
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
"Brian Selzer" wrote:

> I think this is what you want:
> DECLARE @.X NVARCHAR(4000)
> SELECT @.X = 'SELECT columnList FROM x WHERE v IN (' + VAL + ')'
> FROM tableName
> EXEC(@.X)
> But had the database been designed correctly, you wouldn't have to hope.
> You could have simply used a join and avoided using dynamic SQL at all.
> (1) You should never use SELECT * in production code.
> (2) A column in a row (cell) should never have more than one value. In
> other words, you should never store comma-separated values in a cell.
> 99.999% of the time, the presence of either of these is a sure sign of the
> laziness, short-sightedness, and incompetence of the database designer or
> developer.
>
> "EranS" <eransevi@.yahoo.com> wrote in message
> news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
>
>|||Thanks Brian,
I can't run what you suggested since the values list should be
dynamically created when running the query, and it should both be in
one query.
This solution is only temporary until I'll normalize my tables better,
so I'm looking for a dirty way to do it. I guess I'll resort to using a
temporary table with the values in rows and then use it with the IN
clause. This won't be as fast as hoped but it should be better then
using a function.|||Take a look at this:
http://solidqualitylearning.com/Blo.../10/22/200.aspx
(by Dejan Sarka)
ML|||Eran, there are some issues with your request...

> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
A UDF is pure T-SQL.

> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> ...
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
Using dynamic execution, you will end up with a different plan generated for
each unique string. Using the CHARINDEX method in a UDF that splits the arra
y
elements into multiple rows (or not encapsulated with a UDF), your code will
still be able to use an index on the table's join column, and reuse executio
n
plans.
That's really the way to go in this case.
My 2c (tested),
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"EranS" wrote:

> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>|||Using a table function to split a string into rows can in most cases prove t
o
be much faster than using the IN operator, since the latter gets translated
into several OR clauses, which slow down the query processing.
On the other hand the table function returns a table of values that can be
joined to the other table(s) in your query, thus taking advantage of the muc
h
faster JOIN operation.
ML|||SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
IF OBJECT_ID ('dbo.fnBAS_utlSplitIDsIntoTable') IS NOT NULL --AND
OBJECTPROPERTY ( OBJECT_ID('dbo.fnBAS_utlSplitIDsIntoTable') ,
'IsinLineFunction' ) = 1
DROP FUNCTION dbo.fnBAS_utlSplitIDsIntoTable
GO
CREATE FUNCTION dbo.fnBAS_utlSplitIDsIntoTable (@.sIDList varchar(8000),
@.Delimeter char(1))
RETURNS TABLE AS
-- Version : $Id: fnBAS_utlSplitIDsIntoTable.sql,v 1.7 2005/05/09 20:11:33
vladimirm Exp $
/*
Usage:
declare @.IDlist varchar(500)
set @.idlist ='12,11,4,23455,13'
select * from dbo.fnBAS_utlSplitIDsIntoTable(@.IDList,',')
*/
RETURN
(
SELECT 1 - LEN(REPLACE(LEFT(@.sIDList, Number), @.Delimeter, SPACE(0))) +
Number AS [idx]
,SUBSTRING( @.sIDList, Number, CHARINDEX(@.Delimeter, @.sIDList +
@.Delimeter, Number) - Number) AS [intID]
FROM dbo.Numbers_8000
WHERE SUBSTRING(@.Delimeter + @.sIDList, Number, 1) = @.Delimeter
AND Number < LEN(@.sIDList) + 1
)
GO
"EranS" <eransevi@.yahoo.com> wrote in message
news:1124871159.442873.283030@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm looking for a way to turn csv like 4,5,6,3 into rows without using
> UDF, so it can be used in pure T-SQL only.
> I want to use it in a code generated sql string, that will use these
> values with an IN statement.
> for example:
> if column VAL contains 1,2,3
> I would like to write something like:
> SELECT * FROM x WHERE v IN (SELECT VAL ...) - this of course does not
> work.
> I also don't want to use CHARINDEX since this query should be as
> efficient as possible.
> I hope this can be done at all.
> Thanks,
> Eran.
>