split excel file into different excel file

[Atngocnguyen]

hi

I have a excel file with data as table 1

I wanna split excel file into different files based on Column Vendor

Is there any way to do it?

[Agnius]

I have already replied to your question in the I Love Automation community Discord server.

The way to do this is to read data from your main Excel file and loop through each row of data you retrieved from it using a For each loop. Then have a Switch inside the loop based on the value in the Vendor column (can be retrieved as %CurrentItem['Vendor']% if you use %CurrentItem% in the For each loop). And then based on that switch, you can write the %CurrentItem% into a corresponding file.

The above would require that you have all of your files open at all times and also that you keep checking the first free row in each of them. Alternatively, you could use Create new data table to create separate tables for each of your vendors. Then inside the Switch cases, you could use Insert row into data table to insert %CurrentItem% into a corresponding table for the applicable vendor. When the loop is over, you could write all those tables into appropriate Excel files. This would be a bit faster, but less dynamic, because you would need to know how many different vendors you have and create a table for each of them in advance.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Atngocnguyen]

Hi,

Thanks for your help as usually

As I wanna a Dynamic flow, mean a Flow can automatically split excel file , no matter how many vendors arethere.
I try this one as below, but only get the result of all file  ( as table 1), not get the result of vendor A only.

So, is there any mistake?

[Agnius]

That's because you are writing to the same file that you read the data from. And you do not increment your Row variable. So, you are simply always overwriting the first row with a row that contains Vendor A. 

You need to increment your Row variable at the end of the loop and you need to launch a separate Excel file for writing your data.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Atngocnguyen]

HI,

I change as below, but it create 2 new excel file : each excel file record 1 data row of Vendor A

could you pls point out my mistake?

[Agnius]

That is because for each row, you launch a new empty document and save the file. You should define some name for your vendor file, use If file exists and if it does, then open it. If it doesn't, then open a blank document and then save it as the file name. This way you will create files dynamically for your vendors.

Do not forget to use Get first free column/row from Excel worksheet action to figure out which row to write to.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Atngocnguyen]

Hi

So, is there any way to write data into 1 new file at once time?.

As I understand, Switch just support to identify which row data contain Vendor A, then write to new workbook

However, it seems not really automatically because  the we need to know how many vendor ?

[Agnius]

Well, it depends on how you want to name your file. If you can simply use the vendor name for the file name, you can in fact set the file path inside the loop, using the vendor name from Excel, then launch the file, write to it, close it and keep doing this in the loop for each row. This would work without a switch.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Atngocnguyen]

hi

In case I wanna name my new files as Vendor name, but I wonder for Switch , if there are 3 vendor A, B, C, I need to add Switch %CurrentItem['Vendor']%   3 Case  = A, =B, =C , right?

How about if I have more than 3 vendors, even 10 or 20 Vendors ? for this scenario, I need to add 10 or 20 case? 

I mean is there any way to get data of each vendor in more dynamic way?

[Agnius]

You can do it dynamically, but then you should in fact not use a Switch statement. As you have correctly noted, a Switch will only work when you have a predefined set of values. If you want it to work dynamically, you should do it differently.

Here's an approach I would use:

This will use the vendor name as the file name. It will then check if the file exists already. If it does, it will open the existing file. If it doesn't, it will open a blank document and then save it as the file path. This way, you will be checking for a file with the vendor name on every row, but if you have already created a file like that, you will not be creating a new one.

Also, it uses the Get first free column/row from Excel worksheet to figure out which row to write to, as that will be different per file.

Here's a snippet you can copy and paste to PAD:

SET Directory TO $'''C:\\RPA'''
LOOP FOREACH CurrentItem IN ExcelData
    SET FilePath TO $'''%Directory%\\%CurrentItem['Vendor']%.xlsx'''
    IF (File.IfFile.Exists File: FilePath) THEN
        Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: FilePath Visible: True ReadOnly: False Instance=> ExcelInstance2
    ELSE
        Excel.LaunchExcel.LaunchUnderExistingProcess Visible: True Instance=> ExcelInstance2
    END
    Excel.GetFirstFreeColumnRow Instance: ExcelInstance2 FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: CurrentItem['Vendor'] Column: $'''A''' Row: FirstFreeRow
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance2 Value: CurrentItem['Amount'] Column: $'''B''' Row: FirstFreeRow
    Excel.SaveExcel.SaveAs Instance: ExcelInstance2 DocumentFormat: Excel.ExcelFormat.FromExtension DocumentPath: FilePath
    Excel.CloseExcel.Close Instance: ExcelInstance2
END

Just make sure you change the value of the %Directory% variable to the actual directory where you want to save your file.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.