MATLAB Answers

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

27 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

Show 3 older comments
Jonathan
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
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
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)

Sign in to answer this question.


Translated by