Reading data from csv with data in the multiple rows

[sriniprad08]

Hi Team,

I need help. I have a situation where i have csv file with column names in single row but data is spread across two rows. I need to bring the second row of data in the same line as first row .

ideally the yyy should be part of xxx and the "0";"0";"0";"30" should be in the next column.

id;"vse_id";"vse_id";"vse_date_created";"vse_date_planned";"vse_priority_id";"vse_object_id";"vse_creator_id";"vse_dt_id";"vse_desc";"vse_recur_interval";"ese_last_instance";"vse_external_id";"vse_changer_id"

41;"41";"1";"2004-05-24 07:07:30";"2004-06-04 00:00:00";"3";"2908";"30";"1";"xxx

yyy";"0";"0";"0";"30"

[sriniprad08]

The output should be

id

vse_id

vse_id

vse_date_created

vse_date_planned

vse_priority_id

vse_object_id

vse_creator_id

vse_dt_id

vse_desc

vse_recur_interval

ese_last_instance

vse_external_id

vse_changer_id

41

41

1

########

########

3

2908

30

1

xxxyyy

0

0

0

30

[atcodedog05 · Accepted solution]

Hi @sriniprad08 

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[jdunkerley79 · Accepted solution]

I would suggest:

First concatenate all the input into a single value with \n as the separator

Then using a Text to Columns tool split to rows on \n ignoring separators in quotes

You can then use a text to columns to split the columns up

Hopefully enough to get you going

[sriniprad08]

Hi @atcodedog05 

thank you so much. Actually the problem is little different there is a second row with yyy 0 ..0.. i need this to be in the first row as well. 

i have attached the sample output in my post.

Thanks,

Sriniv

[atcodedog05]

Hi @sriniprad08 

It has been added check the end of the output

I took your expected output into consideration while building the workflow 🙂

[atcodedog05]

Hi @sriniprad08 

Use this input tool config

So that your first row also comes as data and not as header.

[atcodedog05]

Happy to help 🙂 @sriniprad08 

Cheers and Happy Analyzing 😀

[sriniprad08]

Hi @atcodedog05 

Thank you. I am trying to upload a csv but not getting an option for "First row contains data". I am getting first row contains field names?

Also one more thing i observed my data is not only split across two rows but multiple rows. For example in my question the "xxx is split into two rows" like this "xxx

"yyy

"zzz 0, 0, 1,2

how to handle this?

thanks 

Sriniv

[sriniprad08]

Thank you @jdunkerley79  very helpful too.