0

I've tried searching but most of the excel loads dealing with header information ask about ignoring them instead of loading them.

I'm working on a project, and have 7 lines of header information I would like loaded into my database table, it looks like this:

header

Using OpenRowset I load only A3:D13, when I check the Columns section of the Excel Source I only see these columns:

columns

I needed to add a column for load date so I've added a derived column into my data flow, after which it looks like this:

derived

Finally I add the DB destination and map out the columns, but as you can see I only have 4 input columns to my 7 columns in my table:

enter image description here

and after trying to load the file into my table I get this mess:

enter image description here

I'm thinking it's got something to do with how the header is formatted, instead of the normal heading then data directly below it, it's set up as heading then data adjacent to the heading.

Any ideas on what I can do to load this information correctly? Thanks in advance.

  • 1
    Those are rows, not columns. – Jacob H Aug 01 '17 at 12:55
  • @JacobH Do I have to unpivot/pivot them inside ssis to change them from rows to columns then? –  Aug 01 '17 at 12:59
  • I think that makes the most sense. Otherwise I think you will have to do something like a for each loop to query each "column" individually. Maybe someone else has a better idea though. – Jacob H Aug 01 '17 at 13:02
  • You have some hidden rows that you might want to deal with before unpivoting. – KeithL Aug 01 '17 at 13:06
  • @KeithL I think all rows are unhidden or Is there any other way to unhide it other than right-clicking the excel sheet and selecting unhide? Because the option is grayed out. –  Aug 01 '17 at 13:10
  • hidden rows will be in your data flow. ie rows 5,7,9,11 – KeithL Aug 01 '17 at 13:14
  • @KeithL Ahh, I see it now, in my excel sheet whenever I search for those rows, nothing shows up though. Only a black line is highlighted in-between the rows that are visible. How would I go about making those rows visible in my data flow? –  Aug 01 '17 at 13:18
  • So before you unpivot, I would conditionally split out null rows. – KeithL Aug 01 '17 at 13:19
  • this is a lot of work for one record. could you possibly get a better source? – KeithL Aug 01 '17 at 13:20
  • @KeithL Unfortunately, no I'd be getting these reports every 5 months. –  Aug 01 '17 at 13:22

0 Answers0