IBM Sterling Transformation Extender

Hi all, trying to find out if there's a solution to this seemingly simple problem - some of our users export their data from excel into CSV, and send it to us.  Excel seems to escape delimiters like this:

Column1,Column2,Column3
abc,hello,world
def,"hi,hello",abc

where double quotes in pairs is used to escape the comma in the field value.  It appears ITX supports a release character, but it would only work if the data was escaped like the following:

Column1,Column2,Column3
abc,hello,world
def,hi",hello,abc

Is there a way to achieve the former rather than the latter?  We have some custom java code currently handling this scenario but we like ITX large file processing, so was hoping to move it to ITX.

------------------------------
Zamir Juma
------------------------------

#DataExchange
#IBMSterlingTransformationExtender

Please attach your Excel file here.  Let me check it out.

------------------------------
Rex Chan
------------------------------

Original Message:
Sent: Mon March 28, 2022 11:55 AM
From: Zamir Juma
Subject: CSV exported from Excel, escaping the delimiter with a pair of double quotes

Hi all, trying to find out if there's a solution to this seemingly simple problem - some of our users export their data from excel into CSV, and send it to us.  Excel seems to escape delimiters like this:

Column1,Column2,Column3
abc,hello,world
def,"hi,hello",abc

where double quotes in pairs is used to escape the comma in the field value.  It appears ITX supports a release character, but it would only work if the data was escaped like the following:

Column1,Column2,Column3
abc,hello,world
def,hi",hello,abc

Is there a way to achieve the former rather than the latter?  We have some custom java code currently handling this scenario but we like ITX large file processing, so was hoping to move it to ITX.

------------------------------
Zamir Juma
------------------------------

#IBMSterlingTransformationExtender
#DataExchange

I am SO glad you asked this question. With ITX 10.1+ we added a new CSV importer in our new UI (called Design Server).  This CSV importer does handle your use case. I will create a video to post here tomorrow on how it can be done.

Stay tuned!

------------------------------
Stephanie Fetzer
------------------------------

Original Message:
Sent: Mon March 28, 2022 11:55 AM
From: Zamir Juma
Subject: CSV exported from Excel, escaping the delimiter with a pair of double quotes

Hi all, trying to find out if there's a solution to this seemingly simple problem - some of our users export their data from excel into CSV, and send it to us.  Excel seems to escape delimiters like this:

Column1,Column2,Column3
abc,hello,world
def,"hi,hello",abc

where double quotes in pairs is used to escape the comma in the field value.  It appears ITX supports a release character, but it would only work if the data was escaped like the following:

Column1,Column2,Column3
abc,hello,world
def,hi",hello,abc

Is there a way to achieve the former rather than the latter?  We have some custom java code currently handling this scenario but we like ITX large file processing, so was hoping to move it to ITX.

------------------------------
Zamir Juma
------------------------------

#IBMSterlingTransformationExtender
#DataExchange

Video showing quote schemes in ITX CSV importer attached.

------------------------------
Stephanie Fetzer
------------------------------

Original Message:
Sent: Tue March 29, 2022 09:09 PM
From: Stephanie Fetzer
Subject: CSV exported from Excel, escaping the delimiter with a pair of double quotes

I am SO glad you asked this question. With ITX 10.1+ we added a new CSV importer in our new UI (called Design Server).  This CSV importer does handle your use case. I will create a video to post here tomorrow on how it can be done.

Stay tuned!

------------------------------
Stephanie Fetzer

Original Message:
Sent: Mon March 28, 2022 11:55 AM
From: Zamir Juma
Subject: CSV exported from Excel, escaping the delimiter with a pair of double quotes

Hi all, trying to find out if there's a solution to this seemingly simple problem - some of our users export their data from excel into CSV, and send it to us.  Excel seems to escape delimiters like this:

Column1,Column2,Column3
abc,hello,world
def,"hi,hello",abc

where double quotes in pairs is used to escape the comma in the field value.  It appears ITX supports a release character, but it would only work if the data was escaped like the following:

Column1,Column2,Column3
abc,hello,world
def,hi",hello,abc

Is there a way to achieve the former rather than the latter?  We have some custom java code currently handling this scenario but we like ITX large file processing, so was hoping to move it to ITX.

------------------------------
Zamir Juma
------------------------------

#DataExchange
#IBMSterlingTransformationExtender

Could you please share the project/ mtt file here?

Thanks in advance.

------------------------------
Mahesh Kalivarapu
------------------------------

Original Message:
Sent: Wed March 30, 2022 01:03 PM
From: Stephanie Fetzer
Subject: CSV exported from Excel, escaping the delimiter with a pair of double quotes

Video showing quote schemes in ITX CSV importer attached.

------------------------------
Stephanie Fetzer

Original Message:
Sent: Tue March 29, 2022 09:09 PM
From: Stephanie Fetzer
Subject: CSV exported from Excel, escaping the delimiter with a pair of double quotes

I am SO glad you asked this question. With ITX 10.1+ we added a new CSV importer in our new UI (called Design Server).  This CSV importer does handle your use case. I will create a video to post here tomorrow on how it can be done.

Stay tuned!

------------------------------
Stephanie Fetzer

Original Message:
Sent: Mon March 28, 2022 11:55 AM
From: Zamir Juma
Subject: CSV exported from Excel, escaping the delimiter with a pair of double quotes

Hi all, trying to find out if there's a solution to this seemingly simple problem - some of our users export their data from excel into CSV, and send it to us.  Excel seems to escape delimiters like this:

Column1,Column2,Column3
abc,hello,world
def,"hi,hello",abc

where double quotes in pairs is used to escape the comma in the field value.  It appears ITX supports a release character, but it would only work if the data was escaped like the following:

Column1,Column2,Column3
abc,hello,world
def,hi",hello,abc

Is there a way to achieve the former rather than the latter?  We have some custom java code currently handling this scenario but we like ITX large file processing, so was hoping to move it to ITX.

------------------------------
Zamir Juma
------------------------------

#DataExchange
#IBMSterlingTransformationExtender