dequote

Overview

The dequote statement is used to remove quotes from the start and end of column names and values

Syntax

dequotedset.id

Details

In cases where data from a 3rd-party system is formatted in such a way that the quoting in the CSV cannot be dealt with during import it is necessary to use option noquote to treat the quotes as if they were data and import the file 'as is'.

The dequote statement can then be used to remove quotes after import. Only cell values (and column names) where there is a quote both at the start and end will be modified and any quotes present in the middle of those values will be left untouched.

The dequote statement will use whichever quote character is defined by option quote. The default is a double quote: "

Example

The following example data cannot be imported without using option noquote due to the presence of the unescaped embedded quotes in the note column on lines 2 and 7:

"account","account2","service","quantity","rate","note","interval","instance"
"Acc1","Subacc1","ServiceX","100","1","Embedded "quote"","monthly","Instance"
"Acc1","Subacc2","ServiceY","100","1","Inactive","daily","Instance2"
"Acc1","Subacc2","ServiceY","100","1","Inactive","daily","Instance2"
"Acc1","Subacc2","ServiceY","100","1","Active","daily","Instance2"
"Acc1","Subacc3","ServiceZ","100","1","Active","individually","Instance3"
"Acc1","Subacc3","ServiceZ","100","1","More ""quotes""","individually","Instance3"
"Acc1","Subacc3","ServiceZ","100","1","Active","individually","Instance3"
"Acc2","Subacc4","ServiceX","100","1","Inactive","monthly","Instance"
"Acc2","Subacc4","ServiceY","100","1","Inactive","daily","Instance2"
"Acc2","Subacc4","ServiceZ","100","1","Active","individually","Instance3"

The following script demonstrates the use of option noquote and dequote to import the data regardless:

option noquote
import system/extracted/example.csv source example alias data
dequote example.data
export example.data as DEQUOTED.CSV

Note that dequote will leave the embedded quotes untouched. This means that statements such as replace can be used to remove or modify them as required.

The resulting exported file from the example script above contains the following:

account,account2,service,quantity,rate,note,interval,instance
Acc1,Subacc1,ServiceX,100,1,Embedded "quote",monthly,Instance
Acc1,Subacc2,ServiceY,100,1,Inactive,daily,Instance2
Acc1,Subacc2,ServiceY,100,1,Inactive,daily,Instance2
Acc1,Subacc2,ServiceY,100,1,Active,daily,Instance2
Acc1,Subacc3,ServiceZ,100,1,Active,individually,Instance3
Acc1,Subacc3,ServiceZ,100,1,More ""quotes"",individually,Instance3
Acc1,Subacc3,ServiceZ,100,1,Active,individually,Instance3
Acc2,Subacc4,ServiceX,100,1,Inactive,monthly,Instance
Acc2,Subacc4,ServiceY,100,1,Inactive,daily,Instance2
Acc2,Subacc4,ServiceZ,100,1,Active,individually,Instance3