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! 

[BrandonB]

What about something like this

 

[BrandonB]

You could even take it a step further with logic up front that uses a directory tool to see all of the files in the folder and then based on the file name automatically pull the latest quarterly file into a dynamic input tool so that you don't have to point the Input Data tool at the latest file each time. Then you could schedule this whole process on Alteryx Server and have it run on a quarterly basis and fully automate it! 

[c20091929]

Hey Brandon,

Thank you so much for spending the time to provide an excellent solution for me!

It works well!!!

Do you know how I can build the logic for the first part?

As you mentioned, Alteryx can pull the latest file by using the name of the file. 

What will be the tool behind this?

Thank you again!

[BrandonB]

Here you go 🙂

 

[c20091929]

Thank you so so much, Brandon!!!

Another quick question - Is there a way that I can import all the files in that folder together and also apply the date and company name columns?

I was trying to remove those three tools but I got an error in the dynamic input

Not sure removing those three tools will work this way:

Load all the files in that folder => Add date and company name column for each excel file => All the output in one table (like append each file together)

[BrandonB]

You can remove the sort and sample tools but you will need the formula tool because that is what is used to specify the sheet that the data is pulled from in the files. 

[c20091929]

Hey man, 

Thank you so much for getting back to me again!

I left the formula tool and it runs well.

The issue is it only pull the data from one excel file but not all the files under the same folder. 

I checked the formula tool and it will default to one excel file if I use the Full path option. 

Anything I need to change from the formula tool?

[BrandonB]

Ah, thinking about it now, you actually need to turn this process into a macro because it needs to extract the necessary data for each file individually. I'll mock something up to demonstrate. 

[BrandonB · Accepted solution]

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.