Importing a CSV with Date/Time and Scientific Notation

9 次查看（过去 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 个评论
显示 3更早的评论
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 Center 和 File Exchange 中查找有关 Large Files and Big Data 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by