Showing posts with label datatypes. Show all posts
Showing posts with label datatypes. Show all posts

Wednesday, March 28, 2012

importing text files and converting datatypes

Hi ya... About to tear my hair out.

I thought i fixed this problem, as it WORKED about two days ago, but now I'm getting errors.

I have a series of text files, some are delimited, some are fixed width. I previously was able to import these files thru a dts package by creating the table in a stored procedure. Exple:
CREATE TABLE [Pol_Dump073104]
(
[Product_Type] varchar (12) NULL,
[Benefit] float NULL,
[Base_Premium] varchar (12) NULL,
[Rider_Premium] float NULL,
[Contract_Value] float NULL,
)

I then import the text file into the table and then cast the float datatypes as money in a select into statement to do aggregate functions on the money fields. this worked previously, but now I'm receiving this: 'Error converting datatype char to money.' I tried to convert to float and received the same error, only with float where the word money was.

Please help! :mad:

ThanksI'm not totaly clear on this: so the data is inside the table 'Pol_Dump073104' and a convert(money, benefit) fails with "Error converting datatype char to money"?|||Yup. This particular DTS package works with most of my text files, and it used to work with this particular text file, but now all of a sudden it won't convert imported varchars (or chars, or nchars, or nvarchars) to floats, or monies. And to be clear, I tried importing the file as all varchars, and then in a later step in my dts packaged, tried to cast those particular fields as floats. And that's when I get that error.

It's frustrating!!!

Am I making any sense?|||I think the varchar column may have values that sql cannot convert to money/float. You may have to check the varchar column prior conversion with functions as ISNUMERIC or a where statement that checks (not like '%[^0-9]%'). You may need to consider this in your package, possibly have it send a notification. The rows that do not fit, what are the options?|||Thanks so much kaiowas. I found the bad rows. Now, how can I tell SQL to send me an exception report with the missing rows?|||Kaiowas, your help saved me. I figured everything out, and it's working beautifully... exception report and all. Just needed to be pointed towards the right track. Many thanks.sql

Friday, March 9, 2012

importing data from Oracle - Is there a limitations on the datatypes that can be

> Thanks,
Hard to answer a question that doesn't have more information, but YES there
are limits:
SQL Server can store 8-, 16- and 32-bit integers in a TINYINT, (Short int?)
INT-type column. Other forms of numeric data types (What Oracle calls
Number(x, y) or Numeric(x, y) or whatever it is...) can be stored in SQL
Server as float or decimal.
Dates can be stored. Ditto, on currency types.
Character data that contains text strings can be stored in SQL Server as
CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, or, if pain is something that
you enjoy -- Binary, or VarBinary.
SQL Server also has an IDENTITY type that sort-of fits with whatever Oracle
calls a number-generator-function -- that thing that auto-generates
surrogate keys.
What did you actually have in mind to do?
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSenseiThanks for the info. Need to import to MSSQL from different data source. I
found more info:
Supported data types
The .NET Managed Provider for Oracle supports the Oracle 8i-specific data
types such as CLOB, BLOB, NCLOB, and Bfile. The .NET Managed Provider for
Oracle 2.0 does not support the Oracle 9i XML data type or the Oracle 10g
data types Binary Float and Binary Double. (from
http://support.microsoft.com/kb/322158/en-us)
"Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
news:%23OKLbOlWGHA.4484@.TK2MSFTNGP02.phx.gbl...
> Hard to answer a question that doesn't have more information, but YES
there
> are limits:
> SQL Server can store 8-, 16- and 32-bit integers in a TINYINT, (Short
int?)
> INT-type column. Other forms of numeric data types (What Oracle calls
> Number(x, y) or Numeric(x, y) or whatever it is...) can be stored in SQL
> Server as float or decimal.
> Dates can be stored. Ditto, on currency types.
> Character data that contains text strings can be stored in SQL Server as
> CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, or, if pain is something that
> you enjoy -- Binary, or VarBinary.
> SQL Server also has an IDENTITY type that sort-of fits with whatever
Oracle
> calls a number-generator-function -- that thing that auto-generates
> surrogate keys.
> What did you actually have in mind to do?
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>