Solved

Convert list of data in CSV to table where first column has field names

ronmak

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.

icon

Best answer by gazzanator 28 May 2021, 03:36

View original

5 replies

G

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.

ronmak

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?

G

See if the attached workspace helps.

nielsgerrits
Rank
Userlevel 6
Badge +32

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.

ronmak

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

28,628
Posts
109,151
Replies
36,504
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.