Mass update an excel file

I am new to Alteryx. I have someone who wants me to mass update data (over 200 lines) all at one time using a Alteryx workflow and a attached Excel file to the workflow. This is so that we don't have to filter line by line in the Excel file to make the updates as this would take a long time. Can you do this?

I know you can use the Excel file as your data input and then maybe use a text input?

Best Practices

Accepted answers

All comments

nagakavyasri

@kgnott46From my understanding of question, if you want to replace an excel file in Output, you can select 'Overwrite Sheet or Range' option in Output data tool.

If you want columns to be replaced, use Formula tool, select the column to be updated and use replace function. If multiple columns are to be updated you can use Multi Field Formula Tool.

Raj

@kgnott46 what you can do is add a sample data of 10-15 lines and the expected output

will help with the flow which can be used for complete data set
you can go through it for upskilling and learning more about the Tools.

kgnott46

I actually need to update multiple rows at one time. I want to purge my list that they gave me out of this Excel file to make a mass update then un purge my filter where I made the updates and save the main Excel file. I want to do this vs. having to actually go into the Excel file and look up each name individually to make the updates as that could take hours. Was hoping I could shortcut using Alteryx with a specific workflow?

JBO

I think it depends on what you are trying to update. I can think of many ways to do it. Can you provide an example of the data in the field you need to update and what you want to update it to?

For example, if you have an Excel spreadsheet and you have a column that identifies all of the rows that need to be updated and the update is the same value for all rows that need to be updated, then can be done with a really simple conditional statement. In the example below, the original data has a column called "Need to Update?" and column called "Color." The rows with an "X" in the "Need to Update?" column need the Color value changed to red.

Step 1: Bring in the Excel sheet with the Input tool.

Step 2: Attach the formula tool

Step 3: Set the field to "Color"

Step 4: Enter this function:

if [Need to Update] = "X"

then "Red"

else [Color]

endif

Step 5: Attach the Output tool and choose the location to output it to.

Original data:

Need to Update?	Color
X	Blue
X	Brown
X	Green
	Yellow
X	Orange
	Purple

Data after running the flow:

Need to Update?	Color
X	Red
X	Red
X	Red
	Yellow
X	Red
	Purple

But it really depends on what you need to update. It could be really simple or you might need a more complicated conditional statement. Like if you are updating rows that meet certain conditions. Maybe you only need to update rows that have a date field with a date value that falls within in a particular range. I can give you a template for that as well.

To sum it up, I think we need more info to help you out. Sample data and what field or values or conditions determine whether a field needs to be updated will help.

kgnott46

It is a very large Excel file with columns that go all the way to column "AL". I have a separate Excel file with over 200 people's names in it. I need to purge these 200+ people's names out of the very large Excel file and update all 200+ rows at the same time with the same verbiage. Updating this verbiage in two columns "AB and AC". This is so that I don't have to manually do this in the very large Excel file like if you did without using Alteryx "the old fashioned way" As I would have to look up each name individually one by one and update that way which could take hours. I hope that helps?

From what someone told me it sounds like you can use a workflow to purge data out of your main input, by also using a text input then some other tools in your workflow to get the data to an output. Some sort of reverse out of the main input make your mass update and then reverse back to the main input - if that makes sense? So when you reverse back to your main input what you mass updated stays updated but that main input now reflects all rows again and not the 200+ that you filtered out.

JBO

Can you please confirm that you need to do two separate things:

1) Remove any rows that have any of the 200+ names; and

2) Update values in columns AB and AC for the remaining rows of data.

JBO

Or are you saying you want to keep the rows that have any of the 200+ names, but clear the name field values and replace whatever values are in fields AB and AC with some new value that is the same for all 200+?

kgnott46

Filter out those 200+ rows names and update rows AB and AC while it is still filtered, have it save to the main file, but also un filter your main file so that all original data is there to include the mass update

I have this attached

Mass Update.pdf

JBO

Ok. I think I understand what you are attempting to achieve. Example flow attached.

1) Join the list of names with main database by the field that has the names. Important: The names must be entered the exact same way in both lists for this to work correctly

2) Connect the Formula tool to the middle "J" output from the Join tool.

3) Use Formula tool to update the fields you want to change (I assumed you wanted the same value in all of the fields).

4) Use the Union tool to bring the purged rows with replaced field values back with the rest of the database.

5) Use the Output tool to export your updated spreadsheet.

Here is a screenshot. You can also download the sample flow below:

Purge Flow Screenshot.JPG

Hope this helps.

Names to Purge Example.zip

kgnott46

Thanks so much for your help, I will give it a try!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703