CODEWITHSUNDEEP
mysqlsqlcsvtypesimport
Savino
Savino

Reputation: 376

MySQL "0 or 1" Column in CSV file imported as "127"

I am trying to import CSV files into a MySQL database.

The values for one of the columns (WIN_FLAG) in my CVS files is either "1" or "0".

When I import the CSV files into the table, every value in the table under the WIN_FLAG column is "127".

A rough example of what is happening is as follows:

for the CSV files:

Name     Date       WIN_FLAG
Dog      23/06/12   0
Cat      22/07/13   1
Mouse    21/05/11   0

what is shown in the table after import:

Name     Date       WIN_FLAG
Dog      23/06/12   127
Cat      22/07/13   127
Mouse    21/05/11   127

I think the problem has something to do with the datatype of my WIN_FLAG column.

I have tried tinyint(4) and tinyint(1) and these both give me this problem.

Is there a different datatype I should be using for this column? Or is it another problem?

Thanks for your help.

Upvotes: 1

Views: 964

Answers (1)

Michael J. Anderson
Michael J. Anderson

Reputation: 479

127 is the max value of a tinyint in mysql.

Using tinyint(1) or tinyint(4) doesnt change how the data is stored, but limits the number of characters that will be returned.

http://dev.mysql.com/doc/refman/5.1/en/integer-types.html

I think the issue isn't with your db, but with how your loading process is interpreting the values from your spreadsheet. If the values imported really are integer = 1 or integer = 0, they would copy over into your tinyint column fine.

Try looking at the raw data your importing. If you have quotes around your numbers, then the way you're importing the data may be mis-interpreting them as character values and converting them into integers.

Upvotes: 1

Related Questions