Someone sent me a
xlsx excel file and I opened it with excel and saved as csv with utf8 encoding.
I use mysql workbench import wizard to <strong>import an excel-made utf8 csv</strong> file to a database table. But the imported result missed some data (less than it should have).
And I think it has something to do with the <strong>quotechar</strong>.
By default the <strong>quotechar</strong> is double quote but I have some data like this (mixing single quote and double quote):<ul><li>8'10" foo bar </li> <li>4" x 6" foo foo bar</li> </ul>
I've try to omit the value but it can't (see the error from the pic).
So here I want to figure out:<ol><li>What does <strong>quotechar</strong> mean here? How does it work? Why does it matter? Can't it just import everything from the csv file? </li> <li>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)?</li> </ol>
<a href="https://i.stack.imgur.com/8Cd3j.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/8Cd3j.png" data-original="https://i.stack.imgur.com/8Cd3j.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>
my data looks like this in excel: <a href="https://i.stack.imgur.com/2f7nY.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/2f7nY.png" data-original="https://i.stack.imgur.com/2f7nY.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>Answer1:
You are going to export your data from Excel as a CSV, I assume, so how this looks in Excel is irrelevant.
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:
When you export it will be:
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.
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:
It will output it as CSV:
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 <em>might</em> choke on that second record (Hopefully it's import process is robust enough to handle encapsulated and non-encaps'd fields though)