Hi--

I'm using MySQL 5.2 Workbench, and when I import a csv into MySQL, blank
values become 0 when I define the datatype to be a DECIMAL (in our case
(DECIMAL(12,2)). Since there is the potential for values to be 0, this
poses a problem for us. I initially wanted to make all the DECIMAL values
become VARCHAR(255), because I can still do inequality queries on this
column if it is a VARCHAR, and VARCHAR does not mind blanks. It keeps them
as is.

But I'm sure that I must be missing something here. Is there a way to use a
DECIMAL-like operator that treats blanks as blanks? If not, is there a
better strategy then making the relevant column a VARCHAR?

Much thanks!

Thanks for the response. I'm not sure that is exactly what is happening
for me.

I tried an example where I created a dummy database and a dummy table. If I
create a column with decimal datatype and insert 2 different rows, one
blank and one null, they both are treated as nulls. This is what I would
like, but it does not work this way when I try to import a csv.

When I try to import a csv file with either null or blank values for a
decimal datatype, they both get treated as 0.00 (if we are using
DECIMAL(12,2)).

I think the issue has to do with the way MySQL 5.2 Workbench imports csv
values. I experimented with a few different tests, but I haven't found a
way to successfully treat null decimal values being imported from a csv as
nulls in the database without just making the whole column a VARCHAR(255)
datatype, which does seem to allow for nulls, but just seems like the wrong
way to solve the problem.

On Sun, Aug 5, 2012 at 9:53 AM, Hassan Schroeder <hassan.schroeder@gmail.com
> wrote:

> On Sat, Aug 4, 2012 at 8:14 PM, Fred G <bayespokerguy@gmail.com> wrote:
>
> > But I'm sure that I must be missing something here. Is there a way to
> use a
> > DECIMAL-like operator that treats blanks as blanks?
>
> A DECIMAL column is either going to contain a decimal number or
> NULL; 'blank' isn't a term that even makes sense in this context.
>
> If you want NULL rather than 0 for a non-specified value, insert it
> that way.
>
> --
> Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
> http://about.me/hassanschroeder
> twitter: @hassan
>

I don't believe this is limited to Workbench. I saw the same behavior using LOAD INFILE and a pipe-delimited file. There's a bug report that's been open since November of 2006 about this: http://bugs.mysql.com/bug.php?id 23212


-----Original Message-----
From: Fred G [mailto:bayespokerguy@gmail.com]
Sent: Monday, August 06, 2012 9:48 AM
To: Hassan Schroeder
Cc: mysql@lists.mysql.com
Subject: Re: DECIMAL datatype automatically makes blank become 0

Thanks for the response. I'm not sure that is exactly what is happening for me.

I tried an example where I created a dummy database and a dummy table. If I create a column with decimal datatype and insert 2 different rows, one blank and one null, they both are treated as nulls. This is what I would like, but it does not work this way when I try to import a csv.

When I try to import a csv file with either null or blank values for a decimal datatype, they both get treated as 0.00 (if we are using DECIMAL(12,2))..

I think the issue has to do with the way MySQL 5.2 Workbench imports csv values. I experimented with a few different tests, but I haven't found a way to successfully treat null decimal values being imported from a csv as nulls in the database without just making the whole column a VARCHAR(255) datatype, which does seem to allow for nulls, but just seems like the wrong way to solve the problem.

On Sun, Aug 5, 2012 at 9:53 AM, Hassan Schroeder <hassan.schroeder@gmail.com
> wrote:

> On Sat, Aug 4, 2012 at 8:14 PM, Fred G <bayespokerguy@gmail.com> wrote:
>
> > But I'm sure that I must be missing something here. Is there a way
> > to
> use a
> > DECIMAL-like operator that treats blanks as blanks?
>
> A DECIMAL column is either going to contain a decimal number or NULL;
> 'blank' isn't a term that even makes sense in this context.
>
> If you want NULL rather than 0 for a non-specified value, insert it
> that way.
>
> --
> Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
> http://about.me/hassanschroeder
> twitter: @hassan
>

________________________________

Notice: This communication may contain privileged and/or confidential information. If you are not the intended recipient, please notify the sender by email, and immediately delete the message and any attachments without copying or disclosing them. LBI may, for any reason, intercept, access, use, and disclose any information that is communicated by or through, or which is stored on, its networks, applications, services, and devices.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql

Thanks! I'll create a temp table to handle it as well.

On Mon, Aug 6, 2012 at 2:13 PM, Stillman, Benjamin <
BStillman@limitedbrands.com> wrote:

> Meant to add:
>
> My workaround was to set the column as varchar then convert it to decimal
> after the import. When adding to the table, I use a temp table to import to
> as varchar, convert the column, the just select from temp table into prod
> table.
>
>
>
> -----Original Message-----
> From: Stillman, Benjamin [mailto:BStillman@limitedbrands.com]
> Sent: Monday, August 06, 2012 2:05 PM
> To: 'Fred G'
> Cc: mysql@lists.mysql.com
> Subject: RE: DECIMAL datatype automatically makes blank become 0
>
> I don't believe this is limited to Workbench. I saw the same behavior
> using LOAD INFILE and a pipe-delimited file. There's a bug report that's
> been open since November of 2006 about this:
> http://bugs.mysql.com/bug.php?id=23212
>
>
> -----Original Message-----
> From: Fred G [mailto:bayespokerguy@gmail.com]
> Sent: Monday, August 06, 2012 9:48 AM
> To: Hassan Schroeder
> Cc: mysql@lists.mysql.com
> Subject: Re: DECIMAL datatype automatically makes blank become 0
>
> Thanks for the response. I'm not sure that is exactly what is happening
> for me.
>
> I tried an example where I created a dummy database and a dummy table. If
> I create a column with decimal datatype and insert 2 different rows, one
> blank and one null, they both are treated as nulls. This is what I would
> like, but it does not work this way when I try to import a csv.
>
> When I try to import a csv file with either null or blank values for a
> decimal datatype, they both get treated as 0.00 (if we are using
> DECIMAL(12,2)).
>
> I think the issue has to do with the way MySQL 5.2 Workbench imports csv
> values. I experimented with a few different tests, but I haven't found a
> way to successfully treat null decimal values being imported from a csv as
> nulls in the database without just making the whole column a VARCHAR(255)
> datatype, which does seem to allow for nulls, but just seems like the wrong
> way to solve the problem.
>
> On Sun, Aug 5, 2012 at 9:53 AM, Hassan Schroeder <
> hassan.schroeder@gmail.com
> > wrote:
>
> > On Sat, Aug 4, 2012 at 8:14 PM, Fred G <bayespokerguy@gmail.com> wrote:
> >
> > > But I'm sure that I must be missing something here. Is there a way
> > > to
> > use a
> > > DECIMAL-like operator that treats blanks as blanks?
> >
> > A DECIMAL column is either going to contain a decimal number or NULL;
> > 'blank' isn't a term that even makes sense in this context.
> >
> > If you want NULL rather than 0 for a non-specified value, insert it
> > that way.
> >
> > --
> > Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
> > http://about.me/hassanschroeder
> > twitter: @hassan
> >
>
> ________________________________
>
> Notice: This communication may contain privileged and/or confidential
> information. If you are not the intended recipient, please notify the
> sender by email, and immediately delete the message and any attachments
> without copying or disclosing them. LBI may, for any reason, intercept,
> access, use, and disclose any information that is communicated by or
> through, or which is stored on, its networks, applications, services, and
> devices.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
>
>

>>>> 2012/08/04 23:14 -0400, Fred G >>>>
I'm using MySQL 5.2 Workbench, and when I import a csv into MySQL, blank
values become 0 when I define the datatype to be a DECIMAL (in our case
(DECIMAL(12,2)). Since there is the potential for values to be 0, this
poses a problem for us. I initially wanted to make all the DECIMAL values
become VARCHAR(255), because I can still do inequality queries on this
column if it is a VARCHAR, and VARCHAR does not mind blanks. It keeps them
as is.
<<<<<<<<
MySQL does not do real CSV files, only its version of CSV-ish files. What do you mean "blank"? Is there really a character there, between separators, or nothing at all?

In any case, I guess you mean NULL in SQL terms. In that case, MySQL insists on seeing NULL on no escape character, or \N, where escape character is \. If this is LOAD DATA, a trick that another used is helpful here, making use of letting LOAD DATA store in a user variable, and using SET to make it either the number or NULL:

LOAD DATA .... (..., @DN, ....) .... SET decField = IF(@DN = '', NULL, @DN);


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql