Import an Excel File not set as a table

I am trying to import this excel file that comes in a non-normalize format. This file is automatically added to a folder and I am trying to normalize the data and extract it into columns. The issue I am having is the import part. Below see a picture of what the report looks like (Demo Data).

Demo- Report Photo In.jpg

My goal is to extra the table part and then be able to move through Alteryx workflow normally. In some cases there maybe more than one file it is working through.

Demo- Report Photo Output.jpg

I have attached demo data of the file report that gets upload into our work queue "Input Excel Demo" Input. Thank you in advance for all your help.

Input Excel Demo.xlsx

Extracting Non Normal Excel File Demo.yxmd

Workflow

Datasets

Input

Error Message

Accepted answers

All comments

Raj

If the Format of file remains same it can be done with help of directory and dynamic input tool.
if format is same every time i can share the flow.ALso confirm if format gets changed.

jdminton

@FUB_IA_SQL_GUY Can you clarify your question? Your workflow works for this one file, but I'm not sure if I'm understanding your question. As the previous poster noted, this will work if you have the same structure for each file. If not, you have to do some things a little differently. Happy to help though.

kamal03

Hi, If all the files follow the same pattern, We can handle this problem using the "Directory" and "Dynamic Input" tools and then we'll clean up any odd rows or sideways data.

FUB_IA_SQL_GUY

@Raj

Yes the format is the same, the only thing is different would be the row might add some or delete some. This is an inventory of vendors and then some of there information may change, add new vendors, or delete old vendors.

FUB_IA_SQL_GUY

@jdminton

The report I attached is an example with demo data. Once this flow is up and running this report gets saved into a OneDrive folder daily of updated vendor information for our company. This data is updated on a daily bases, the columns and format will stay the same just updates to the records.

New vendors added
Old vendors deleted
Current vendors may have information modified

My problem is I'm just not sure how to get around the format of the file. The first 3 rows are just titles for the report, and some other things like Freeze pan, and merges in the first couple of rows. I am just trying to extract the table parts of the excel workbook and turn them into SQL table as my output. With the report coming in daily, I want to be able to run the flow each day and it makes a archived (Report date) in my SQL table. I work in a department where we need to be able to pull up archived information, i.e. The report data as of [Report Date].

FUB_IA_SQL_GUY

@kamal03

Yes it is the same pattern. I thought it might have something to do with the Dynamic Input, just wasn't sure how to get it to work with a XLSX. I have done it in the past with CSV and was just a normalize table. This one wasn't coming in correctly because of the format of this particular xlsx file (Example I attached above of what the format will be like).

jdminton

Let's start here. Like I said, you have the correct setup to bring the data in. You will want to read all of the data in each time and use Alteryx to compare and update values or add additional records. I did adjust the template settings on this one to bring in the filename as well so you'll know exactly which file it originated in. I didn't configure the output data as it is specific to your SQL DB. I also made copies of the input file to simulate different reports coming in. Please take a look and let me know any questions up to this point. You can also apply a filter between the directory tool and dynamic input to select only reports from yesterday or add another input tool from your SQL filenames and join to identify reports already processed and exclude them. Feel free to add any follow up questions.

Extracting Non Normal Excel File Demo-jm.yxzp

jdminton

@FUB_IA_SQL_GUY were you able to test this out?

Quick Links

This months top contributors

mceleavey 383

AkimasaKajitani 382

mbarone 337

Hollingsworth 335

LanisC 335