boniver Posted April 13, 2010 Share Posted April 13, 2010 Hi, I'm new to phpMyAdmin and relatively new to MySQL. I've got a .csv file which I want to import into a database I've created on MySQL. My .csv file has a column which has decimal values (for example: 10.1, 40.4, 0.1 etc) as well as some null values (completely empty cells in the excel sheet) which are relevant values in my case. Now my big problem is, whenever I import the .csv file, the null values get automatically converted to 0.0 or 0 even though I've chosen for that attribute to be Null with a default value of Null, and yet it still converts them to 0 which I really don't want as 0 would be considered a value in my data. This happens with any of the data types (decimal, float and double) I really need help with this, been fiddling around with my database for far too long. Thanks! Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/ Share on other sites More sharing options...
Ken2k7 Posted April 13, 2010 Share Posted April 13, 2010 I don't think empty values are really null. Null means nothing. An empty cell may just be an empty string. Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040665 Share on other sites More sharing options...
boniver Posted April 13, 2010 Author Share Posted April 13, 2010 I went through the .csv file and deleted/cleared the contents of each empty cell to ensure there wasn't any text or anything in them. How can I import them into the database so that these empty cells are nulls? I would think many people would run into this problem, especially when importing a large number of rows/columns Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040667 Share on other sites More sharing options...
Ken2k7 Posted April 13, 2010 Share Posted April 13, 2010 Well... it gets processed, but you probably want a script to change them to null. Maybe write an importer? Basically, you're inputing an empty string - "" - that is NOT the same as a null value. Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040675 Share on other sites More sharing options...
boniver Posted April 13, 2010 Author Share Posted April 13, 2010 still getting the same thing. those cells with the text "null" in them get converted to 0.0 for decimal or 0 for float/double i dont get it, this has me really stuck edit: oh i see, empty cells are considered as "". I dont know how to get around this without manually doing sql update statements which will take forever I don't know how to do scripts. im just new to this all Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040677 Share on other sites More sharing options...
boniver Posted April 13, 2010 Author Share Posted April 13, 2010 ok, what's driving me even more crazy is i have a similar table that uses decimal (3,3) and the empty cells in that .csv file were successfully turned into NULL on that table. ive done nothing different with this .csv file i dont know whats going on but something is really screwing with me Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040684 Share on other sites More sharing options...
Jax2 Posted April 13, 2010 Share Posted April 13, 2010 Stupid question, but did you check to see if that table field is set to "NOT NULL"? or has a default set on it? <-- love that!! It reminds me of myself on a daily basis lol! Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040687 Share on other sites More sharing options...
boniver Posted April 13, 2010 Author Share Posted April 13, 2010 Yep, the attribute looks like this: Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040689 Share on other sites More sharing options...
Ken2k7 Posted April 13, 2010 Share Posted April 13, 2010 Is it a big deal whether it is an empty string or null? Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040697 Share on other sites More sharing options...
boniver Posted April 13, 2010 Author Share Posted April 13, 2010 Is it a big deal whether it is an empty string or null? An empty string would be fine I think, however I've been trying that in the csv file and it still gets converted to 0 Here are two lines from my .csv files: 10,4,Japan,2007,0.960,0 11,4,Luxembourg,1980,,-3 clearly there is a missing value on the second line, but why the hell does it get converted to a 0.0 when I've specified to allow nulls??? Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040703 Share on other sites More sharing options...
Ken2k7 Posted April 13, 2010 Share Posted April 13, 2010 Because the empty space is treated as an empty string. When MySQL casts it to the type specified, which is decimal, it changes to 0.0 so that makes perfect sense. Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040704 Share on other sites More sharing options...
boniver Posted April 13, 2010 Author Share Posted April 13, 2010 ok wow, that has solved it. Have to manually put in NULL in those spaces in the csv file Thank you all for putting up with my crap. Really appreciate it. Now if you don't mind, I think I'll go have a heart attack Link to comment https://forums.phpfreaks.com/topic/198337-null-values-with-decimalfloatdouble/#findComment-1040706 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.