csv Data Stored in Data Table Cell

lou

I'm looking for help to extract a csv file that is stored within a cell in a data table.  I want to setup a script to iterate through this column and either extract to a new data table or save the cell contents as a csv file (that I can later open as a data table in JMP).  I've briefly looked at text to columns; however, there is so much data contained within each cell, this approach does not seem practical.  There are about 45 rows and 5 columns of header data. Then, the data consists of thousands of rows and 46 columns.  Any thoughts on how to handle this will be appreciated!  Lou

 

 

Mauro_Gerber

Hello

 

This is what i did:

Import the text and substitute the delimeter character to parse it as a Matrix (I only have numbers, you may have to change it so its' a list. replace the ";" with space and the line end entry (could also be \!n or \!cr) with ",":

 

text_raw = column(dt,data_list[1])[1];
temp = Substitute( Substitute( text_raw, ";", " " ), "\!r", "," );// prepare for parsing
raw_data = Parse( Eval( "[" || temp || "]" ) ); // text to matrix
n_data = nitems(raw_data);
			

- generate a new table with all the rows you need

- Add the number of rows of the matrix/list with addrows and fill the contend. Maybe by column of the list with an aditional inner loop

- copy data from the original table like test ID to all the rows so you have all the contend you need.

 

repeat this for each row from the original table.

It may be a bit slow and you may speed it up by knowing in advance what you will fill (alwasy the same number of rows to add).

 

there may be a direct way from a raw text string to data table but I wasn't able to find it.

 

Another way would be to save the contend of the row as a text file and re-import it with the open command.

This may be easyer to code but may take longer.