CODEWITHSUNDEEP
phplaravelcsvfgetcsvutf8mb4
Wajid Hussain
Wajid Hussain

Reputation: 81

UTF-8, binary data and special characters issue while reading CSV file in laravel

I am using League/CSV Laravel package to read and manipulate CSV file and save that CSV data into a database but I am facing some issues for some rows only which has some special characters like "45.6 ºF" while reading data from CSV.

I have searched a lot about this problem and found that we should use "UTF-8" or "utf8mb4" in the database collation and save that CSV in "utf8" also but it works only for all those special characters which are on the keyboard.

I want to use all type of special characters like "45.6 ºF" which are not on the keyboard also.

Currently, my code is reading CSV column data and convert it into binary data ' b"column value" ' It adds "b" with the string and converts that string into binary value for only those strings which have any special characters.

I have spent a lot of time but could not find any better solution to this problem. So please help me, I shall be very thankful to you.

 $reader = Reader::createFromPath(public_path().'/question.csv', 'r');
 $reader->setHeaderOffset(0);
 $records = $reader->getRecords();
 foreach ($records as $offset => $record) {
    $qs = Question::first();
    $qs->question =  $record['Question'];
    $qs->save();
 }

It is giving me this result after reading from CSV with "b".

array:2 [▼
  "ID" => "1"
  "Question" => b"Fahrenheit to Celsius (ºF to ºC) conversion calculator for temperature conversions with additional tables and formulas"
]

but it should be in the string format without "b" binary.

If I copy that string with special characters and assign it to the static variable, then it works fine and saves data into a database like this 

$a="Fahrenheit to Celsius (ºF to ºC) conversion calculator for temperature conversions with additional tables and formulas";

$qs = Question::first();
$qs->question =  $a;
$qs->save();

After a lot of struggle, i have found the solution of this problem. I just added this line to code to convert it into utf8_encode before saving in the database.

$r = array_map("utf8_encode", $record);

Don't just copy paste the text from google to save in database because copy paste text and special characters don't work most of the time.

Thanks.

Upvotes: 4

Views: 3072

Answers (2)

Rick James
Rick James

Reputation: 142433

Do not use any conversion routines; it only leads to "two wrongs accidentally making a right".

With the existence of MySQL's LOAD DATA INFILE, do you even need fgetcsv? Simply execute the LOAD SQL command with the suitable character set specified in the command. The value for that should match the encoding of the csv file. If in doubt, try to get the hex of º from the file:

hex BA   --> character set latin1
hex C2BA --> character set utf8 (or utf8mb4)

The column in the database table can be latin1 or utf8 or utf8mb4. The conversion, if needed, will happen during the LOAD.

The degree sign is one of the few special characters that exists in both charsets, so if you have others, latin1 may not be a viable option. (utf8/utf8mb4 has lots more special characters.)

The current use of b"..." may be making things worse by shoehorning C2BA into a latin1 column, leading to Mojibake: º instead of º.

Upvotes: 0

Wajid Hussain
Wajid Hussain

Reputation: 81

I have found a solution to this problem. below line of code fixed my issue $r = array_map("utf8_encode", $record); We just need to convert into utf8_encode before saving into database.

Upvotes: 3

Related Questions