How do I convert a CSV formatted string to a table or timetable?

2019 1 7
1 个回答
回答已采纳
更新时间：2019 1 7
17 次查看（30 天）

0 个投票

How do I convert a CSV formatted string to a table or time series table, so it then can be plotted as a candle stick plot? The string is the output result of a http: request and could look something like:
'timestamp,open,high,low,close,volume
2019-01-07 14:45:00,102,102.4,103.23,103.00,1955320
2019-01-07 14:46:00,102.3,102.2,102,6,103.9,267856
'

6 个评论
显示 4更早的评论 隐藏 4更早的评论

Stephan
Stephan 2019-1-7
Can you attach a .mat file of the output you get?
Jonathan
Jonathan 2019-1-7
Hi, thank you for the quick reply. The string is still in memory where I would prefer to convert it to a timetable, so there isn't a file.
Jan
Jan 2019-1-7
@Jonathan: You can simply create such a file using the save command. This makes it easier to write an answer, because we do not have to guess, how the data look like exactly.
textscan should be able to solve the problem efficiently.
Jonathan
Jonathan 2019-1-7
My mistake, I thought you were referring to where the output from the http: as residing. Due to an active NDA, I'm not able to give the actual data.
Each line is '\n' terminated. The first line describes each field. Each line contains 6 fields with comma delimiters. The first field is date time, 2nd,3rd,4th,5th are real numbers, the last is an integer.
I did look at text scan, and I was looking for a better solution as textscan chokes on the first line which has the field descriptors.
Jan
Jan 2019-1-7
编辑：Jan 2019-1-7
@Jonathan: An NDA?! You could post the above mentioned example as MAT file for example. This is not a new information, but the readers do not have to retype it, when they try to create an answer. This is not much work, but it can be avoided.
Here is a Matlab command, which creates the data:
str = ['timestamp,open,high,low,close,volume', char(10), ...
'2019-01-07 14:45:00,102,102.4,103.23,103.00,1955320', char(10), ...
'2019-01-07 14:46:00,102.3,102.2,102,6,103.9,267856', char(10)]
You are welcome :-)
It is confusing, that the 2nd line contains 6 elements, and the 3rd 7. Maybe you mean "102.6" instead of "102,6"?
Jonathan
Jonathan 2019-1-7
编辑：Jonathan 2019-1-7
The data in the actual http response is covered by NDA...
The string is assigned from:
cmd = 'curl -k https://www.specialwebsite.com/query....';
[status, A] = dos(cmd);
string A has the output from the http which is a single string with each line of the csv format terminated with '\n'. Looks like
A = ['timestamp,open,high,low,close,volume\n2019-01-07 14:45:00,102,102.4,103.23,103.00,1955320\n2019-01-07 14:46:00,102.3,102.2,102,6,103.9,267856'];

请先登录，再进行评论。

请先登录，再回答此问题。

 采纳的回答

OCDER
OCDER 2019-1-7
编辑：OCDER 2019-1-7

2 个投票

NEW ANSWER
Try a variation of this, where your CSV-formatted string is directly read and converted to a table.
TextStr = ['timestamp,open,high,low,close,volume', newline, ...
'2019-01-07 14:45:00,102,102.4,103.23,103.00,1955320', newline, ...
'2019-01-07 14:46:00,102.3,102.2,102.6,103.9,267856', newline]
HeaderFmt = '%s%s%s%s%s%s';
DataFmt = '%D%f%f%f%f%f';
Fields = cellfun(@(x) x{1}, textscan(TextStr, HeaderFmt, 1, 'Delimiter', ','), 'un', 0);
Data = textscan(TextStr, DataFmt, 'Headerlines', 1, 'EndOfLine', newline, 'Delimiter', ',');
Table = table(Data{:}, 'VariableNames', Fields);
-----
OLD ANSWER
Why not save the CSV-formatted string as a real .csv file, and then reload via readtable?
TextStr = ['timestamp,open,high,low,close,volume', char(10), ...
'2019-01-07 14:45:00,102,102.4,103.23,103.00,1955320', char(10), ...
'2019-01-07 14:46:00,102.3,102.2,102.6,103.9,267856', char(10)]
FID = fopen('temp.csv', 'w');
fprintf(FID, TextStr);
fclose(FID);
Table = readtable('temp.csv');
If you have the Financial Toolbox, then you can use the candle function
https://www.mathworks.com/help/finance/candle.html

1 个评论
显示 -1更早的评论 隐藏 -1更早的评论

Jonathan
Jonathan 2019-1-7
Yes, that would work but you pay a cost in time as the data would have to be written out to a file and then read back in to convert it. The actual output can be quite long. And the application will have a large number of http calls per min. Granted, it is likely that the buffer in the hard drive could catch the turnaround. Better would be a vitural hard drive that would reside in memory.
The data could also be generated as JSON formatted data in the return string, but I haven't found a solution for it.

请先登录，再进行评论。

更多回答（0 个）

类别

在 帮助中心 和 File Exchange 中查找有关 String Parsing 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by