-2

I am parsing an excel file and adding the values im getting into a database. But one of the columns I am parsing can be either a float or a string. Like such: 0,45 or Contact Support. What is the best way for me to add this as it is, to my database? Or more how should I format my database column for this?

mrfr
  • 1,724
  • 2
  • 23
  • 44
  • or better still how do I fix my data? That's what you should be asking – e4c5 Feb 16 '17 at 11:41
  • Excel makes it easy to store inconsistent and demormalised data (that's why management and marketing love it and IT hates it). Do you need to transmit those errors into the database or get them fixed? – Álvaro González Feb 16 '17 at 11:42
  • 1
    Without knowing what's the purpose of storing the data, what you plan to do with it afterwards, it is pretty difficult to come up with a sensible suggestion. It is very easy to recommend to have 2 fields, one for the floats and another for the strings, however, this may be completely impractical from reporting point of view. You need to consider your reporting requirements and make a decision based on that. As it stands, your question cannot be responsibly answered. – Shadow Feb 16 '17 at 13:11

3 Answers3

2

use varchar datatype(in MySQL) for the column you want to parse and you can save both the integer and character values.

Pathik Vejani
  • 4,263
  • 8
  • 57
  • 98
Rony Samuel
  • 462
  • 7
  • 20
  • So if I add `1,0` into a `varchar` value. If i retreve it, would it be an float or a string? – mrfr Feb 16 '17 at 11:44
  • @FelixRosén it will be string! – Pathik Vejani Feb 16 '17 at 11:45
  • Alright! So there isnt really a way to check for it. If the excel row you are parsing have strings and floats in them, you store everything as strings. – mrfr Feb 16 '17 at 11:46
  • 1
    @FelixRosén. Yes. Since varchar is designed to contain characters and symbols, the data will be treated as string. – Rony Samuel Feb 16 '17 at 11:48
  • @FelixRosén. For getting the result of values with integer/string, you will have to write query for checking it as a regular expression. – Rony Samuel Feb 16 '17 at 11:52
  • @FelixRosén. Check out the similar question http://stackoverflow.com/questions/22510736/mysql-select-only-numeric-values-from-varchar-column – Rony Samuel Feb 16 '17 at 11:53
1

I recommend you create a float type column and a varchar column. Write your parser so that valid float values are filled to the float column and the rest to the varchar one. This will allow you to both sort your database by the value of the float type column and run queries on fields that are missing that value: SELECT * FROM mytable WHERE myfloat IS NULL

Kaivosukeltaja
  • 15,541
  • 4
  • 40
  • 70
  • And when i retrive my data I check both the columns and if one is null, get the data from the other (If `myfloat` is null, get data from `myVarchar`? – mrfr Feb 16 '17 at 11:49
  • @FelixRosén, correct. You can also build your query so that it will return the string if the float is null using `SELECT CASE` or something similar. – Kaivosukeltaja Feb 16 '17 at 11:53
0

first am with Kaivosukeltaja answer.

but if you can't do that, make a varchar column to save the strings or doubles.

BTW when you get data from a database all the data comes in string format then you can cast it to any type you want.

if you are using PHP:

$x = $row['string_double_column'];

if(is_numeric($x))
  return doubleval($x);
else
 return $x;