Originally posted by: SystemAdmin

I have a question about reading CSV files (extracted from Excel) in DataStage TX. There are a number of posts in the forum expressing the preference of reading CSV files instead of XLS files. I do have a CSV file to read, but I have a problem in that many of the fields in the CSV file can contain commas as data content. Excel wraps such fields in double-quotes, so the data content commas can be differentiated from the delimiter commas.

Example record from CSV file:
111,222,"333,aaa",444,"555,xxx",666

In this example, there are 6 data fields in the record. The content of the 3rd field is 333,aaa and the content of the fifth field is 555,xxx

Does anybody have any suggestions about how to specify the type tree to read this file such that commas used for delimters vs. commas used in data content can be differentiated?

Thanks!

KG
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

KG

I have simply edited the type tree field for the fields that that enclosed by quotes. For that field use an initiator and a terminator of double quotes (").

In your example this would apply to the 3rd and 5th fields.

This would ensure that the commas in the 3rd and 5th fields are read as data (between the quotes) rather than as delimiters.

Regards

Tarun
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

How about saving the speadsheet as a TXT (tab delimited) file instead of a CSV (comma delimited) file? You may still need to define qupotes as initiators and terminators for the affected fields as Excel still wraps the comma separated values in quotes. Why? I don't know but they do.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Thanks for the replies so far.

I should have mentioned that Excel only wraps the field content in double quotes if it has embedded commas. For example:

111,222,"333,aaa",444,"555,xxx",666
111,"222,XXX",333,444,555,"666,YYY"

Since I don't know ahead of time which fields will have the quotes, I can't include that as part of the field delimiter.

Regarding the idea of exporting the file as tab-delimited instead, I can't do that because the file is coming to us from an external provider. So, I think I'm stuck with the CSV.

KG
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

What about asking the provider to export as tab-delimited instead of csv? We run into this and our partners are usually willing to comply. Might be worth a try to ask. All they can say is "no".
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

I have to deal with a similar situation too . . .

My input is a csv file containing only two fields (columns). The second value is subject to including a comma, in which case the value comes wrapped in quotes.

I chose to ignore these rows from being processed by using a RESTART

The restart on the row in the file generates REJECTs, but also passes the data to the functional map which results in the second field being considered as a complete row, while REJECTing the first field.

It is frustrating. Is this a bug in 7.5?
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

So you've been given a sow's ear and you're expected to make a silk purse of it? One way that will work, although it may be tedious if you have to do it for each field, is to create the field as a partitioned item containing two items one with double quotes for initiator and terminator and one without. Just make sure the name of the item with quotes precedes the one without quotes alphabetically as this is the order in which the partition will be evaluated.

Below is the export file of a type tree that will read in your data correctly:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE TTMAKER SYSTEM "ttmaker60.dtd">
<?ANALYZE?><TTMAKER Version="6.0"><NEWTREE Filename="C:\MercatorTips\CSVwithEmbeddedCommas.mtt"><ROOT SimpleTypeName="Data" SetUpProperties="DEFAULT" SetUpComponents="DELETE" OrderSubtypes="ASCENDING"><Sequence partition="NO"><Implicit/></Sequence>
<CharTextWestern><Size Min="0" Max="S"/>
<Western CharSet="NATIVE"/>
<ValueRestrictions IgnoreCase="NO" Rule="INCLUDE"></ValueRestrictions>
</CharTextWestern>
</ROOT>
<ITEM SimpleTypeName="Field" CategoryOrItemParent="Data" partition="YES" OrderSubtypes="ASCENDING"><TypeSyntax><TERMINATOR><Literal IgnoreCase="NO"><Western CharSet="NATIVE"/>
<LiteralValue>,</LiteralValue>
</Literal>
</TERMINATOR>
</TypeSyntax>
<CharTextWestern><Size Min="0" Max="S"/>
<Western CharSet="NATIVE"/>
<ValueRestrictions IgnoreCase="NO" Rule="INCLUDE"></ValueRestrictions>
</CharTextWestern>
</ITEM>
<ITEM SimpleTypeName="QuotedField" CategoryOrItemParent="Field Data" partition="NO" OrderSubtypes="ASCENDING"><TypeSyntax><INITIATOR><Literal IgnoreCase="NO"><Western CharSet="NATIVE"/>
<LiteralValue>"</LiteralValue>
</Literal>
</INITIATOR>
<TERMINATOR><Literal IgnoreCase="NO"><Western CharSet="NATIVE"/>
<LiteralValue>"</LiteralValue>
</Literal>
</TERMINATOR>
</TypeSyntax>
<CharTextWestern><Size Min="0" Max="S"/>
<Western CharSet="NATIVE"/>
<ValueRestrictions IgnoreCase="NO" Rule="INCLUDE"></ValueRestrictions>
</CharTextWestern>
</ITEM>
<ITEM SimpleTypeName="UnQuotedField" CategoryOrItemParent="Field Data" partition="NO" OrderSubtypes="ASCENDING"><CharTextWestern><Size Min="0" Max="S"/>
<Western CharSet="NATIVE"/>
<ValueRestrictions IgnoreCase="NO" Rule="INCLUDE"></ValueRestrictions>
</CharTextWestern>
</ITEM>
<GROUP SimpleTypeName="File" CategoryOrGroupParent="Data" OrderSubtypes="ASCENDING"><Sequence partition="NO"><Implicit/><SequenceComponent><RelativeTypeName>Record</RelativeTypeName>
<Range Min="1" Max="S"/>
</SequenceComponent>
</Sequence>
</GROUP>
<GROUP SimpleTypeName="Record" CategoryOrGroupParent="Data" OrderSubtypes="ASCENDING"><TypeSyntax><TERMINATOR><Literal IgnoreCase="NO"><Western CharSet="NATIVE"/>
<LiteralValue><NL></LiteralValue>
</Literal>
</TERMINATOR>
</TypeSyntax>
<Sequence partition="NO"><Explicit Track="Places"><Delimited location="INFIX"><DelimiterLiteral><Western CharSet="NATIVE"/>
<LiteralValue>,</LiteralValue>
</DelimiterLiteral>
</Delimited>
</Explicit>
<SequenceComponent><RelativeTypeName>Field</RelativeTypeName>
<Range Min="1" Max="S"/>
</SequenceComponent>
</Sequence>
</GROUP>
</NEWTREE>
</TTMAKER>
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

Jim's solution might be an order or magnitude more elegant than mine. Just shows you who the smart one is. (kudos Jim)

What I did was to think of a preprocessor map that would convert the raw CSV file and change the delimiter from a comma to a pipe (|). I started tinkering and was able to fairly quickly come up with a solution that took the raw CSV:

code:1:06a2b54082111,222,\"333,aaa\",444,\"555,xxx\",666
111,\"222,XXX\",333,444,555,\"666,YYY\"[/code:1:06a2b54082]

and produced a modified version of it:

code:1:06a2b54082111|222|333,aaa|444|555,xxx|666
111|222,XXX|333|444|555|666,YYY[/code:1:06a2b54082]

Then I would think it would be fairly simple to send this data to another map that would be able to handle it like you want.

Email me if you want to see my example. I can send you the trees and map.

John M Gibby
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

I think you can solve your issue by using the unordered Group.

1. Create 2 items, First with inititator " and Terminator " and the second one is without anything
2. Create an unOrdered Group and keep both these element in that.
3. For Mapping you can follow the INDEX property for that particular group.

Good Luck,
Pranab.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

What if the data itself contains a double quote, and another double quote will be placed in front of the real double quote. For example,

aaa,"bb,b","cc,""c",ddd,eee

bb,b = field 2
cc,"c = field 3

Since we don't know which fields will contain a comma or a double quote. In this case, what can I do on the type tree to ensure that it can parse it properly?

Thanks
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

I think the pre-process map idea would still work. You can also import the csv into Excel and export as required. You could even import it into access and use ODBC. Many ways to skin a csv.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange