Importing a CSV with Date/Time and Scientific Notation

조회 수: 12 (최근 30일)
CW
CW 2019년 10월 29일
편집: Star Strider 2019년 10월 31일
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!
  댓글 수: 1
Walter Roberson
Walter Roberson 2019년 10월 29일
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

댓글을 달려면 로그인하십시오.

채택된 답변

Star Strider
Star Strider 2019년 10월 29일
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.
  댓글 수: 5
Jeremy Hughes
Jeremy Hughes 2019년 10월 30일
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
Star Strider 2019년 10월 30일
편집: Star Strider 2019년 10월 31일
@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.

댓글을 달려면 로그인하십시오.

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by