0

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 import an excel-made utf8 csv 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 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. 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)?

enter image description here

my data looks like this in excel: enter image description here

Improve this question
7
  • Ok, so you have values separated by commas. What if you have commas in the data? Well a common approach is to wrap all the text in double quotes (I think Excel export does this by default). This way, one can distinguish between the commas (between the quotes) and the commas that delineate the columns. Now what happens when you have double quotes in the data? You must use a different character to enclose your strings (or columns). One that does not exist in the data.
    – Jacob H
    May 22, 2018 at 18:18
  • This is a duplicate of stackoverflow.com/questions/50472683/… and should be closed.
    – Mike Lischke
    May 23, 2018 at 7:19
  • @JacobH thanks, your comment solved one of my confusion. I opened the csv file in Sublime Text and did some data with comma are wrapped in double quotes. And now I understand why.
    – Rick
    May 23, 2018 at 9:47
  • @MikeLischke there's a useful comment here and I would like to keep the question. Do I disobey any rules if I ask a question at both sites? Because I think there might be different views (maybe some specialists visit this site more often, sort of thing).
    – Rick
    May 23, 2018 at 9:50
  • 1
    @MikeLischke Ok, i would follow your advice and stop posting same question on different sub-sites.
    – Rick
    May 23, 2018 at 12:39

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.