DataType Question

------=_NextPart_000_0003_01C68F94.FCF1E1C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I'm importing data into MySQL from a CVS file.... one column has dollar =
amounts. There are negative numbers like this... (999.99). Is there a =
way to get the database to convert this into.... -999.99? And what is =
the best datatype to choose?

Thanks,


Melissa
------=_NextPart_000_0003_01C68F94.FCF1E1C0--

Re: DataType Question

I've converted the data.... but when I try to insert into a table with
either decimal or double.... I get 'data truncated for column.... 1265'
error. What numeric datatype with allow for negative numbers? I looked at
the MySQL site and thought they would work.

Melissa

----- Original Message -----
From: "Don Lancaster"
To: "Melissa Dougherty"
Sent: Wednesday, June 14, 2006 9:38 AM
Subject: RE: DataType Question


> You could write a stored procedure to convert the data. Accept as string,
> and for each value with parentheses, strip the parentheses and tack on the
> negative sign. I didn't see anything in the MySQL reference manual about
> currency.
>
> -----Original Message-----
> From: Melissa Dougherty [mailto:melissa@cse-corp.com]
> Sent: Wednesday, June 14, 2006 9:29 AM
> To: win32@lists.mysql.com
> Subject: DataType Question
>
>
> I'm importing data into MySQL from a CVS file.... one column has dollar
> amounts. There are negative numbers like this... (999.99). Is there a
> way to get the database to convert this into.... -999.99? And what is the
> best datatype to choose?
>
> Thanks,
>
>
> Melissa
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: DataType Question

Decimal, Double and Float will all allow negative numbers.

A little more information would be helpful... what version of MySQL are
you using? How are you loading your data in the CVS file into the table?
What length are you setting for the datatype? (ie; 7,2) Cheers.

Armando

Melissa Dougherty wrote:
> I've converted the data.... but when I try to insert into a table with
> either decimal or double.... I get 'data truncated for column.... 1265'
> error. What numeric datatype with allow for negative numbers? I looked
> at the MySQL site and thought they would work.
>
> Melissa
>
> ----- Original Message ----- From: "Don Lancaster"
> To: "Melissa Dougherty"
> Sent: Wednesday, June 14, 2006 9:38 AM
> Subject: RE: DataType Question
>
>
>> You could write a stored procedure to convert the data. Accept as
>> string, and for each value with parentheses, strip the parentheses and
>> tack on the negative sign. I didn't see anything in the MySQL
>> reference manual about currency.
>>
>> -----Original Message-----
>> From: Melissa Dougherty [mailto:melissa@cse-corp.com]
>> Sent: Wednesday, June 14, 2006 9:29 AM
>> To: win32@lists.mysql.com
>> Subject: DataType Question
>>
>>
>> I'm importing data into MySQL from a CVS file.... one column has
>> dollar amounts. There are negative numbers like this... (999.99). Is
>> there a way to get the database to convert this into.... -999.99? And
>> what is the best datatype to choose?
>>
>> Thanks,
>>
>>
>> Melissa
>>
>
>

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: DataType Question

I'm using MySQL 5.0.19 and I'm loading the data using MySQL Front. I've
tried using decimal(10,2) and double(12,2), both gave me the data truncated
error.

Is there an easier way to load data into the database.... other than using
MySQL Front?

Melissa
----- Original Message -----
From: "Armando"
To:
Sent: Wednesday, June 14, 2006 8:22 PM
Subject: Re: DataType Question


> Decimal, Double and Float will all allow negative numbers.
>
> A little more information would be helpful... what version of MySQL are
> you using? How are you loading your data in the CVS file into the table?
> What length are you setting for the datatype? (ie; 7,2) Cheers.
>
> Armando
>
> Melissa Dougherty wrote:
>> I've converted the data.... but when I try to insert into a table with
>> either decimal or double.... I get 'data truncated for column.... 1265'
>> error. What numeric datatype with allow for negative numbers? I looked
>> at the MySQL site and thought they would work.
>>
>> Melissa
>>
>> ----- Original Message ----- From: "Don Lancaster"
>>
>> To: "Melissa Dougherty"
>> Sent: Wednesday, June 14, 2006 9:38 AM
>> Subject: RE: DataType Question
>>
>>
>>> You could write a stored procedure to convert the data. Accept as
>>> string, and for each value with parentheses, strip the parentheses and
>>> tack on the negative sign. I didn't see anything in the MySQL reference
>>> manual about currency.
>>>
>>> -----Original Message-----
>>> From: Melissa Dougherty [mailto:melissa@cse-corp.com]
>>> Sent: Wednesday, June 14, 2006 9:29 AM
>>> To: win32@lists.mysql.com
>>> Subject: DataType Question
>>>
>>>
>>> I'm importing data into MySQL from a CVS file.... one column has dollar
>>> amounts. There are negative numbers like this... (999.99). Is there a
>>> way to get the database to convert this into.... -999.99? And what is
>>> the best datatype to choose?
>>>
>>> Thanks,
>>>
>>>
>>> Melissa
>>>
>>
>>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=melissa@cse-corp.com
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: DataType Question

----- Original Message -----
From: "Melissa Dougherty"
To: "Armando" ;
Sent: Thursday, June 15, 2006 3:40 AM
Subject: Re: DataType Question


> I'm using MySQL 5.0.19 and I'm loading the data using MySQL Front. I've
> tried using decimal(10,2) and double(12,2), both gave me the data
> truncated error.
>
> Is there an easier way to load data into the database.... other than using
> MySQL Front?
>
Yes. Perl or PHP. If you write a little script using these languages you can
convert data or data types as you want ;-)

Petr Vileta, Czech republic
(My server rejects all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: DataType Question

I use a little program called Navicat which works brilliantly to import from
various sources.
Helen

----- Original Message -----
From: "Petr Vileta"
To:
Sent: Thursday, June 15, 2006 5:23 AM
Subject: Re: DataType Question


> ----- Original Message -----
> From: "Melissa Dougherty"
> To: "Armando" ;
> Sent: Thursday, June 15, 2006 3:40 AM
> Subject: Re: DataType Question
>
>
>> I'm using MySQL 5.0.19 and I'm loading the data using MySQL Front. I've
>> tried using decimal(10,2) and double(12,2), both gave me the data
>> truncated error.
>>
>> Is there an easier way to load data into the database.... other than
>> using MySQL Front?
>>
> Yes. Perl or PHP. If you write a little script using these languages you
> can convert data or data types as you want ;-)
>
> Petr Vileta, Czech republic
> (My server rejects all messages from Yahoo and Hotmail. Send me your mail
> from another non-spammer site please.)
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=helen@rttc.co.za


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org