How to process a csv file where numbers and text are at the same column?

Hello, everyone. I have the attached CSV file and there are some things that I have to do:

1. create a table,
2. read every text or number that they are, after the colon (:),
3. read all the rows from 1-37 and
4. transfer the variables names at the first row and the text and numbers to the second row meaning a table 2X37.

I kindly asking for help because I have totally 7400 files to process!!! Thank you in advance.

[Walter Roberson]

That appears to be the 1991 MSA/MAS spectral file format, as described at https://www.microscopy.org/resources/scientific_data/HMSAFileFormat-Presentation_2012_small.pdf

Can you output as .hmsa instead? If so then I see https://github.com/pyhmsa/pyhmsa-matlab which appears to be python code to convert .hmsa into matlab .mat files.

[Nivodi]

No, unfortunately, I can't.

Answer 1

[Image Analyst]

That's not a CSV file. You might have to write a custom reader for it. Here's a start:

fullFileName = fullfile(pwd, 'test2_seq.csv')
% Open the file.
fileID = fopen(fullFileName, 'rt');
% Read the first line of the file.
textLine = fgetl(fileID);
while ischar(textLine)
  % Read the remaining lines of the file.
  fprintf('%s\n', textLine);
  if contains(textLine, '#DATE', 'IgnoreCase', true)
    colonLocation = strfind(textLine, ':');
    semicolonLocation = strfind(textLine, ';');
    DATE = strtrim(textLine(colonLocation + 2 : semicolonLocation(1)));
  elseif contains(textLine, '#TIME', 'IgnoreCase', true)
  elseif contains(textLine, '#OWNER', 'IgnoreCase', true)
  elseif contains(textLine, '#NPOINTS', 'IgnoreCase', true)
  elseif contains(textLine, '#NCOLUMNS', 'IgnoreCase', true)
  elseif contains(textLine, '#XUNITS', 'IgnoreCase', true)
  elseif contains(textLine, '#YUNITS', 'IgnoreCase', true)
  elseif contains(textLine, '#DATATYPE', 'IgnoreCase', true)
  elseif contains(textLine, '#XPERCHAN', 'IgnoreCase', true)
  elseif contains(textLine, '#OFFSET', 'IgnoreCase', true)
  elseif contains(textLine, '#OFFSET', 'IgnoreCase', true)
  elseif contains(textLine, '#LIVETIME', 'IgnoreCase', true)
  elseif contains(textLine, '#SIGNALTYPE', 'IgnoreCase', true)
    % etc.
  end
  % Read the next line.
  textLine = fgetl(fileID);
end
% All done reading all lines, so close the file.
fclose(fileID);

[Nivodi]

Thank you very much, I run the code but it didn't work. The error message is this:

#FORMAT : EMSA/MAS Spectral Data File;df;adf Undefined function 'contains' for input arguments of type 'char'.

Error in test2 (line 27)

if contains(textLine, '#DATE', 'IgnoreCase', true)

[Walter Roberson]

Which MATLAB release are you using?

[Nivodi]

R2015a version

[Image Analyst]

Use strfind() instead of contains(). Instead of

if contains(textLine, '#DATE', 'IgnoreCase', true)

use

if ~isempty(strfind(textLine, '#DATE'))

[Nivodi]

THANK YOU! It worked! You are a genius! One last question.... How can I save the results (probably as a table) so I can process it or add it to another table? In the workspace are these (see the attached image)

[Image Analyst]

Use the table() command to construct a table of known fields first. Then go through the loop assigning things. Is the table going to hold data from multiple files, or just one file? I would think a structure would be a better data type to hold this information.

[Nivodi]

yes, I want, gradually (because I think that it is impossible to do it at once), to add all these 7400 files, so I could create a table(T1) 7400Xa. Finally, I have to add this T1 with another table(T2) which is also 7400Xb and create a new "hypertable"(3).

[Nivodi]

I tried this:

T2=table('Date', 'TIME','BEAMKV')

and the "answer" from the command window was:

Error using table (line 281)
Wrong number of arguments.

Error in testXSP (line 9)
T2=table('Date', 'TIME','BEAMKV')

Caused by:
    You may have intended to create a table with one row from one or more variables that are character strings.
    Consider using cell arrays of strings rather than character arrays. Alternatively, create a cell array with one row, and convert that to a table using CELL2TABLE.

[Walter Roberson]

You need to define types for the entries; see https://www.mathworks.com/matlabcentral/answers/112462-how-can-you-create-an-empty-table-object-with-non-zero-rows-or-non-zero-columns#answer_256749

For example:

T2 = table( datetime(zeros(0,3)), duration(zeros(0,3)), 0, 'VariableNames', {'Date', 'TIME', 'BEAMKV'} );

[Nivodi]

I tried to do it exactly as the link describes but it didn't work......

I did this:

sz=[2 4]

VarTypes={'char'}

VariableNames={'anode_z','BEAMKV','DATATYPE','DATE'}

T=table('Size',sz,'VariableTypes',VarTypes,'VariableNames',VariableNames)

and I got this:

Error using table (line 281)
Invalid parameter name: Size.

Error in testXSP (line 161)
T=table('Size',sz,'VariableTypes',VarTypes,'VariableNames',VariableNames)

Caused by:
    You may have intended to create a table with one row from one or more variables that are character strings.
    Consider using cell arrays of strings rather than character arrays. Alternatively, create a cell array with
    one row, and convert that to a table using CELL2TABLE.

I don't know what I am doing wrong.....It can't be so difficult...

[Walter Roberson]

