0

I'm dealing with this problem in my MYSQL database for several hours now. I work with OS X 10.8.4 and use the tool Sequel Pro to work with my database. The table I have troubles with looks like this: 

CREATE TABLE `Descriptions` (  

  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,  
`company` varchar(200) DEFAULT NULL,  
`overview` mediumtext,    
`trade` mediumtext,  
PRIMARY KEY (`id`))  
 ENGINE=InnoDB AUTO_INCREMENT=1703911 DEFAULT CHARSET=utf8;

I imported a csv file like this: 

LOAD DATA LOCAL INFILE 'users/marc/desktop/descriptions kopie.txt'
INTO TABLE descriptions  
FIELDS TERMINATED BY ';'  
LINES TERMINATED BY '\n'  
(@dummy, company, overview, trade)

When I look at the data in my table now, everything looks the way I expect (SELECT * Syntax). But I can't work with the data. When I try to select the company 'SISTERS', from which I know that it exists, it gives me no results. Also the fields "overview" and "trade" are not NULL when there's no data, it is just an empty string. The other tables in the database works just fine with the imported data. Somehow MySQL just doesn't see the values as something to work with, it doesn't bothers to read them.

What I tried so far:
- I used text wrangler to convert the csv to txt (utf-8) and loadet it into the database, did not work
- I changed the fields into BLOB and back to varchar/mediumtext to force mysql to do something with the data, did not work
- I tried to use the Sequel Pro Import function, did not change anything
- I tried to make a new table and copy the old one into it, did not change anything
- I tried to force mysql to change the values by using the concat syntax (just adding random variables which I could delete later again)

Could it have something to do with the collation settings? Could it has something to do with my regional settings (Switzerland) on my OS X) Any other ideas? I would appreciate any help very much.

Kind Regards,
Marc

Marc U.
  • 63
  • 4
  • are you sure the fields in the CSV file match the columns in your table. One thing that stands out to me is that your `id` is defined as `auto_increment`. You really don't need that if the id is in the CSV file. Also when you import does it say there are warnings? You can do `SHOW WARNINGS` to get mysql to show you any problems it encountered but tried to continue with. – Cfreak Aug 28 '13 at 21:05
  • There were no errors importing the Data. Also when I look at the Data by SELECT * FROM descriptions, everything is where it is intended to be. I don't use the ID from the csv file although it is in the first column there. I send it to @dummy and use the auto_increment instead. – Marc U. Aug 28 '13 at 21:20

1 Answers1

0

I could solve the problems. When I opened the csv in Text Wrangler and let the invisible characters show, it was full of red reversed question marks. Those sneaky bastards, they messed up everything. I don't now what they are, but they were the problem. I removed them with the "Zap Gremlins..." option.

Marc U.
  • 63
  • 4