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

40 views (last 30 days)
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 Comments
Jan
Jan on 7 Jan 2019
Edited: Jan on 7 Jan 2019
@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 on 7 Jan 2019
Edited: Jonathan on 7 Jan 2019
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'];

Sign in to comment.

Accepted Answer

OCDER
OCDER on 7 Jan 2019
Edited: OCDER on 7 Jan 2019
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
  1 Comment
Jonathan
Jonathan on 7 Jan 2019
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.

Sign in to comment.

More Answers (0)

Categories

Find more on Data Type Conversion in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!