Specifying the variable types and table size only became possible as of R2018a. For earlier versions you have to use the technique I showed.

[Nivodi]

Ok, Walter. Thank you!

[Nivodi]

Image analyst, I tried to apply to code that you send to a file (I can't attach it because its format is not supported; its formation is as the file that I attached at the beginning and it can open with WordPad), and there is a problem with the semicolumLocation. I tried but the only thing that I managed was to show up the value from the date only (I wrote "semicolumLocation+15" and "semicolonLocation = strfind(textLine,':')"). Thank you in advance.

[Walter Roberson]

You can zip the file and attach the zip

[Nivodi]

Thank you, Walter! :))

Answer 2

[Walter Roberson]

projectdir = '.';    %directory the files are in
dinfo = dir( fullfile(projectdir, '*.xsp') );
filenames = fullfile(projectdir, {dinfo.name});
nfiles = length(filenames);

table_so_to_speak = cell(nfiles, 1);

for K = 1 : nfiles
  S = fileread( filenames{K} );
  parts = regexp(S, '^#?(?<field>\w+)\s*: *(?<val>.*?)\r?$', 'names', 'lineanchors', 'dotexceptnewline');
  table_so_to_speak{K} = [{parts.field}; {parts.val}];
end

However, I suspect you want more like

projectdir = '.';    %directory the files are in
dinfo = dir( fullfile(projectdir, '*.xsp') );
filenames = fullfile(projectdir, {dinfo.name});
nfiles = length(filenames);

table_so_to_speak = cell(nfiles+1, 1);

for K = 1 : nfiles
  thisfile = filenames{K};
  S = fileread( thisfile );
  [~, basename] = fileparts(thisfile);
  parts = regexp(S, '^#?(?<field>\w+)\s*: *(?<val>.*?)\r?$', 'names', 'lineanchors', 'dotexceptnewline');
  if K == 1
    table_so_to_speak(1,1:length(parts)+1) = [{'filename'}, {parts.field}];
  end
  table_so_to_speak(K+1,:) = [{basename}, {parts.val}];
end

[Nivodi]

Thank you Walter!! it worked! Of course, I don't understand even a single thing of the code... So...If you would like and if I am not asking too much when you have plenty of free time....could you explain the code, please? This code is too high level from me and it will take me an eternity to understand it by myself.

[Walter Roberson]

The first few lines use dir() and fullfile to get a complete list of xsp filenames in the given directory.

After that you loop over each file.

You read an entire file at one go as a continuous string. Then you do some pattern matching on the string. The pattern matching looks for an optional # at the beginning of a line, followed by any combination of digits and letters and underscore (a "word"), followed by spaces, followed by colon, and stores the word in a structure field named "field". Then it continues looking on the line for spaces, followed by whatever is there, until the end of line that might be preceded by a carriage return, and it stores that "whatever is there" in a structure field named "val". It keeps doing that for the entire string that is the file contents, so it is building up a structure array of field names and corresponding values.

Once the structure array is found, if this is the first file then it stores the field names read in as the first row in a cell array; it does not bother to store the field names for files after that because it assumes they are the same in the same order as the first file. Regardless of whether it was the first file or not, it stores the filename and the field values as a row in the cell array.

This reading and text analysis and storing is done for all of the files.

[Nivodi]

Thank you very much Walter! The produced cell "table_so_to_speak" I converted it to a table, the first row can be recognized as variables? If not, how can I do that?

[Walter Roberson]

You could adapt the second half of my code at https://www.mathworks.com/matlabcentral/answers/285186-importing-data-without-knowing-number-of-columns#comment_368710

I will work on creating a separate function for that, but I have some other things to do at the moment.

[Walter Roberson]

Okay, I have enclosed code to turn the cell array of strings into a table. You would use it like

actual_table = cellstr2table(table_so_to_speak);

This will examine all fields and attempt to convert strings representing numbers into numbers, and attempt to convert dates and times into datetime or duration objects.

The code does not handle missing (empty) slots in columns intended to be numeric. However, it will convert to duration or datetime if at least one entry in the column converts.

[Nivodi]

>> testXSP_xsp Error: File: testXSP_xsp.m Line: 18 Column: 1 Function definitions are not permitted in this context.

Is it caused because I have R2015a version?

[Walter Roberson]

I do not know what is in your testXSP_xsp.m file. I did not suggest putting a function inside a script file; you need R2016b or later to have a function inside a script file. Put cellstr2table.m in a file of its own.

[Nivodi]

Ok Walter. Thank you very much for your help!

[Nivodi]

Hello Walter,

I would like to ask you something. In the code that you gave me

projectdir = '.';
dinfo = dir( fullfile(projectdir, '*.xsp') );.......

How can I put the files in the table "table_so_to_speak(K+1,:)" in order? (Seq_09032017_B6, Seq1_09032017_B6, Seq2_09032017_B6,Seq3_09032017_B6 ect)?

Thank you for one more time!

[Image Analyst]

If your operating system is not returning the filenames in a sorted order, then you can call sort() to sort them alphanumerically:

filePattern = fullfile(projectdir, 's*.xsp'); % Whatever...
fileInfo = dir(filePattern)
% Sort according to base file name.
baseFileNames = {fileInfo.name}
[sortedNames, sortOrder] = sort(baseFileNames);
% Apply the same sorting order to the fileInfo structure array.
fileInfo = fileInfo(sortOrder)

[Walter Roberson]

See https://www.mathworks.com/matlabcentral/fileexchange/47434-natural-order-filename-sort for some flexible code for sorting filenames.

[Nivodi]

Thank you very much both of you!!