Skip to content
+1-888-319-3663

COMMUNITY FORUM

How to export to CSV with embedded double quotes

Mark asked 2 months ago

I have an urgent issue I need to resolve.

I have a SQL statement that generates a file that needs to export to a CSV file that has all fields enclosed in double quotes.  This file will be uploaded to a WordPress website.  The WordPress import requires all fields to be double quoted

However, there are some fields that have the double quotes embedded in the field. These are special fields that have the formatting information, etc such as 

{“rental”:”false”,”sale”:”false”}

 

In SQL, I am surrounding the text/string fields in single quotes.

Now, when I export, I am telling SmartConnect to surround all fields with double quotes, but the embedded double quotes in the field are getting converted to single quotes and then causes issues with WordPress.

 

How do I export to CSV, maintaining the embedded double quotes?  I tried double quoting, such as 

{“”rental””:””false””,””sale””:””false””}

But all of the double quotes were converted to single quotes, which made it worse.  

This is for a website that will be running live next week so I need to find a solution asap.

 

Cheers,

 

Mark

Mark replied 2 months ago

As a follow up, I also tried changing the double quotes to char(34) in the SQL statement that builds the field. They were also converted to single quotes.

Does anyone have any ideas or maybe even an alternative solution?

Answers
Mickie Stamm answered 2 months ago

Hi Mark – 

So you have something like this as your source (simplified, for testing purposes):

SELECT ‘ABCCO’ as CUSTNMBR
, ‘{“rental”:”false”,”sale”:”false”}’ as SpecialFormat
, ‘ABC Company aka “ABCC”‘ as CUSTNAME

And you are getting this in the CSV output – with special formatting fields converted to single quotes and others not being escaped with another double quote(?):

CUSTNMBR,SpecialFormat,CUSTNAME
“ABCCO”,”{‘rental’:’false’,’sale’:’false’}”,”ABC Company aka “ABCC””

I tried a few different things and the best I could do was a hack – to replace my source SQL with something crazy like this instead:

SELECT ‘ABCCO’ as CUSTNMBR
, replace(‘{“rental”:”false”,”sale”:”false”}’,'”‘,’~^~’) as SpecialFormat
, replace(‘ABC Company aka “ABCC”‘,'”‘,’~^~’) as CUSTNAME

Then, open the CSV file in Notepad and search/replace the ~^~ with “”:

before:

CUSTNMBR,SpecialFormat,CUSTNAME
“ABCCO”,”{~^~rental~^~:~^~false~^~,~^~sale~^~:~^~false~^~}”,”ABC Company aka ~^~ABCC~^~”

after:

CUSTNMBR,SpecialFormat,CUSTNAME
“ABCCO”,”{“”rental””:””false””,””sale””:””false””}”,”ABC Company aka “”ABCC”””

I don’t like it because it’s for sure a hack but I’m not seeing another way either (yet). Also, please let me know if I misunderstood your issue.

 

Mark replied 2 months ago

No, that is exactly the issue. Currently, our webmaster is opening the CSV file and fixing any column with the formatting information and changing the single quotes to double quotes. But we are trying to automate the process. We also found that when SmartConnect exports out to CSV, it “simplifies” the headings, removing special characters and replacing with underscores. I have gotten around this by having a task after the map runs to fix the headings which I found in an eone forum post. I wonder if I need to do something similar with the double quotes? But it is only about 3 columns then need to be fixed so not sure how that will go.

Mickie Stamm replied 2 months ago

Ah ok – I don’t know why I took it from your original post that this was a one time export scenario. Anyway, yes I would suggest adding to your header replacement code a search and replace in the text file. I had suggested something odd like ~^~ only because that key combination would otherwise probably not exist in the data and you could safely search and replace on that string. What version of SmartConnect are you using? I have been testing with 21.1.0.763 and I just looked at another install I have with 20.18.1.59 and I’m seeing a lot more options in version 21, including a mapping for the header values which I just tested out, including a bunch of special characters and it seemed to work great.


If you would like to submit an answer or comment, please sign in to the eOne portal.