导入数据时，quotechar在mysql中是什么意思? [英] what does quotechar mean in mysql while importing data?

问题描述

有人向我发送了一个xlsx excel文件，我用excel打开了该文件，并使用utf8编码将其另存为csv.

Someone sent me a xlsx excel file and I opened it with excel and saved as csv with utf8 encoding.

我使用mysql workbench导入向导将excel制造的utf8 csv 文件导入数据库表.但是导入的结果错过了一些数据(少于应有的数据).

I use mysql workbench import wizard to import an excel-made utf8 csv file to a database table. But the imported result missed some data (less than it should have).

我认为这与 quotechar 有关.

默认情况下， quotechar 是双引号，但我有一些这样的数据(混合单引号和双引号):

By default the quotechar is double quote but I have some data like this (mixing single quote and double quote):

• 8'10"foo bar
• 4"x 6" foo foo bar

我尝试省略该值，但不能(请参见图片中的错误).

I've try to omit the value but it can't (see the error from the pic).

所以在这里我想弄清楚:

So here I want to figure out:

1. quotechar 在这里是什么意思?它是如何工作的?为什么这有关系?它不能仅从csv文件导入所有内容吗?
2. 在数据混合单引号和双引号的同时如何正确导入数据(后来我需要检索它们并将其用作搜索关键字，因此最好保留原始格式)?

1. What does quotechar mean here? How does it work? Why does it matter? Can't it just import everything from the csv file?
2. How can I import the data correctly while my data mixes single quote and double quote (later I need to retrieve them and use as search keywords, so it'd be better to keep the original form)?

我的数据在excel中看起来像这样:

my data looks like this in excel:

推荐答案

您将从Excel将数据以CSV格式导出，因此在Excel中的外观无关紧要.

You are going to export your data from Excel as a CSV, I assume, so how this looks in Excel is irrelevant.

当您以CSV格式从excel导出数据时，它将用双引号将您的数据封装起来.数据本身中的任何双引号都将由excel自动替换为第二个双引号.

When you export the data from excel as a CSV format it's going to encapsulate your data in double quotes. Any double quotes in the data itself is going to be escaped by a second double quote automatically by excel.

例如，如果您的数据是:

As an example, if your data is:

 8"

导出时将是:

 "8"""

您必须告诉Mysql您将字符串包含在字符"中.那就是它在说的quotechar.这是您要填写的表格上的第二个字段.

You have to tell Mysql that you are enclosing strings in character ". That is the quotechar it's talking about. It's the second field on that form you are filling out.

我不确定MySQl会变得多么挑剔，因为我还没有永远将CSV导入Mysql. Excel CSV输出的诀窍在于，如果您有类似这样的数据:

I'm not sure how picky MySQl is going to be here since I haven't imported CSV to Mysql in forever and ever and ever. The trick with the Excel CSV output is that if you have data like:

8"
8'

它将输出为CSV:

"8"""
8'

第二个记录/字段没有获得双引号封装，因为它不包含需要封装的字符(双引号，回车或换行符).

The second record/field doesn't gain the double quote encapsulation since it doesn't contain a character that requires encapsulation (A double quote, a carriage return, or a line feed).

Mysql 可能会阻塞(希望它的导入过程足够强大，可以处理封装的和非封装的字段)

Mysql might choke on that second record (Hopefully it's import process is robust enough to handle encapsulated and non-encaps'd fields though)

这篇关于导入数据时，quotechar在mysql中是什么意思?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！