Read CSV file with ¤ character and insert into MySql Table

[PriteshP23]

I am not able to insert data from CSV file with "¤" Character. Please help me to solve the problem.

Thanks a lot in advanced.

CSV File:

FirstName¤LastName¤City¤Year |$|
Bill¤Gates¤NW¤2013¤|$|
David¤Beckham¤London¤2010¤|$|

PHP Code:

$csv_file = "Contacts.csv";
$csvfile = fopen($csv_file, 'r');
$theData = fgets($csvfile);
    $i = 1;
    do
    {
        $insert_csv = array();
        $insert_csv['FirstName'] = $data[0];
        $insert_csv['LastName'] = $data[1];
        $insert_csv['City'] = $data[2];
        $insert_csv['Year'] = $data[3];

        $query = "INSERT INTO LTE_noria_enodeb (FirstName, LastName, City, Year)
        VALUES ('".$insert_csv['FirstName']."','".$insert_csv['LastName']."','".$insert_csv['City']."','".$insert_csv['Year']."')";

        $req = new requete($site->db, $query);
        echo $query;
    }while ($data = fgetcsv($csvfile,1000,";","|$|"));

    fclose($csvfile);

    // I tried this way also.. But both are not working :(:(

   $sql = 'LOAD DATA LOCAL INFILE "ContactsList" INTO TABLE Contacts(FirstName, LastName, City, Year) fields terminated by "¤" LINES TERMINATED BY "\n" IGNORE 1 LINES';
$req = new requete($site->db, $sql);

[Stuugie]

How about excluding such characters, is that an option?

[almostbob]

http://php.net/manual/en/function.fgetcsv.php
examine the section on setting the delimiter
while ($data = fgetcsv($csvfile,1000,"¤",etcetera

[PriteshP23]

@Stuugie: no, not that option. I am sorry.

@almostbob: I had written " ¤ " only in my code. Still it is not working.. Please, Can you correct my code ?

line 18.   }while ($data = fgetcsv($csvfile,1000,"¤","|$|"));

[almostbob]

}while ($data = fgetcsv($csvfile,1000,"¤","|"));
delimiters; end data, data separator, can only be single characters
so long as |$| is unique as the end data record marker,, the above should work
fingers crossed

sometimes graphic character eg. ¤ can be the display figure for a non-characterset letter,
if the data input is not the same language/characterset as the database the ¤ could represent any of several million possible high order utf characters, or 10hex lower order control character
eg english has 26 letters in 2 cases, 10 digits about 40 punctuations,
Japanese has 50K Kanji
ANSI <=> UTF8 is one such problem

[IIM]

http://stackoverflow.com/questions/9032204/how-to-read-multibyte-characters-from-a-csv-file-using-php might help in solving your issue.

[PriteshP23]

@almostbob: Thanks for your time. But i am getting values only in 1st column. Other columns are empty.

Bill¤Gates¤N

@IIM: Do you have idea how to do setlocale to a UTF-8 locale ?

[pzuurveen]

have you tried using

$delimiter=chr(207);
while ($data = fgetcsv($csvfile,1000,$delimiter,'|'))

[almostbob]

Following Pzuurveen's idea, can you open the csv source file in a hex editor, and find exactly what character the delimiter is, and if it is not displayable, you can use php tet processing to replace every instance with a "," (comma no quotes)

[PriteshP23]

@pzuurveen & @almostbob

It is giving the same result. :( :( Any other input ??

Bill¤Gates¤N

[pzuurveen]

I did an echo ord('¤'); and found the correct ascii-code to be 164 so use $delimiter=chr(164);

note to self: "don't do any visual lookups and have eyes checked"

[PriteshP23]

@pzuurveen: Good Remark. Thanks. Noted. I will do crosscheck. I got value 194 for "¤".

It is giving the SAME Result. I would like to solve the problem.

echo ord('¤');
$delimiter=chr(194);

// Output:
Bill¤Gates¤N

[PriteshP23]

In addition, It is taking " |$| " at last. For example: Year|$|

It should be only Year.