Matrix to Table format
7 views (last 30 days)
Show older comments
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.
What I need is the following
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,
Geoff on 12 Jun 2012
Are you saying you want to generate the SQL:
CREATE TABLE my_table blah blah blah
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.
Find more on Tables in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!Start Hunting!