Search…
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:
1
"account","account2","service","quantity","rate","note","interval","instance"
2
"Acc1","Subacc1","ServiceX","100","1","Embedded "quote"","monthly","Instance"
3
"Acc1","Subacc2","ServiceY","100","1","Inactive","daily","Instance2"
4
"Acc1","Subacc2","ServiceY","100","1","Inactive","daily","Instance2"
5
"Acc1","Subacc2","ServiceY","100","1","Active","daily","Instance2"
6
"Acc1","Subacc3","ServiceZ","100","1","Active","individually","Instance3"
7
"Acc1","Subacc3","ServiceZ","100","1","More ""quotes""","individually","Instance3"
8
"Acc1","Subacc3","ServiceZ","100","1","Active","individually","Instance3"
9
"Acc2","Subacc4","ServiceX","100","1","Inactive","monthly","Instance"
10
"Acc2","Subacc4","ServiceY","100","1","Inactive","daily","Instance2"
11
"Acc2","Subacc4","ServiceZ","100","1","Active","individually","Instance3"
Copied!
The following script demonstrates the use of option noquote and dequote to import the data regardless:
1
option noquote
2
import system/extracted/example.csv source example alias data
3
dequote example.data
4
export example.data as DEQUOTED.CSV
Copied!
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:
1
account,account2,service,quantity,rate,note,interval,instance
2
Acc1,Subacc1,ServiceX,100,1,Embedded "quote",monthly,Instance
3
Acc1,Subacc2,ServiceY,100,1,Inactive,daily,Instance2
4
Acc1,Subacc2,ServiceY,100,1,Inactive,daily,Instance2
5
Acc1,Subacc2,ServiceY,100,1,Active,daily,Instance2
6
Acc1,Subacc3,ServiceZ,100,1,Active,individually,Instance3
7
Acc1,Subacc3,ServiceZ,100,1,More ""quotes"",individually,Instance3
8
Acc1,Subacc3,ServiceZ,100,1,Active,individually,Instance3
9
Acc2,Subacc4,ServiceX,100,1,Inactive,monthly,Instance
10
Acc2,Subacc4,ServiceY,100,1,Inactive,daily,Instance2
11
Acc2,Subacc4,ServiceZ,100,1,Active,individually,Instance3
Copied!
Last modified 22d ago
Export as PDF
Copy link