CSV parsing with multiple values in one column

Question

I have a csv comma delimited file that I am trying to parse in Alteryx but running into a few edge cases. It has 8M+ rows and 34 columns.

Some of the columns have multiple values that are also separated by comma that I do not want to separate. I cannot put them in quotes, brackets or parenthesis. I have to work with this file.

Input:

Col1ab,cd,7,6,45,21,gh

Desired Output:

Col1Col2Col3Col4Col5Col6abcd7,64521gh

I am working with strings mostly here.
I know what column has multiple values (for example col3 here) if that helps.

Thanks in advance!

apathetichell · Answer

yeah - without an understanding of the semantic construction of the description field and an understanding of the universe -> I can't tell you if you will get to 70%. My assumption is that the value is extracting some type of information from the description field vs just splitting it. I also assume you can kind of do what you want using an LLM but -> costs.  You can try to post some more examples -> you can try to follow my split by text to columns/row mode /tile suggestion above - > and see if that gets you to 70% - if it doesn't it may explain what it is that makes sense/doesn't make sense about your data structure.

Maybe pytorch? https://stackoverflow.com/questions/71552396/extracting-data-logically-from-unstructured-text

https://www.reddit.com/r/LangChain/comments/1d3a3ee/parsing_unstructured_text/

langchain? chatgpt?

AncientPandaman · Answer

@agrawaluk 
if fixed field number and only 1 field of desc. it may use split of cutting front and back.

eg:

id, acc, desc, a, b, c

1, 23xx, lambda a, b: a + b, 1, 2, 3

split front

idaccdesc, a, b, c123xxlambda x, b: x + b, 1, 2, 3

then split end

idaccdescabc123xxlambda x, b: x + b123

else may use any unique column as anchor in regex,

like column of date, Y/N, or any other fixed length columns

agrawaluk · Answer

Agree with you.

The values are not always one term. They are I would say 20% times multiple values.

Thanks for your input. I appreciate it.