Jim: But Bob said that the spreadsheet would constantly change because there's a column for each item. One day there could be 158 items, the next 157 or 159. So tables would have to be created on the fly rather than pre-defined.
I'm assuming you could do a gateway import creating a new table. Then use sys_columns to cursor through each column in that table to load the data into your permanent table. Yeah, I could see you having over 300K rows if it's a table with one item and one location, but I can't think of any other way. I don't think it would be "difficult" to populate, just a bunch of code. If you want to make sure there's no hard coding whatsoever, I think you'd have to cursor through each row (store location), then use the cursor thru each sys_columns value to trap whether it's a yes/no. What's the "name" of each column? Is it something that is meaningful, such as an Item# that's in your table? Karen > 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 >
