douzhuanfen5923 2015-02-05 13:26
浏览 78

通过excel将数据上传到Db

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
  • 写回答

1条回答 默认 最新

  • dongmei8071 2015-02-05 16:52
    关注

    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

    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题