Skip to main content
  • EN

I have a CSV with data exported from an old program.  It created two columns.  The first column contains field names and the second contains the values.  It looks a bit like this:

FirstName, Paul
LastName, Smith
Birthdate, 1/1/2020
CellPhone, 123-456-789
FirstName, Mary
LastName,Smith
CellPhone, 123-555-1212

Each group starts with "FirstName", but each person in the data does not necessarily have data for each of the fields.  (e.g., Mary Smith doesn't have a birthday in the system).  I can use a duplicate filter to get a list of all potential fieldnames, but how do I do the next step?  I want to create a table that has all the data for each person in the list.   In the simplified example, two rows would result.

There are two things to do here, first is to group all records for the same person together. For this I would use a counter that increments whenever FirstName is seen and the new count is stored with a VariableSetter. Then for every record a VariableRetreiver is used to get that count.

If your columns are called column1 and column2 then use an AttributeCreator with

New Attribute se to @Value(column1) and value set to column2.

Then use an Aggregator grouping by the counter.

Thanks. This helps and I have managed to add a column with an ID that repeats until the next FirstName appears.

I also played around learning about AttributeCreator, but can't quite wrap my head around how to make it all happen.

I got as far as using a DuplicateFilter to get all of the attribute names, which I wrote out to a spreadsheet which now has the first row containing the attribute names.

I am stuck on the next part which would go through each line in the source table, read the attribute name and value and insert it into a new table. Should I start with some kind of template with the ID column populated so that each attribute will get placed in the right row?

See if the attached workspace helps.

See if the attached workspace helps.

Nice to see other type of solutions.

As an alternative you can assign group ID's like you can do it in Excel: If AttributeName = FirstName then previous ID+1, else previous ID. This can be done in the AttributeCreator using Adjacent Feature Attributes.

See if the attached workspace helps.

Thanks @Gary Nicholson​ for the workspace. I was surprised at how many step were involved but after spending some time on it and looking more closely at documentation I finally got my translation working. Well, almost. I will post a new thread on an issue I have converting dates.

Reply


Community Stats

30,162
Posts
114,512
Replies
37,820
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.