Reading a complex CSV file and converting to MAT

Hi all,

I'm trying to read in a large CSV file that has been exported from Vicon Nexus -- with 3D motion capture data -- with as minimal modification to the original exported file as possible (TestData.CSV accessed here -- attache) .

From what I've read, .MAT file would be the best file to convert to but because of the header info and the 2 separated sets of data in the same file -- "TRAJECTORIES" and "FORCE PLATES" -- I believe the only option I have is to use textscan. I need to use both sets of data and index an unknown number -- experimenting -- of columns & row.

Eventually I only want to read in the variables that are needed, which are located in different columns and in both of the two different data sets. The two different data sets are best exported into the 1 CSV file to contain all the variables needed for further analysis down the line. Is it possible to use textscan in this case? Any help would be much appreciated. Cheers.

[Star Strider]

I would use xlsread (link) or readtable (link).

[per isakson]

Have you seen

• https://docs.vicon.com/display/Nexus25/Modeling+with+MATLAB
• https://www.youtube.com/watch?v=XQawjlGmFY4 ?

[James]

Sorry I should have mentioned I only have Nexus 1.8.5 so I hadn't investigated more previously. But ill look into it.

[per isakson]

I didn't know that Vicon Nexus exists, but my first reaction was to google "Vicon Nexus matlab". These two links were among the top five. They show an ambitious support/integration of Matlab. Maybe they also provide means to transfer data via files to Matlab.

"From what I've read, .MAT file would be the best file to convert to" yes, why not, but you have to it in steps.

• Decide how you want to store the data in variables in Matlab
• Read and parse the csv-file
• Save to a mat-file

Regarding the second step see https://se.mathworks.com/matlabcentral/answers/365496-parse-text-file#answer_290758

>> str = fileread( 'TestData.csv' );
>> whos str
  Name      Size                   Bytes  Class    Attributes
  str       1x2015360            4030720  char       

>> [ n,s,r] = xlsread( 'TestData.csv' );
>> whos n s r 
  Name         Size              Bytes  Class     Attributes
   n            0x0                   0  double              
   r         3882x1             4449976  cell                
   s         3882x1             4449976  cell                

>> s(20)
ans = 
    'Field #,X,Y,Z,X,Y,Z,X,Y,Z,X,Y,Z,X,Y,Z,X,Y,Z,X,Y,Z,X,Y,…'

xlsread seems to read your file line by line without parsing anything. (Matlab R2016a)

[per isakson]

One more link Third-Party Products & Services, Vicon Nexus

"Nexus has an Offline Data SDK specifically designed to allow users to push and pull specific items of data into MATLAB" Whatever that is?

[James]

Thanks a whole lot! readtable seemed to do the trick! Because of admin issues with running Nexus 1.x.x on Windows 10, I couldn't attempt to use the Matlab integration into Nexus. Cheers again!

Answer 1

[Jule]

Hi James!

I am dealing with the same kind of csv-files and was wondering if you could help me out here. I managed to import the file using readtable. However, now I can't find a possibility to detect where the needed variables are in this table.

   find(T=={'FORCE PLATES'})

gave the error 'Undefined operator '==' for input arguments of type 'table'.' Hope you or someone else here can help me out.

Thanks!

[James]

Hi Jule,

I can't verify this for sure, but I'm sure the Matlab plugin for Nexus 2.x would be the most efficient method but I didn't have that luxury unfortunately.

I used the method below. It's probably not the fastest approach but I spent so long on it that I can't be bothered adjusting it for now haha. Bear in mind that as pointed out by @per isakson above, the file needs to be small enough to fit in memory so it might need to be trimmed beforehand, and I'd suggest you trim it anyways as it can take a multiple of seconds to read the table into matlab and then convert it to a cell array if the trial is substantially long.

% 1) Get filename/s (I assume this is done)

% 2) Import CSV using 'readtable', which converts the CSV data into type 'table' in Matlab

t = readtable(file_name);

% 3) Convert table into a cell array

tc=table2cell(t);

% 4) Search cell array for variable/s e.g. 'FORCE PLATES'

[row, col, vector] = find(strcmp(tc, variable'));

% 5) Use row and col to index the cell array as needed

[Jule]

Hi James, thanks a lot for your quick answer! As I don't have access to Nexus either, I also need to work with Matlab. How do you trim csv files? I have over 300 files that want to be analysed... Also, did you manage to import it as csv? I first had to save it as a xlsx because with the csv file, matlab says

'Error using readtable (line 198) Reading failed at line 153. All lines of a text file must have the same number of delimiters. Line 153 has 22 delimiters, while preceding lines have 1.

Note: readtable detected the following parameters: 'Delimiter', ',', 'HeaderLines', 151, 'ReadVariableNames', false, 'Format', '%f%q''

Thanks again! Jule

[James]

Re Trimming: I incorrectly presumed you were using Nexus as well and that you still had the motion capture files before exporting them into ASCII/CSV. That's where I had to trim the files i.e. Nexus, not Matlab.

Re Importing: I didn't have to save it to xlsx. readtable worked fine with the CSV files.

Re Error: I think I encountered a similar error. You'd have to look at that line/row and compare it to the others to try and understand what the error is referring to, but it looks like in line 153, the delimiter readtable uses to differentiate lines is used 22 times in that one line -- I could be totally wrong in my interpretation though. Have you read the documentation using the links above? If you upload one of the files I could have a look when I get time. Other tips to help debug:

(1) Try using readtable with at least 3 files, find the pattern in the errors if it occurs in all files tested, and use the pattern to hone in on the actual problem

(2) Make a copy of the files, delete the erroneous line/row -- and maybe even those below -- and see what effect that has, and use that to hone in on the actual problem.

[Jule]

Re Error: Since I only have real patient data, I'm not allowed to upload it here. But my csv is very similar to yours. I have the sections ANALYSIS, EVENTS, ANALOG and FORCE PLATE. With every file, the error occurs at the line under 'ANALOG'. Matlab is right saying that there is only one comma ('800.000000,Hz'), however the same appears in the line under 'TRAJECTORIES' (l. 18 in your example file) without producing an error there. Interestingly, if I delete the problematic line, readtable works but deletes everything above said line. Matlab seems to think everything above this is header. That does not happen with the xlsx files.

[James]

Firstly, you could try and edit the CSV files to remove the unwanted comma/s and then try readtable again.

Secondly, as long as you can get the data into a cell array then you can index that, so if you can already do that then it may pay to carry on.

Hope that helps.