topic Re: csv Data Stored in Data Table Cell in Discussions

csv Data Stored in Data Table Cell

lou — Sat, 10 Jun 2023 23:27:18 GMT

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

Re: csv Data Stored in Data Table Cell

txnelson — Thu, 18 Mar 2021 10:11:56 GMT

In the file that has the cvsdata column, does that column have the string "Filename:," at the beginning of each cell, or are you using it just as a place holder for what will be the actual path to the file?
Are there always the same number of header lines for each csv file, or will a script have to be written to get rid of the header records. Or is there information in the header records that need to be retained in the resulting data table? Or do the header records contain the names of the 46 columns?
Can you provide a sample csv file?
Can you provide an example of what the output table needs to look like?

Re: csv Data Stored in Data Table Cell

Mauro_Gerber — Thu, 18 Mar 2021 08:19:59 GMT

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.

Re: csv Data Stored in Data Table Cell

lou — Tue, 23 Mar 2021 12:18:35 GMT

Thanks!