Importing a CSV with Date/Time and Scientific Notation

I am trying to write a code to import the output from Thorlabs Optical Power Meter (PM100USB with S120C sensor) at a series of locations from translation stages using Thorlabs Kinesis.

The CSV output from Optical Power Meter has been problematic, considering it has date, time, and scientific notiation values.

dlmread, shown below:

opt_n2_n2 = dlmread('sample_test_11.csv',';',16,1)

produces this error message:

% Error using dlmread (line 147)
% Mismatch between file and format character vector.
% Trouble reading 'Numeric' field from file (row number 1, field number 3) ==> /29/2019;09:52:19;2.268E-09\n

I have attached a sample of the output and shown below are a few other attempts that I have made to import the data, all of which have been unsuccessful.

%opt_n2_n2 = fileread('sample_test_11.csv')
%opt_n2_n2 = textscan('sample_test_11.csv','D16:D35')
fid = fopen('sample_test_11.csv', 'rt');
%C = textscan(fid,'%f%f%f%f','HeaderLines',6);
m = textscan(fid,'%f%f%f%s%s','HeaderLines',2, 'Delimiter',';');
fclose(fid);
%headers = string( strsplit( fgetl(fid), ';') );
%data = cell2mat( textscan(fid, '%f%f%f%f%f', 'Delimiter', ';', 'collectoutput', 15) );

I was successful at converting the csv to an Excel file and importing the data, but considering that each experiment will involve 64 csv files I would prefer to automate this process through Matlab. I was also able to force the optical intensity data through manually importing it through the workspace, but once again I would like to automate this process.

The utlimate goal after importing the data is to compile it into a single one column matrix/variable, then plot it as a surface. I was able to generate the surface plot, showing optical intensity based on position, after the Excel import.

Any advice on simplifying/automating this process for facilitated reproducibility would be very much appreciated! Please let me know if there are any questions, and thanks in advance!

[Walter Roberson]

We recommend using readtable()

Using textscan() is also possible, but you should use %D format specifications to read the dates and times. Or at the very least you should add / and : to your list of delimiters and read 8 numbers per line

Answer 1

[Star Strider]

Unfortunately, readtable has problems with your file. (I tried that first.)

That aside, working with your file as a table is definitely the way to go!

Try this:

[D,S] = xlsread('sample_test_11.csv');
Vars = strsplit(S{15},';');
for k = 16:size(S,1)
    DataC = strsplit(S{k},';');
    Sampl(k-15,:) = str2double(DataC{1});
    DatTm(k-15,:) = datetime([DataC{2:3}]);
    Pwr(k-15,:) = str2double(DataC{4});
end
T1 = table(Sampl,DatTm,Pwr, 'VariableNames',{Vars{1}, 'DateTime', Vars{4}});

producing (in full):

T1 =

  20×3 table

    Samples           DateTime          Power (W) 
    ________    ____________________    __________

        0       29-Oct-2019 09:52:18    2.3571e-09
        1       29-Oct-2019 09:52:19     2.268e-09
        2       29-Oct-2019 09:52:20    2.2881e-09
        3       29-Oct-2019 09:52:21    2.2332e-09
        4       29-Oct-2019 09:52:22    2.2512e-09
        5       29-Oct-2019 09:52:23    2.2388e-09
        6       29-Oct-2019 09:52:24    2.2589e-09
        7       29-Oct-2019 09:52:25    2.2355e-09
        8       29-Oct-2019 09:52:26    2.2634e-09
        9       29-Oct-2019 09:52:27    2.2299e-09
       10       29-Oct-2019 09:52:28    2.2581e-09
       11       29-Oct-2019 09:52:29    2.2343e-09
       12       29-Oct-2019 09:52:30    2.2812e-09
       13       29-Oct-2019 09:52:31    2.2533e-09
       14       29-Oct-2019 09:52:32    2.2254e-09
       15       29-Oct-2019 09:52:33    2.2719e-09
       16       29-Oct-2019 09:52:34    2.2321e-09
       17       29-Oct-2019 09:52:35    2.2332e-09
       18       29-Oct-2019 09:52:36    2.2578e-09
       19       29-Oct-2019 09:52:37     2.241e-09

That should do what you want.

[CW]

Thank you so much! I had also tried creating a table previously but was having trouble translating it to a manageable format for plotting, manipulation, etc. What you have provided works perfectly and I really appreciate your help!

[Star Strider]

As always, my pleasure!

The problem I had with readtable:

T1 = readtable('sample_test_11.csv', 'HeaderLines',14, 'Delimiter',';');

is that it imported the dates as a datetime array and imported the times as a duration array. For some reason, I could then not concatenate them to create a unified datetime array, even when I tried to convert the duration array to a datetime array. (I consider this a bug.) So the loop was the only option.

[Walter Roberson]

Add the datetime and duration columns to get absolute time.

[Jeremy Hughes]

Hi Star Strider,

You want to add the duration to the datetime. Concatination is trying to make a nx2 array

T.Datetime = T.Date + T.Time;

Concatination is trying to make a nx2 array.

[Star Strider]

@Walter & @Jeremy — Thank you both!

I definitely didn’t see that in the documentation.

EDIT — (31 Oct 2019 at 01:55)

However, I did eventually find it in: Add CalendarDurations to Datetime Array. It just wasn’t where I was looking for it.