Solved

MySQL importing empty values default to 0

Posted on 2010-08-29
5
926 Views
Last Modified: 2012-05-10
I have the following field,
Field: field1
Type: DOUBLE
Length/Values:
Default: Null
Collation:
Attributes:
Null: TRUE

when I import an exported csv file from Microsoft Access with fieldtype value and default value nothing. For an empty(null) value, MySQL replaces it with 0, when obviously I want Empty/Null.
Because it is a number the csv just looks like ,,, as only text fields are quoted.

help appreciated


0
Comment
Question by:thydzik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Rajesh Dalmia
ID: 33555810
u can run a update query after data import

Update Table set field1=NULL where field1 = 0
0
 
LVL 11

Author Comment

by:thydzik
ID: 33555829
Thanks, this is a good suggestion, but problem is there are actual 0 values which I need to keep.
I am thinking if I don't get a solution to change the values to text fields.
But I just don't understand why the default value of Null doesn't work, even when I check the Null tick-box.
0
 
LVL 11

Expert Comment

by:Rajesh Dalmia
ID: 33555856
if the value in csv file is empty then normal import will make that 0...
and anyway for field type double you should have a valid numeric value...
if u really want a NULL value also then you can do thing...
in csv you replace empty values with some other value like '-999' or anything else...something which after import will help you  identify...after import you can update that specific value with NULL value using query....
0
 
LVL 11

Accepted Solution

by:
thydzik earned 0 total points
ID: 33555905
yeh, the problem is MySQL needs a value of 'NULL' to make it a null value, but Access doesn't do this for Null values, it makes it an empty string on export.

I would either need to change the Number field to a Text field in access to allow a default value of 'NULL' to be present in the exported csv, or change the Number field to a Text field in MySQL to allow an empty string to be present in the csv file.
0
 
LVL 2

Expert Comment

by:Tiller79188231
ID: 33558727
convert your field to a varchar... then import your data.. then update your field to replace '' string value with NULL... then convert field back to integer...

update tableName set fieldName=NULL where fieldName='';
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question