Hi I have to import an Excel workbook on a weekly basis. The workbook can contain up to 100 sheets. I am using a ForEachLoop to read each sheet. The sheets do not have column names and each sheet has a "header" and "Detail" section. In the header I have to read out 4 cell values. These are based at B8, B10, C10 and E 12. I also have to read each of the details, these are contained in cell A16 to K16 and there can many details records, so that can run up to 100's of rows. Can someone help me in what the best approach would be to read the detail and the headers. I am thinking that I can use a derived column for the 4 headers, but I can' t figure out how to read a single Cell value using the derived column. Also the Details, it would be great to get an idea of what to do here, I am thinking that maybe I take a range from A1:A400 and K16:K400 and read them into an interim table in our database. Any help greatly appreciated. Aidan Gill

You're on the right path. To read those headers and footers separately, you don't need a Derived Column - you need two data flows (or at least four sources in the same data flow if you want to combine them later). To read the headers (without a complex SQL statement), you'll need three Excel Sources. In the Excel Source #1, can use a SQL Statement like "SELECT F1 AS B8 FROM 'Sheet1!B8'" - that will get you one row of one column. Excel Source #2 could have a statement like "SELECT F1 AS B10, F2 AS C10 FROM 'Sheet1!B10:C10'". Source #3: "SELECT F1 AS E12 FROM 'Sheet1!E12'". You could then Merge Join them together... but you'll have to do a cross-join to do that. To read the footers, use a range just like you've thought about. Talk to me now on

Hi Todd Thanks for that, that worked a treat. Now I have the 2 values I want from the header I want to be able to use them with that separate data task which extracts the details, am I best to store them out to a variable for use by that other task? What I am doing in order to create a correct record I need the 2 values above and I need to add these to each line in the detail. ThanksAidan Gill

If you want to use header values in your detail rows, then I'd merge your two data flows into one if that doesn't cause any other problems. If you want them in separate data flows, then it's going to be a little difficult to carry them around. You could put them in variables if you want, but that will require a script. You could also send that header rowset to a RAW file destination, then use a RAW source in the other flow. Talk to me now on