필터 지우기
필터 지우기

Matrix to Table format

조회 수: 10 (최근 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