MATLAB Answers

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

46 views (last 30 days)
Jonathan
Jonathan on 7 Jan 2019
Edited: OCDER on 7 Jan 2019
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
Jonathan
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)

Community Treasure Hunt

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

Start Hunting!

Translated by