Matrix to Table format

2 ビュー (過去 30 日間)
Brian
Brian 2012 年 6 月 7 日
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 件のコメント
per isakson
per isakson 2012 年 6 月 12 日
Should I read
GOOG,20111231,10
as
{'GOOG','20111231',[10]} ?

サインインしてコメントする。

回答 (1 件)

Geoff
Geoff 2012 年 6 月 12 日
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.

カテゴリ

Help Center および File ExchangeDatabase Toolbox についてさらに検索

タグ

製品

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by