{"id":204408,"date":"2025-05-29T13:58:21","date_gmt":"2025-05-29T05:58:21","guid":{"rendered":"https:\/\/server.hk\/cnblog\/204408\/"},"modified":"2025-05-29T13:58:21","modified_gmt":"2025-05-29T05:58:21","slug":"%e5%b0%86-excel-%e6%95%b0%e6%8d%ae%e9%9b%86%e8%bd%ac%e6%8d%a2%e4%b8%ba-sql-%e6%8f%92%e5%85%a5%e8%af%ad%e5%8f%a5","status":"publish","type":"post","link":"https:\/\/server.hk\/cnblog\/204408\/","title":{"rendered":"\u5c06 Excel \u6570\u636e\u96c6\u8f6c\u6362\u4e3a SQL \u63d2\u5165\u8bed\u53e5"},"content":{"rendered":"

<\/b> <\/p>\n

\u5c06 Excel \u6570\u636e\u96c6\u8f6c\u6362\u4e3a SQL \u63d2\u5165\u8bed\u53e5<\/h1>\n

\u6765\u5230\u7684\u5927\u5bb6\uff0c\u76f8\u4fe1\u90fd\u662f\u7f16\u7a0b\u5b66\u4e60\u7231\u597d\u8005\uff0c\u5e0c\u671b\u5728\u8fd9\u91cc\u5b66\u4e60\u6587\u7ae0\u76f8\u5173\u7f16\u7a0b\u77e5\u8bc6\u3002\u4e0b\u9762\u672c\u7bc7\u6587\u7ae0\u5c31\u6765\u5e26\u5927\u5bb6\u804a\u804a\u300a\u5c06 Excel \u6570\u636e\u96c6\u8f6c\u6362\u4e3a SQL \u63d2\u5165\u8bed\u53e5\u300b\uff0c\u4ecb\u7ecd\u4e00\u4e0b\uff0c\u5e0c\u671b\u5bf9\u5927\u5bb6\u7684\u77e5\u8bc6\u79ef\u7d2f\u6709\u6240\u5e2e\u52a9\uff0c\u52a9\u529b\u5b9e\u6218\u5f00\u53d1\uff01<\/p>\n

<\/p>\n

\u5229\u7528 python \u4f7f\u5c06 excel \u6587\u4ef6\u8f6c\u6362\u4e3a sql \u6570\u636e\u5e93\u6210\u4e3a\u4e00\u4e2a\u7b80\u5355\u7684\u8fc7\u7a0b\u3002<\/p>\n

\u9996\u5148\uff0c\u8bf7\u6309\u7167\u4ee5\u4e0b\u6b65\u9aa4\u5c06 excel \u6570\u636e\u5bfc\u51fa\u5230 csv \u6587\u4ef6\uff1a<\/p>\n

    \n
  1. \u6253\u5f00\u60a8\u7684 excel \u6587\u4ef6\u3002<\/li>\n
  2. \u5bfc\u822a\u5230\u6587\u4ef6 > \u53e6\u5b58\u4e3a\u3002<\/li>\n
  3. \u9009\u62e9 csv\uff08\u9017\u53f7\u5206\u9694\uff09(*.csv) \u4f5c\u4e3a\u6587\u4ef6\u7c7b\u578b\u5e76\u4fdd\u5b58\u6587\u4ef6\u3002<\/li>\n<\/ol>\n

    \u6309\u7167\u8fd9\u4e9b\u7b80\u5355\u7684\u8bf4\u660e\uff0c\u60a8\u53ef\u4ee5\u5c06 excel \u6570\u636e\u65e0\u7f1d\u8f6c\u6362\u4e3a\u4e0e sql \u6570\u636e\u5e93\u517c\u5bb9\u7684\u683c\u5f0f\u3002<\/p>\n

    first_name last_name \u7535\u5b50\u90ae\u4ef6 user_id user_login_name
    first01 last01 \u540d\u5b57\u59d3\u6c0f01 id001 \u767b\u5f55\u540d01
    first02 last02 firstlastname02 id002 \u767b\u5f55\u540d02
    first03 last03 \u540d\u5b57\u59d3\u6c0f03 id003 \u767b\u5f55\u540d03
    first04 last04 \u540d\u5b57\u59d3\u6c0f04 id004 \u767b\u5f55\u540d04
    first05 last05 \u540d\u5b57\u59d3\u6c0f05 id005 \u767b\u5f55\u540d05
    first06 last06 firstlastname06 id006 \u767b\u5f55\u540d06
    first07 last07 \u540d\u5b57\u59d3\u6c0f07 id007 \u767b\u5f55\u540d07
    first08 last08 \u540d\u5b57\u59d3\u6c0f08 id008 \u767b\u5f55\u540d08<\/p>\n

    \u5229\u7528\u811a\u672c\u6216\u5de5\u5177\u5c06 csv \u6587\u4ef6\u8f6c\u6362\u4e3a sql \u683c\u5f0f\u3002\u4f8b\u5982\uff0c\u60a8\u53ef\u4ee5\u4f7f\u7528 python \u811a\u672c\u6765\u89e3\u6790 csv \u6587\u4ef6\u5e76\u521b\u5efa sql \u63d2\u5165\u8bed\u53e5\u3002\u4e0b\u9762\u662f\u4e00\u4e2a\u57fa\u672c\u7684 python \u811a\u672c\uff0c\u53ef\u5e2e\u52a9\u60a8\u5f00\u59cb\u8f6c\u6362\u8fc7\u7a0b\uff1a<\/p>\n

    import pandas as pd\n\n# Read the CSV file into a DataFrame\ndf = pd.read_csv('D:\/temp\/test\/TestExcel.csv') # Add the path to your CSV file\n\n# Generate SQL insert statements\ntable_name = 'Test_Table_Name' # Replace with your desired table name\nsql_statements = []\n\nfor index, row in df.iterrows():\n    columns = ', '.join(row.index)\n    values = ', '.join([f\"'{str(value)}'\" for value in row.values])\n    sql_statements.append(f\"INSERT INTO {table_name} ({columns}) VALUES ({values});\")\n\n# Save to a file\nwith open('D:\/temp\/test\/insert_statements.sql', 'w') as f:\n    for statement in sql_statements:\n        f.write(statement + '\\n')\n<\/pre>\n
    \u4ee5\u4e0b\u662f\u4e0a\u9762\u4ee3\u7801\u751f\u6210\u7684\u811a\u672c\u7684\u7ed3\u679c\u3002<\/p>\n
    insert into test_table_name (first_name, last_name, email, user_id, user_login_name) values (‘first01’, ‘last01’, ‘firstlastname01’, ‘id001’, ‘loginname01’);
     insert into test_table_name (first_name, last_name, email, user_id, user_login_name) values (‘first02’, ‘last02’, ‘firstlastname02’, ‘id002’, ‘loginname02’);
     insert into test_table_name (first_name, last_name, email, user_id, user_login_name) values (‘first03’, ‘last03’, ‘firstlastname03’, ‘id003’, ‘loginname03’);
     insert into test_table_name (first_name, last_name, email, user_id, user_login_name) values (‘first04’, ‘last04’, ‘firstlastname04’, ‘id004’, ‘loginname04’);
     insert into test_table_name (first_name, last_name, email, user_id, user_login_name) values (‘first05’, ‘last05’, ‘firstlastname05’, ‘id005’, ‘loginname05’);
     insert into test_table_name (first_name, last_name, email, user_id, user_login_name) values (‘first06’, ‘last06’, ‘firstlastname06’, ‘id006’, ‘loginname06’);
     insert into test_table_name (first_name, last_name, email, user_id, user_login_name) values (‘first07’, ‘last07’, ‘firstlastname07’, ‘id007’, ‘loginname07’);
     insert into test_table_name (first_name, last_name, email, user_id, user_login_name) values (‘first08’, ‘last08’, ‘firstlastname08’, ‘id008’, ‘loginname08’);<\/p>\n
    \u8bf7\u6ce8\u610f\uff0c\u6709\u5728\u7ebf\u5de5\u5177\u53ef\u5c06 csv \u6587\u4ef6\u8f6c\u6362\u4e3a sql \u63d2\u5165\u8bed\u53e5\u3002\u4f7f\u7528\u8fd9\u4e9b\u5de5\u5177\u65f6\u52a1\u5fc5\u5c0f\u5fc3\u8c28\u614e\uff0c\u4ee5\u907f\u514d\u66b4\u9732\u654f\u611f\u6570\u636e\u3002\u5728\u67d0\u4e9b\u60c5\u51b5\u4e0b\uff0c\u516c\u53f8\u53ef\u80fd\u51fa\u4e8e\u5b89\u5168\u539f\u56e0\u963b\u6b62\u8bbf\u95ee\u67d0\u4e9b\u7f51\u7ad9\u3002<\/p>\n
    \u6587\u4e2d\u5173\u4e8e\u7684\u77e5\u8bc6\u4ecb\u7ecd\uff0c\u5e0c\u671b\u5bf9\u4f60\u7684\u5b66\u4e60\u6709\u6240\u5e2e\u52a9\uff01\u82e5\u662f\u53d7\u76ca\u532a\u6d45\uff0c\u90a3\u5c31\u52a8\u52a8\u9f20\u6807\u6536\u85cf\u8fd9\u7bc7\u300a\u5c06 Excel \u6570\u636e\u96c6\u8f6c\u6362\u4e3a SQL \u63d2\u5165\u8bed\u53e5\u300b\u6587\u7ae0\u5427\uff0c\u4e5f\u53ef\u5173\u6ce8\u516c\u4f17\u53f7\u4e86\u89e3\u76f8\u5173\u6280\u672f\u6587\u7ae0\u3002<\/p>\n
          \u7248\u672c\u58f0\u660e \u672c\u6587\u8f6c\u8f7d\u4e8e\uff1adev.to \u5982\u6709\u4fb5\u72af\uff0c\u8bf7\u8054\u7cfb\u5220\u9664<\/p>\n","protected":false},"excerpt":{"rendered":"
    \u5c06 Excel \u6570\u636e\u96c6\u8f6c\u6362\u4e3a S...<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4925],"tags":[],"class_list":["post-204408","post","type-post","status-publish","format-standard","hentry","category-4925"],"_links":{"self":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts\/204408","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/comments?post=204408"}],"version-history":[{"count":0,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/posts\/204408\/revisions"}],"wp:attachment":[{"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/media?parent=204408"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/categories?post=204408"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/server.hk\/cnblog\/wp-json\/wp\/v2\/tags?post=204408"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}