MATLAB Answers

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

18 views (last 30 days)
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:
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


Show 3 older comments
Jonathan on 7 Jan 2019
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 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 on 7 Jan 2019
The data in the actual http response is covered by NDA...
The string is assigned from:
cmd = 'curl -k';
[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 on 7 Jan 2019
Edited: OCDER on 7 Jan 2019
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);
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);
Table = readtable('temp.csv');
If you have the Financial Toolbox, then you can use the candle function

  1 Comment

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