Help to a newbie. Import and data handeling

[Hamder83]

Hi 

Im fairly new to alteryx - only been playing around with this for a month.

Ive got a task at hand, creating a summary based on invoice specifications.

All the invoices has similar structure. But all files has more than 1 sheet.

1 Sheet that is not usefull. And 1+ Sheets with proper data.

Ive found the CReW_WildcardXLSXInput importer, which works great.

Im able to import the correct sheets, and exclude those who are not usefull.

But in each file i have:
Kundenummber (custoner number) 
and 
Faktura dato (invoice date) 

I want to full two colums with this data.
So 

File 1 has:
Invoice date: 28-11-2019
Customer number: 1 

And File 2 has:
Invoice date: 01-08-2019

Customer number: 2

So i have to extract it, and fill a colum based on the file its extraced from.

Ive tried and upload my sample data.

And my attemp to create a flow (im not sure i have all files attached here)



 

[VianneyM]

Hi @Hamder83,

could you add the headers file?

also what you want to achieve is to extract 2 columns from each tab (invoice date and customer number) and add another column that says from which file this was extracted?

[RolandSchubert]

Hi @Hamder83 ,

the header file is missing, but I've already modified your workflow a bit.

Each sheet contains header rows, I've remove these using a Filter tool (NULL values in fields as condition)

There is one remaining row with the field names you use later on, but this row exists on each sheet, so I've used a second filter to identify these rows, selected the first of these rows and added the row to the data again. Using a Dynamic Rename you create the field names needed.

Next step would be select the fields you need and to do the calculations.

Hope this is helpful.

Regards

Roland

[gautams091]

Hi

In the Input Data tool, there is an option "Output File Name as Field". Select that field as "File Name Only". 

File name will appear as column in the imported data file. 

Regards,

Gautam

[Hamder83]

Hi 

The headers are in the excel files, in two of the sheets as row 10 🙂 

[Hamder83]

That seems to work like a charm! Thank you 

I have one more question, and this seems super simple, but i must admit my skills aint good enough yet.

In both files there are some data i would love to have transfered into two coulms.

ive sttached the data.

But how do i create a coloumn with data from a specific cell ? 

Basicly i want the flow to read the invoice number and the customer number, and fill that to each line in the file.

So i can track which lines belong to which invoice - if that makes sense?

[RolandSchubert · Accepted solution]

The required information can be found on the first data sheet only (and on the Summary sheet you skip), but can be easily identified by the entries in cells A2 and A5. So it is possible to filter out this rows, cross tab it to create columns containing the customer and invoice number and re-join it to the data. I've modified and attached the workflow. Does this work for you?

[Hamder83]

Hi Roland 

Sorry about the late reply.

This was excactly what i was hoping for. Thank you very much for your help 🙂 

[Hamder83]

Hi Roland 

May I ask for your assistence again? 

I ran the workflow as your made it.

But for November I have 4 invoices, with different headers and coloumn position.

Furthermore I need to add fields, even if not contained in the 4invoices.

Ive added the 4 headers - and the total header thats our standard form.

.... Do I make any sense at all?

It seems like the correct header title is not put into the correct column.

 

[RolandSchubert]

Hi @Hamder83 ,

it seems, the "Import" part has to be replaced. The problem is, that field names are loaded as "data" and converted to fieldnames in a second step (I assumed, field names are always the same, but as we see now, it's a bit different).
Anyway, I've created a macro, that should do the import, so that all fields are loaded (and not "merged" as it happened). The workflow I've attached should load the sheets, add the fields from the header to each row and (hopefully) sets the field names.

Best,

Roland