I have an table PARTY in MYSQL workbench DB , column party_id is primary key,Not Null and unique
Party_id Name Currency
10080 Person INR
10081 Party_GP USD
I have the below data in excel which i am going to upload through Macros (here party_id will be null)
Party_id Name Currency
Party_group GBP
Company USD
Person INR
i have macros code for uploading excel data to MYSQL DB. since here party_id is null , i am unable to insert record into DB. i need macros code to get max (party id) +1 from table PARTY from DB and it should get update in excel data of party_id during excel upload.
After upload the result in DB table should be like this
Party_id Name Currency
10080 Person INR
10081 Party_GP USD
10082 Party_group GBP
10083 Company USD
10084 Person INR
I had a similar issue trying to import csv into MySQL Workbench. I found that it was easier to use an insert into statement, which was generated by concatenating the commands around the cells.
In workbench, make sure that your primary key is set to auto-increment, and then I would copy and paste the contents of the concatenated cells(Column D in the example below) directly and run the query.
| Party_id | Name | Currency | | |----------|-------------|----------|--------------------------------------------------------------------------| | | Party_group | GBP | ="INSERT INTO `PARTY`,(`Name`, `Currency`) VALUES (`"&B2&"`, `"&C2&"`);" | | | Company | USD | ="INSERT INTO `PARTY`,(`Name`, `Currency`) VALUES (`"&B3&"`, `"&C3&"`);" | | | Person | INR | ="INSERT INTO `PARTY`,(`Name`, `Currency`) VALUES (`"&B4&"`, `"&C4&"`);" |
Copying Column D would result in pasting the following into workbench: INSERT INTO PARTY (Name, Currency) VALUES (Party_group, GBP); INSERT INTO PARTY (Name, Currency) VALUES (Company, USD); INSERT INTO PARTY (Name, Currency) VALUES (Person, INR);
As long as pasty_id is set to auto increment, it should update to the highest value +1