MySQL error code 1366

[ghosh22]

Hi I am trying to build a MySQL database. I have made a table where some fields are VARCHAR and others are INT. All of them are null. One of the columns I made is Vote which is INT(5).
Now I am trying to import data from a text file. The problem is, some of the rows in the column Vote are blank.
As a result, MySQL giving me ERROR 1366 which says the following:
"Incorrect integer value: ' ' for column 'Vote' at row 1"
Tried making the column NOT NULL using the ALTER TABLE command..didn't work..
Would you please tell me how to fix it?
Thanks

[d5e5]

Try creating a table having the same column names whose columns are all VARCHAR and import your data into that. That should work as long as your VARCHAR columns are big enough. Then you can write a query that reads the data from this all-VARCHAR table and INSERTs into the table you want to populate. That seems like a roundabout way to do it, but I used to do that a lot years ago when I had problems importing a file. If you have to convert a value from VARCHAR to INT, it's easier to handle that in your insert query that selects from your intermediate table.

[ghosh22]

Hi Thanks for your reply. I tried making a test table to populate with my data where some data are missing as well like before. But now the problem is different which you could see from this image. The first Country column VARCHAR(20) is not coming properly! Country names are like Australia, Greece etc..
[IMG]http://i56.tinypic.com/2cnfri9.jpg[/IMG]
But missing data in columns City, Age are loading properly now..
Please note that, I am doing all these in my Windows computer. I saved the data first in .xlsx format and then saved them as tab delimited text.
Command used to load data:
LOAD DATA INFILE "J/MySQL/test.txt" INTO TABLE test;
Thanks..

[d5e5]

Hi Thanks for your reply. I tried making a test table to populate with my data where some data are missing as well like before. But now the problem is different which you could see from this image. The first Country column VARCHAR(20) is not coming properly! Country names are like Australia, Greece etc..
[IMG]http://i56.tinypic.com/2cnfri9.jpg[/IMG]
But missing data in columns City, Age are loading properly now..
Please note that, I am doing all these in my Windows computer. I saved the data first in .xlsx format and then saved them as tab delimited text.
Command used to load data:
LOAD DATA INFILE "J/MySQL/test.txt" INTO TABLE test;
Thanks..

If you attach your test.txt tab delimited file to your post we could test the import and try to replicate the error.

[ghosh22]

If you attach your test.txt tab delimited file to your post we could test the import and try to replicate the error.

Hi Please check my attached tab delimited .txt file. Also is there no other way of inserting missing INT fileds?
Thanks

[d5e5]

If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.

see note in docs for LOAD DATA
The following works on my linux computer. (I don't have MySQL on Windows.)

DROP TABLE IF EXISTS `all_text`;
CREATE TABLE `all_text`(
`country` VARCHAR(20),
`some_nbr` VARCHAR(20),
`city` VARCHAR(20),
`gender` VARCHAR(20),
`vote` VARCHAR(20)
) ENGINE = MYISAM;

#Change the file path to get this to run on your computer
LOAD DATA LOCAL INFILE "/home/david/Programming/data/test.txt" INTO TABLE all_text
LINES TERMINATED BY '\r\n';

SELECT * FROM all_text;

The result of the above is

+-----------+----------+------+--------+----------+
| country   | some_nbr | city | gender | vote     |
+-----------+----------+------+--------+----------+
| Australia | Sydney   | 55   | M      | 245254   |
|           | Beijing  | 65   | M      | 22254    |
| Greece    | Athens   |      | F      | 2222AVG5 |
| Thailand  | Bangkok  | 42   | M      | 76577    |
| Malayasia |          | 22   | M      | 7676578  |
| Japan     | Tokyo    | 75   | F      | 987765   |
| Chile     | Santiago | 58   | F      | 5453ASD  |
| Russia    | Moscow   | 75   | F      | 343545   |
| Uganada   |          |      | M      | 676867   |
| Canada    | Montreal | 77   | M      | 4544345  |
+-----------+----------+------+--------+----------+

[ghosh22]

Thanks a lot!! It works..I think my mistake was in the command as I missed 'Local' and 'Lines terminated' parts.
I have one more question. Searched Google for it but no help. I think I am searching wrong terms.
I have attached another small tab delimited text file attached here. I want the corresponding rows should come. For eg. If I select Country="England", then I want the output with all the corresponding cities and Particiapnats.
So the output table should look like:

Country  Cities Participants
England     Taunton   225
            Bristol   654
            Plymouth  585
England     London    552
            Exeter    235
            Leeds     445

I tried using LIMIT but no luck (or I am not using it correctly!) (Please note that the numbers showing here is not part of my table!)
Please suugest..
Thanks

[d5e5]

Thanks a lot!! It works..I think my mistake was in the command as I missed 'Local' and 'Lines terminated' parts.
I have one more question. Searched Google for it but no help. I think I am searching wrong terms.
I have attached another small tab delimited text file attached here. I want the corresponding rows should come. For eg. If I select Country="England", then I want the output with all the corresponding cities and Particiapnats.
So the output table should look like:

Country  Cities Participants
England     Taunton   225
            Bristol   654
            Plymouth  585
England     London    552
            Exeter    235
            Leeds     445

I tried using LIMIT but no luck (or I am not using it correctly!) (Please note that the numbers showing here is not part of my table!)
Please suugest..
Thanks

Your input table has no country associated with most of the cities, such as Bristol, Plymouth, Exeter and Leeds. When you write a SELECT query you can easily retrieve the rows that contain 'England', but of course that will miss the rows where country is null. I don't know of any way around that except to manually fill in all null values in the country column with the appropriate country. You know that Leeds is in England, but MySQL does not.

[ghosh22]

hmm.thats really a hard work 'cause I have a lot of data like this and manually filling them is a pain!
Anyway..thanks for your help...