Load Quarterly Excel File into the Database

c20091929

Hey guys, 

I hope everything is going well with you. 

I need help with loading quarterly excel file into our database. 

The excel looks like this:

Row 5 is the company name

Row 8 is the date of the report

From Row 15 and below is the real table.

I will get the same excel every 3 months, and I need to load those into our database. 

I want to load the data and create two extra columns: The date and the company name.

Like this:

I tried to do something like the below, but this required adding a new field when I have the new file. 

I want to drop those files into a folder and build the logic to recognize the date and the company name from row 5 and row 8. 

Thank you for your help and your time again! 

Sample 1.xlsx

Sample.yxmd

Sample 2.xlsx

BrandonB

This workflow passes in one file at a time into the macro and performs the steps within to each file and stacks the resulting output

 \

This is what it looks like inside the macro. Notice that I am using a control parameter connected to an action tool that updates the Input Data tool. 

 

Example files are attached. 

example v3 macro.yxmc

example v3 full workflow.yxmd

workflow package.yxzp