Matrix to Table format

Question

Brian 2012-6-7

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/40550-matrix-to-table-format

I have a very large file that received in a matrix format. Because I'm going to write the data to a SQL database table I need to convert it to a table format. What I have is data that looks like this.

FinancialTicker,Date,Factor1,Factor2.....,Factor100
GOOG,20111231,10,9.....

What I need is the following

FinancialTicker,Date,Factor1
FinancialTicker,Date,Factor2

or

GOOG,20111231,10
GOOG,20111231,9

Both of these examples are cells with mixed numeric and textual data. I first tried writing a loop to create one line at a time, but since I have 100 columns and 350,000 rows, that's a lot of iterations and it was taking hours. Can someone give me an easier method for creating this table formatted data? Maybe a command of snippit of code that would create and Index to create the large table all at once.

Thanks a lot,

Brian

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

per isakson 2012-6-12

Should I read

GOOG,20111231,10

as

{'GOOG','20111231',[10]} ?

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Geoff 2012-6-12

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/40550-matrix-to-table-format#answer_50528

在 MATLAB Online 中打开

Are you saying you want to generate the SQL:

CREATE TABLE my_table blah blah blah

Followed by

INSERT INTO my_table (`FinancialTicker`, `Date`, `Factor1`) VALUES
  ('GOOG', '20111231', '10'),
  ('GOOG', '20111231', '9'),
  ('GOOG', '20111231', '8'),
  ('GOOG', '20111231', '7'),
  ('GOOG', '20111231', '6'),
  ('GOOG', '20111231', '5');

You know... obviously with all the columns.... Well, I use MySQL and that's how I do bulk inserts. Just slam each row in as a tuple, and make sure the query doesn't exceed the maximum length allowed by the server (about 1 megabyte on mine). Might not be relevant to your SQL engine.

If you can do this type of query at all, you'll need close off each query and start a new one several times to keep it from overflowing. Can do that automatically of course... Simplest way is to just decide to do 5000 rows per query or whatever. I don't think there's anything wrong with representing everything as a string (even numbers) to make it easier. You should enclose your field names in back-ticks....

So write all these queries out to a SQL file and then throw it at your server.

You're not trying to link the data to other tables, right? You're just creating a flat table.