Just create a Bulk Update Tool. Can’t think this did not existed for such a long time!
Use it via Setup > Bulk Update (or just type “bulk” on the toolbar)
14 Likes
This awesome, but I cannot Bulk Update on the Status field. I have all these Delivery Notes that are in the “To Bill” status that I just want to switch to “Closed” … Bulk Update won’t let me do this operation – meaning I’d have to edit each one individually. Any thoughts? The same goes for Purchase Receipts. I don’t use any of the billing stuff, so I want to skip To Bill and just move to Closed.
I guess that might be because you actually need to bill those DN’s in order to satisfy system’s logic.
But you don’t … I can go into each DN individually and change Status to Closed. I’m sure more things happen internally other than updating a value to close it, but I’d love to be able to do this in bulk.
Can one update 4500 records instead of 500
Write a Python script!
Last week I delved a bit deeper into the world of MariaDB. I followed this procedure manually first, which I am sure can be scripted with Python or made simpler, but I had to do a test and this worked. I used this for cost centers and item groups, uploading a combination of 49,500 groups and nodes per table. (These are UNSPSC categories for managing inventory and budgeting per item category). I have seen no stability issues, and ERPNext is working absolutely fine on my remote and VirtualBox servers. Adapt accordingly to your data structure.
Export data from MariaDB database for ERPNext using a .csv file
If this is your first time doing this, I highly recommend taking a look in the data within the tables to learn about table data format
-
On a clean installation of ERPNext create at least 3 levels of cost center groups (folders) and then create one final node nested in level 3 group. If already have data, move to next step
-
Using SSH, login to your server. Or if using locally, move to next step
ssh user@erpnextserver
- Login to MariaDB (Password is stored in your site_config.json file)
mysql -u root -p
- Check which databases exist with the following MySql command. If you do not know which database to choose, check each one individually by selecting it and then using the
show tables;command.
show databases;
- Select the database containing the ERPNext tables with the
usecommand: (change db id accordingly)
use 1af3b1647ab2768;
- Show the tables to verify it is the ERPNext database in use. In this particular case, the tables I used where `
tabCost Center` and `tabItem Group`
Use the backquote symbol to encase table names to avoid errors.
show tables;
- Verify the table header structure (column names, data types, etc.) using the
describecommand. I personally copy and paste this data from my MAC terminal onto an excel spreadsheet. I manually transpose the paste to columns.
describe `tabCost Center`;
- OPTIONAL: Verify the contents of the table to make sure this is the data you need exported
SELECT * FROM `tabCost Center`;
- Now export to a .csv file to the tmp folder in your linux OS.
SELECT * INTO OUTFILE ‘/tmp/costcenters.csv’
CHARACTER SET ‘utf8’
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
FROMtabCost Center;
- I work remotely using MAC OS X, Excel and TextMate. Thus, I copy the files using the
scpcommand in MAC OS X (Linux too). Modify the username, server, and destination path at the end of the command + arguments according to your needs.
sudo scp user@erpnextserver:**/tmp/costcenters.csv** /Users/username/Documents/
Parse Data for Import into Database
-
Review the data, adding the column headers for easy reference. Also make sure the “rgt” and “lft” column headers are numbered accordingly. Everything else can be left either at “0” or “\N”, except for the obvious columns or headers you need imported.
-
Parse and then finally copy the data you wish imported into the structured table.
-
When data looks ready, copy only data onto a new blank excel spreadsheet. Save as .csv with utf-8 formatting.
In my case, I use an additional formula in excel to concatenate into one row per cell, it is complete with th enecessary commas. I then select each and copy onto a blank TextMate file, which I then save as a .csv with utf-8.
Import data to MariaDB database for ERPNext using a .csv file
- Copy the .csv file with the parsed data to your server if working remotely.
sudo scp /Users/username/Documents/costcenters-forimport.csv user@erpnextserver:/tmp/
- Using SSH, login to your server. Or if using locally, move to next step
ssh user@erpnextserver
- Login to MariaDB (Password is stored in your site_config.json file)
mysql -u root -p
- Select the database containing the ERPNext tables with the
usecommand: (change db id accordingly)
use 1af3b1647ab2768;
- Import the data to the table.
LOAD DATA INFILE ‘/tmp/costcenters-forimport.csv’
REPLACE
INTO TABLE `tabCost Center`
CHARACTER SET utf8
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’;
Once you have done it once you will be able to figure out how to to the same process for other data items. It also helps to have as a sort of “backup” in case you need to rebuild an ERPNext server with the basic structure of accounts, cost centers, item groups, warehouses, and other data so you can then image the “Production ready” server for immediate deployment for each new business you work with
Any suggestions on how best to accomplish all these steps using a python script, are welcome!
5 Likes
I have a major update issue. I have got a full database of items, but the suppliers were not linked. I created a new file with the supplier column and started to upload it in bulk (3,000 records per time) I have 95,000 records. But the system ignores the update, it doesn’t add the new column data!
URGENT HELP NEEDED
Thanks, we know about it when we need it
Is it possible to update a custom field with the value of another field?