Comma Separated Table

[Inactive User]

Hello!

I am looking to find a way to create a table from a data set that is in a single cell, separated by commas (including the headers!).

I don't need a workflow built out, though I am looking for advice on tools that can accomplish this. For example, I have the data set connected to a Text to Columns tool and I have it set up to rows (which created 200+ rows), I tried to connect it to another Text to Columns to then output the rows as columns, though there doesn't appear to be a way to determine what I want my headers to be. I used the formula tool to create new headers, though can the formula tool filter what data is within each column? I tried a few expressions, though it either returns as null or the first value in the data set.

[Luke_C]

Hi @Inactive User 

Do the headers end up in the first row of the data? Or can you sort in a way that gets them there? If so you can use the dynamic rename tool to use the first row of data as the header.

https://help.alteryx.com/current/designer/dynamic-rename-tool

[Inactive User]

Unfortunately the headers are further down in the data set, starting at row 15 when I parse the data into rows. The first 14 rows of data aren't needed however.

[Luke_C]

Hi @Inactive User 

Then you can use a sample tool to skip the first 14 rows of data, followed by a dynamic rename to take the new first row as the headers

[Inactive User]

The data I am using is from Qualtrics, so it's a single line of all headers and data separated by commas. I was close to reaching a solution, though again I got stuck with getting specific data in specific columns. I attached a picture of the raw data I am trying to parse into a table from Qualtrics. The second picture is one of my many workflows to try and get that data into a table using Alteryx.

Is there a way to filter the data for each column? I feel like I could have a finished table if I could just filter the data, or if there is somehow a better way to tell Alteryx where to put the data for each column

[apathetichell]

Could you post a line of sample data with the headers? If so I think someone can mock this up for you... the keys are usually something like:

1) separate the headers from the data.

2) add a record id to the data.

3) text to columns by row

add a dummy column variable and crosstab back (again with grouping by record id)

On the headers do the same. Your headers should be the first row of data now and you union the two together and feed through a dynamic rename.

[Inactive User]

Here is what the data looks like, or should look like in the table with the headers (I also attached it).

In another workflow I tried, I found an old video on Alterys' youtube channel from 2014 which filtered out the column headers, though this is from a JSON parse that already is formatted into a table. The main issue I am having is getting data into a table from data that isn't formatted already.

Let me know if there is anything else I can provide, appreciate the help!

[Inactive User]

Here is where I am at currently. I think I managed to filter out the header information, though now I am lost on how to populate the columns with the respective data. I attached an example of what I need to get the table to look like (not needed to be exact) along with my current workflow. I have conditions in the filter to look for each header name using:

Left([Data], 10)=='ResponseID'
OR
Left([Data], 11)=='ResponseSet'
OR
Left([Data], 4)=='Name'
OR
Left([Data], 6)=='Status'
OR
Left([Data], 9)=='StartDate'
OR
Left([Data], 7)=='EndDate'
OR
Left([Data], 8)=='Finished'

[apathetichell]

Hi,

I can't edit .png data in Alteryx - so could you load something in .xlsx or .csv? I think there's probably a more dynamic way to do your formulas but I can't implement it without data. Sorry,

[Inactive User]

Hello,

Here is the data I am working with in an .xlsx file