Tue Dec 07, 2021 6:03 am
Login Register Lost Password? Contact Us


Quote in dataset

Topics specific to the use of the ECL IDE

Tue Jun 16, 2015 4:00 pm Change Time Zone

Hi Team,

what is the use of Quote attribute while we read the csv files in the dataset? Is it indicating the columns having quotes in the source. Please clarify.

Thanks,
Elango
elango_v
 
Posts: 14
Joined: Wed May 20, 2015 12:50 pm

Tue Jun 16, 2015 5:26 pm Change Time Zone

Elango,

Standard CSV-type files always have a record delimiter (usually CRLF) and a field delimiter (usually a comma). Therefore, if your data should happen to contain any of the specified delimiters for that file, then the strings that could contain them must also be delimited by "quote" characters so the system can correctly interpret the difference between them.

For example, say you have a 3-field standard CSV file:
Code: Select all
id, name, address
and the data looks like this:
Code: Select all
1, jones, fred, 123 main st, anytown, FL 12345
note that there are "too many" commas here and by default the three fields will contain this:
Code: Select all
1, jones, fred
and you will have lost actual data. To make it come out "right" your data has to look like this:
Code: Select all
1, "jones, fred", "123 main st, anytown, FL 12345"
and that's what the QUOTE option on our CSV files is all about.

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1606
Joined: Wed Oct 26, 2011 7:40 pm

Wed Jun 17, 2015 10:54 am Change Time Zone

Thanks for the update.I am facing a truncation issue in a column . there are 10 columns in a csv file.
below is one column value in the source -
"dsfklsdfk dskaflskdf as Jafar Dochin, 136 Chous, mumbai, formerly of 20 St Leonard Court, bombay 330000, 9 Cunzie Neuk, santacruize, Fife and 1 View Court, formerly t/a \"Sofia’s\" Fish Bar was sequestrated by The Accountant in Bankruptcy on 26 February 2009 and ambany, Accountant in Bankruptcy, has been appointed to act as trustee on the sequestrated estate. Any creditor of the debtor named above is invited to submit his statement of claim in the prescribed form, with any supporting accounts or vouchers, to Eileen Blackburn CA, French Duncan, the agent acting on behalf of the Accountant in Bankruptcy in this sequestration. For the purpose of formulating claims, creditors should note that the date of sequestration is 26 February 2009. ambani, Accountant in Bankruptcy, Trustee Accountant in Bankruptcy, 1 mg Road, chennai, 622004. (2517/403)"

I am using these attributes : csv( separator(','),heading(1), terminator(['\n', '\r\n']), quote(['\'','"']))

All other records are fine except the above column. it is truncated remaining string are overlapping other columns.
is that because there \"\" inside the column? how to handle this? all other records are working fine except the above.
Please clarify.
elango_v
 
Posts: 14
Joined: Wed May 20, 2015 12:50 pm

Wed Jun 17, 2015 1:17 pm Change Time Zone

Try it this way:
Code: Select all
CSV( SEPARATOR(','),HEADING(1), QUOTE('"'), ESCAPE('\'))

There is no default value for the ESCAPE character, so you need to explicitly specify it.

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1606
Joined: Wed Oct 26, 2011 7:40 pm

Wed Jun 17, 2015 1:27 pm Change Time Zone

Yes. It is working now after providing the escape attribute. Thanks a lot for the info.
elango_v
 
Posts: 14
Joined: Wed May 20, 2015 12:50 pm


Return to ECL IDE

Who is online

Users browsing this forum: No registered users and 1 guest

cron