23646

what does quotechar mean in mysql while importing data?

Question:

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

<hr />

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:

8"

When you export it will be:

"8"""

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:

8" 8'

It will output it as 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 <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)

Recommend

  • Read asynchronously data from NetworkStream with huge amount of packets
  • Change Playback Quality of YTPlayerView
  • Override a private method in the lib folder of a gem
  • What happens when a browser that supports SPDY receives an HTTP2 (H2) response?
  • Merging rows with shared information
  • xslt - referencing the output result of the previous template
  • How to prepare a C++ string for sql query
  • PyYaml parses '9:00' as int
  • undefined reference error due to use of static variables [duplicate]
  • XSD assert not recognised
  • How to retrieve information from antrun back to maven?
  • Meteor helpers not available in Angular template
  • Linq Objects Group By & Sum
  • Using $this when not in object context
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Array.prototype.includes - not transformed with babel
  • Spray.io: When (not) to use non-blocking route handling?
  • Align navbar back button on right side
  • Volley JsonObjectRequest send headers in GET Request
  • Accessing IRQ description array within a module and displaying action names
  • Window Size for Mac application
  • Modifying destination and filename of gulp-svg-sprite
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • Redux, normalised entities and lodash merge
  • Build own AppleScript numerical error handling
  • Websockets service method fails during R startup
  • Do create extension work in single-user mode in postgres?
  • Google cloud sdk not working when python points python3
  • GridView Sorting works once only
  • Is there a mandatory requirement to switch app.yaml?
  • How to format a variable of double type
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • Buffer size for converting unsigned long to string
  • Hits per day in Google Big Query
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • How to Embed XSL into XML
  • java string with new operator and a literal
  • How to push additional view controllers onto NavigationController but keep the TabBar?