Video Screencast Help

Map CSV data to fields in a Collection

Created: 18 Sep 2013 • Updated: 24 Sep 2013 | 3 comments
This issue has been solved. See solution.

I have a CSV file with one rowq of field names and one row of actual data

Field1Name, Field2Name, Field3Name
Field1Data, Field2Data, Field3Data

I'm using the Read CSV From File and told it to Use First Line as Field Names.

How do I get a data type from the single line of CSV text so I can map it to a Collection type?

Thanks!

Operating Systems:

Comments 3 CommentsJump to latest comment

reecardo's picture

You can try the following:

1. Use an Add New Data Element comp to add a new empty string collection/array to the stream

2. Use a Iterate Text File Lines component to navigate through the rows of the CSV file. I would ignore the first line by using a counter, comparing to the text of the column, or just navigating back into comp on First Line path. (note: in my pic below I use a Single Path demo comp from the Demo DLL that does nothing, just so I can loop back into the component)

3. When you get a text file line, use a Split Text Into Collection component (splitting on the comma) to make a collection of elements.

4. Add the elements to your initial collection using an Add Items To Collection component.

Lather/rinse/repeat in your Iterate text File Lines loop until you have the entire collection. You can also see this thread, which is pretty similar: https://www-secure.symantec.com/connect/forums/csv...

Untitled.png

Shovel's picture

How do I map the series of fields from the split text info to my target type?

i.e.

TEXT FILE
JobNumber, Address, Supervisor
#1234, 12 Elm Street, John

Target Data Type
JobNumber (Text), Address (Text), Supervisor (Text)

reecardo's picture

Referencing this previous thread: https://www-secure.symantec.com/connect/forums/csv...

"What you want to do is use a Single Value Mapping component (before the Add Items to Collection component) to map the collection elements into your complex element.

Lets say you have a complex element type called Blah with 3 properties: field1, field2, and field3.

Change the Add New Data Element comp in my pic above to be an array of Blah (vs an array of strings).

Stick a Single Value Mapping before the Add Items to Collection comp. Pick Blah as the target type. Now create your mapping - each field of Blah (field1, field2, and field3) would be mapped using a ProcessMappingAssignment. The Conversion Model for the mapping would look like this:

Start ->

Get Item By Index on your split array to get the 0th, 1st, or 2nd item (as appropriate for field1, field2, field3) ->

End (with the output hooked up to your Get Item By Index output)

Now, take the output of the mapping and pass it to the Add Items to Collection comp (Add Items to Collection needs to be changed to point to an array of Blah vs an array of strings now, of course)"

SOLUTION