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 CenterFile Exchange 中查找有关 Database Toolbox 的更多信息

标签

产品

Community Treasure Hunt

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

Start Hunting!

Translated by