Exporting Matlab struct data to csv for Microsoft SQL Server to read

I have 1 day TAQ data for 5 stocks in 5 different .m files. I need to load the data with trade volume for each stock and which exchange the trade was performed on (This is a string value). I need to access this in SQL, where I would like a table containing of a column with Stock ID, one with Exchange ID, and one with Trade volume and then the corresponding values down the rows.

The matlab data are stored as a 1x1 struct with 4 fields, where each of these fields are again a 1x1 struct with an amount of fields. So say I had data for stock A in A.m, what I have done until now is loading the data with:

A = load(filelocation\filename);

And then getting the trade and exchange data I need from the struct by:

trA = A.TAQTr.volume;
exA = A.TAQTr.ex;

Note: The dimensions on trA are something like "293800x1 uint32", while on exA it would be something like "293800x1 char"

I have done this for all 5 stocks A, B, C, D, E. So I now have 10 column vectors containing all the data I need. Can I export this as one csv file so I can load it in SQL and if so how? I believe csvwrite only takes numeric values and exA's data type is char. Also I need a way to identify the stock with an "ID" for subsequent analysis in SQL.

The ending table I would like in SQL would look something like this:

![Example SQL]http://imgur.com/XxbZ34J

If you have other suggestions to do this in an easier way I welcome them. I have tried to be specific here, so hope it is allright and since the question is rather big, I would be happy even if you could just point me in the right direction.

Thanks //tralala

Answers


Approach 1: use table datatype and write to a csv

This requires a more recent version of Matlab. Try something like:

Atable = struct2table(TAQTr.A);
Atable.ID = repmat({'A'},height(Atable),1);
writetable(Atable,'Atable.csv');

Could also do more:

Btable = struct2table(TAQTr.B);
Btable.ID = repmat({'B'},height(Btable),1);
BothTables = [Atable; Btable];
writetable(BothTables, 'both.csv');
Fancy approach 2: directly connect to SQL server

With database toolbox and a JDBC driver, you may be able to directly insert your data too, but this may be a needlessly complicated approach.

That said, being able to execute SQL queries on a local database is imho a super cool way to import data into Matlab :P.

Approach 3: super basic

And if you have an earlier version of Matlab without tables etc..., it's not hard to write your own CSV export function using fopen and fprintf.

 fid = fopen('myfile.csv','w');
 if fid>0
     for k=1:n_rows
         fprintf(fid,'%s, %f, %f\n','A',data{k,1},data{k,2});
     end
     fclose(fid);
 end

Need Your Help

Java pointer behavior

java pointers

So I was experimenting with a permutation algorithm a few days back and discovered something.

memory mapped with mmap, and used with mprotect

c mmap mprotect

I have to do a provide memory regions for threads and apply basic concepts of memory management. The idea is create a Thread Local Storage, and manage the with write, read, and clone, and erase. The