Load wonky excel data

[rittermd]

I have been given an excel file that is a glossary of kpi and measures used in the app.

I have not had to do this for over a year and I am totally blanking on this.

The spreadsheet has a first column called Term.  The first row contains a value (Revenue).  The next x rows fall under this value but the column is empty.  Then you hit the next term (Invoice).  Same scenario.  Value on first row and the rest are blank.

I want to take that value and add it to each row in my data load.  So if there are 7 rows then Revenue should appear in all 7 as the value for Term and so on.

I realize that I could just add the value to the spreadsheet.  If it turns out that is easier then I just might do that.  But I wanted to see my options.  Thanks.

[QFabian]

Hi @rittermd , try this with peek() function :

Data:
LOAD * INLINE [
Term, value
Revenue, 200
, 400
, 300
, 250
, 800
Invoice, 150
, 200
, 500
, 300
];

Load
Term,
if(rowno()=1, Term,if(isnull(Term) or Term='', peek('Term2'), Term)) as Term2,
value
Resident Data;

drop table Data;

[QFabian · Accepted solution]

---

---

---

@rittermd wrote:

I realize that I could just add the value to the spreadsheet.  If it turns out that is easier then I just might do that.  But I wanted to see my options.  Thanks.

---

Yes, but as a definition, you should never alter the source data.

iamgie the excel have updates, so everytime you got a new file, you'll have to alter the file.

[rittermd]

Thanks.  That gave me exactly what I needed.