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

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

'

[Stephan]

Can you attach a .mat file of the output you get?

[Jonathan]

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]

@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]

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]

@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]

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'];

Answer 1

[OCDER]

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

[Jonathan]

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.