Placeholder

Customer Forum

How should I escape characters when Importing into Workbooks?

Workbooks Support Posted: 2011-03-29 15:20

Workbooks imports data in comma-separated values (CSV) format.  A variety of options exist to allow you to choose how that CSV data is interpreted.

Normally you will not need to be too concerned with these options: the defaults "just work". However sometimes you need to import data containing 'special' characters like this:

a\b"s,!@

(If you're importing from ACT! you'll quickly find data like this.)

The problem with importing the above is that the comma is viewed as separating fields so that CSV lets you wrap the whole up in double-quotes. Which means that double-quotes are also special. Here you need to prefix it with a backslash: backslashes are also special!

So the rules are:

  • wrap the value up in double-quotes if there is a comma in it.
  • prefix 'special' characters, including double-quotes and backslashes with a backslash.

Sometimes using a separator of comma and quoting fields using double-quotes is not what you want.  The options which let you choose the delimiters and the way in which characters are "escaped" are available in the Advanced CSV file upload settings section when you create a new import job (click image to enlarge): 

Advanced CSV file upload settings

Internally Workbooks uses MySQL's LOAD DATA INFILE command passing in the parameters you set in the Advanced CSV file upload settings.

The full documentation which explains how these are used are available on the MySQL website, here.