Parsing CSV Data into Individual Tables

[Comp_c4]

My data is from a CSV that dumps into one giant excel table. There are ~100 tables that I need to break into individual tabs & tables in a workbook. Once this is done, I can analyze and adjust the data. I will then need to recombine the data back into one giant excel table to resave as a CSV for import.

I'm stuck parsing the data into individual tabs/ tables....Help! Suggestions welcome!

The data is formatted as shown below:

• File_Info isn't really a table as it has basic file info
• The Data is formatted as Long Text currently
• Since the data comes from one giant table, there can be null data in the table headings and table data
  • i.e. the individual tables once parsed are different sizes.

File Info_1	File Info_2	File Info_3	File Info_N
Table_1	null	null	null
Table_1 Headings	Table_1 Heading_1	Table_1 Heading_2	Table_1 Heading_N
Table_1 Data_Row_1	Table_1 Data	Table_1 Data	Table_1 Data
Table_1 Data_Row_2	Table_1 Data	Table_1 Data	Table_1 Data
Table_1 Data_Row_N	Table_1 Data	Table_1 Data	Table_1 Data
Table_2	null	null	null
Table_2 Headings	Table_2 Heading_1	Table_2 Heading_2	Table_2 Heading_N
Table_2 Data_Row_1	Table_2 Data	Table_2 Data	Table_2 Data
Table_2 Data_Row_2	Table_2 Data	Table_2 Data	Table_2 Data
Table_2 Data_Row_N	Table_2 Data	Table_2 Data	Table_2 Data
Table_N	null	null	null
Table_N Headings	Table_N Heading_1	Table_N Heading_2	Table_N Heading_N
Table_N Data_Row_1	Table_N Data	Table_N Data	Table_N Data
Table_N Data_Row_2	Table_N Data	Table_N Data	Table_N Data
Table_N Data_Row_N	Table_N Data	Table_N Data	Table_N Data

 

[johnny51981]

I've tried to give a more detailed response, but the coffee isn't producing results just yet.

However, I would use helper columns to identify where the table changes occur, else null. Then fill them down, they will fill down to the next non null table change.
Filter to a single table name in this new helper column.
Remove helper column
Filter out table name row
Promote Table_X Headings to Headers (they should be in row 1 at this point)
Adjust/augment as needed
Repeat

 

[Comp_c4]

I've tried to give a more detailed response, but the coffee isn't producing results just yet.

However, I would use helper columns to identify where the table changes occur, else null. Then fill them down, they will fill down to the next non null table change.
Filter to a single table name in this new helper column.
Remove helper column
Filter out table name row
Promote Table_X Headings to Headers (they should be in row 1 at this point)
Adjust/augment as needed
Repeat

Not entirely sure I follow all your method, but it starts with what I currently do: helper columns followed by a very manual effort. I am looking to find a more automated method of parsing the tables into tabs given there are so many of them. If it helps for context, the data is from a Primavera P6 file.