在laravel中读取CSV文件时发出UTF-8,二进制数据和特殊字符

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.

展开翻译

译文

我正在使用League / CSV Laravel包来读取和操作CSV文件并将CSV数据保存到数据库中但是我 我正面临一些问题,只有一些行有一些特殊字符,如“45.6ºF”,同时从CSV读取数据。</ p>

我已经搜索了很多关于这个问题,发现我们应该使用“ 数据库排序规则中的UTF-8“或”utf8mb4“并将该CSV保存在”utf8“中,但它仅适用于键盘上的所有特殊字符。</ p>

我想要 使用键盘上没有的所有类型的特殊字符,如“45.6ºF”。</ p>

目前,我的代码正在读取CSV列数据并将其转换为二进制数据'b “column value”'</ code>它将“b”</ code>与字符串相加,并将该字符串转换为二进制值,仅用于那些具有任何特殊字符的字符串。</ p>

< 我花了很多时间但找不到更好的解决方案来解决这个问题。 所以请帮助我,我将非常感谢你。</ p>

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

从CSV读取“b”后,它给我这个结果。</ p>

  array:2 [▼
“ID”=&gt; “1”
“问题”=&gt; b“华氏度到摄氏度(ºF到ºC)”转换计算器用于温度转换,带有附加表格和公式“

</ </ code> </ pre>

但它应该在字符串中 格式没有“b”二进制。</ p>

如果我用特殊字符复制该字符串并将其分配给静态变量,那么它可以正常工作并将数据保存到这样的数据库中</ p>

  $ a =“华氏度到摄氏度(ºF到ºC)的转换计算器,用于温度转换,附加表格和公式”; 

$ qs = Question :: first();
$ qs-&gt; question = $ a;
$ qs-&gt; save();
</ code> </ pre>

经过多次努力,我找到了解决方案 这个问题。
我刚刚将这一行添加到代码中,以便在保存到数据库之前将其转换为 utf8_encode </ strong>。</ p>

  $ r = array_map(“utf8_encode  “,$ record); 
</ code> </ pre>

不要只是复制从谷歌粘贴文本以保存在数据库中,因为复制粘贴文本和特殊字符不能正常工作 时间。</ p> \ n

谢谢。</ p>
</ div>

duanmeng1950
duanmeng1950 这个符号没什么特别的,我只是从Console中复制粘贴文本,这个符号显示在laravel转储值中。
6 个月之前 回复
duanmiexi2275
duanmiexi2275 “来自哪里?
6 个月之前 回复

2个回答

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.

展开翻译

译文



我找到了解决这个问题的方法。 下面的代码行修复了我的问题 $ r = array_map(“utf8_encode”,$ record)</ code>; 我们只需要在保存到数据库之前转换为 utf8_encode </ strong>。</ p>
</ div>

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 º.

展开翻译

译文



不要使用任何转换例程; 它只会导致“两个错误意外地做出正确的行为”。</ p>

由于存在MySQL的 LOAD DATA INFILE </ code>,你甚至需要 fgetcsv </ 码>? 只需使用命令中指定的合适字符集</ em>执行 LOAD </ code> SQL命令即可。 其值应与csv文件的编码匹配。 如果有疑问,请尝试从文件</ em>获取º</ code> 的十六进制:</ p>

  hex BA  - &gt; 字符集latin1 
hex C2BA - &gt; 字符集utf8(或utf8mb4)
</ code> </ pre>

数据库表中的列可以是latin1或utf8或utf8mb4。 如果需要,转换将在 LOAD </ code>期间发生。</ p>

度数符号是两个字符集中存在的少数特殊字符之一,所以如果你有 其他人,latin1可能不是一个可行的选择。 (utf8 / utf8mb4有更多特殊字符。)</ p>

当前使用 b“...”</ code>可能会因为使用 C2BA而使事情变得更糟 </ code>进入latin1列,导致Mojibake:º</ code>而不是º</ code>。</ p>
</ div>

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!