mass update in table storage tables

Question

Is there a way to mass update the TableStorage entities?

Say, I want to rename all the Clients having "York" in the City field to "New-York".

Is there some tools to do it directly (without the need to writing code)?

Answer 1

You could try to use Microsoft Azure Storage Explorer to achieve it.

First, you have some entities in table storage with a City field in your Storage Explorer.

Then you could click Export button to export all your entities to a .csv file. Enter Ctrl + F and choose Replace item. Fill the find and replace item with what you want then choose Replace All.

Finally, go back to the Storage Explorer and click Import button to choose the .csv file you have edited before.

Answer 2

I wanted to do the trick with export/import but it's a no go when you have millions of records. I exported all the records and ended up with ~5gb file. Azure Storage Explorer couldn't handle it (my pc i7, 32gb ram).

If someone is also struggling with similar issue, you can do as follow:

1. Export records to csv file
2. Remove the lines that you don't want to modify (if needed). You can use grep "i_want_this_phrase" myfile > mynewfile or use -v option to find all that doesn't match the given phrase. If file is too large, split it with some command eg. cat bigFile.csv | parallel --header : --pipe -N999 'cat >file_{#}.csv'
3. Remove everything except the RowKey column.
4. Prepare az cli command similar to az storage entity merge --connection-string 'XXX' --account-name your_storage -t your_table -e PartitionKey=your_pk MyColumn=false [email protected]=Edm.Boolean RowKey=. Remember about odata.type. At first I did an update without this and instead of bools, I switched to strings. Luckily it was easy to fix.
5. Open the file in VSC, select all with ctrl+a, then shift+alt+i to put a cursor at the end of all lines and then paste previously prepared az cli command. This way you will get a list of az cli updates for each RowKey.
6. Add #!/bin/bash at the beginning of the file, save as .sh, modify privileges chmod +x yourfile and run.

Of course if you want, you can create some bash script for that and read a file line by line and execute az command. I just did it my way as it was much simpler for me, I'm not so experienced in bash, so it would take me a while to dev&test the script.