MATLAB Answers

0

Matrix to Table format

Brian 님이 질문을 제출함. 7 Jun 2012
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
on 12 Jun 2012
Should I read
GOOG,20111231,10
as
{'GOOG','20111231',[10]} ?

로그인 to comment.

태그

제품

답변 수: 1

Answer by Geoff
on 12 Jun 2012

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.

  댓글 수: 0

로그인 to comment.



Translated by