Bob, You might create an ODBC connection to the spread sheet. I believe there is a Microsoft item to do that. If not unload the spread sheet as a CSV and use the Microsoft CSV/TEXT ODBC connector. you would use the ODBC connection to define a 158 column table Store Number and the 157 items.
You then can define various views to access the data. The UNION statement could be effectively used to get transform the column data to row data by store. Jim Bentley American Celiac Society [email protected] tel: 1-504-737-3293 ________________________________ From: "[email protected]" <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, January 12, 2010 8:01:08 AM Subject: [RBASE-L] - Large Spread sheet matrix data I have an application where a supplied data source is an Excel spreadsheet. This spread sheet is a matrix consisting of locations as rows and stocked items as columns. (I have no control over the spread sheet design!) Store# ItemA ItemB ItemC ItemD..... 0100 Y N N Y 0200 Y Y N N 0300 N Y Y N 0400 N N N Y etc. This spread sheet is updated regularly, with stores added or removed (# of rows), stock items added or removed (# of columns) and the store information changed. (Y/N) Currently there are 1950 rows in the spread sheet and 157 items (columns). I need to link Rbase to this spreadsheet and be able to run routines against store# and what items each store stocks, etc. Such information such as # of stores that ItemA is stocked in, how many items Store 0100 has. etc. etc. Any thoughts on an efficient method as to link this continually changing data into Rbase table(s). ? A simple table with Store Number and Item number would have 306150 rows of data. (1950 x 157) and would also be difficult to populate from the above matrix. Innovative ideas appreciated! Thanks- Bob
