将 Excel 工作表导入 MySQL，其中包含与单独表相关的值

发布于 2024-10-02 04:29:21 字数 1204 浏览 1 评论 0原文

首先，这可能是问这个问题的错误地方。所以，对此表示诚挚的歉意！

我有两个MySQL表，如下：

第一个表：employee_data

id   name         address        phone_no

1    Mark         Some Street    647-981-1512

2    Adam         Some Street    647-981-1214

3    John         Some Street    647-981-1452

第二个表：employee_wages

id   employee_id         wages        start_date

1       3                $15          12 March 2007

2       1                $20          10 Oct 2008

3       2                $18          2 June 2006

我知道，这两个表可以合并为一个表，不需要将这些数据拆分为两个表。但是，我正在处理的工作要求这些数据是单独的并且位于两个不同的表中。

现在，以前我的公司过去常常在 Excel 工作表中处理所有这些数据，他们遵循将这两个表合并为一张工作表的传统方法，如下所示：

Excel 工作表

id   name           wages         start_date

1    Mark           $20           10 Oct 2008

2    Adam           $18           2 June 2006

3    John           $15           12 March 2007

现在，目标是将数据从 Excel 工作表导出到 MySQL 表。

正如您所注意到的，employee_data.id 链接到employee_wages.employee_id

我如何替换Excel工作表“名称”列中的值，以便它们代表在employee_data.id列中给出的实际唯一ID。

可能是我可以使用 PHP/MySQL 来完成，或者我可以在 VB 脚本中完成此操作。但是，我不是 VB 脚本方面的专家。

任何帮助将不胜感激。

谢谢！

原文

First up, this might be the wrong place to ask this question.. So, sincere apologies for that!

I have two MySQL Tables as follows:

First Table: employee_data

id   name         address        phone_no

1    Mark         Some Street    647-981-1512

2    Adam         Some Street    647-981-1214

3    John         Some Street    647-981-1452

Second Table: employee_wages

id   employee_id         wages        start_date

1       3                $15          12 March 2007

2       1                $20          10 Oct 2008

3       2                $18          2 June 2006

I know, both these tables can be combined into one and there is no need to split this data into two tables. But, what i'm working on requires this data to be separate and in two different tables.

Now, previously my company used to handle all this data in Excel sheets and they followed the conventional method of having these two tables combined into one sheet as follows:

Excel Sheets

id   name           wages         start_date

1    Mark           $20           10 Oct 2008

2    Adam           $18           2 June 2006

3    John           $15           12 March 2007

Now, the objective is to Export the data from Excel sheets into MySQL Tables.

As you can notice employee_data.id is linked to employee_wages.employee_id

How can i replace the values in the Excel Sheet 'name' column so that they represent the actual unique ID they're given in the employee_data.id column..

May be i can do it with PHP/MySQL or i can get this done in VB Script.. BUt, I'm not an expert in VB Script..

Any help will be much appreciated..

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

拥抱我好吗 2024-10-09 04:29:21

将 Excel 数据保存为 csv 文件（在 Excel 2007 中使用“另存为”）

使用文本编辑器（例如记事本）检查保存的文件，看看它实际上是什么样子，即使用了什么分隔符等。

启动 MySQL 命令提示符（I'我很懒，所以我通常从 MySQL 查询浏览器 – 工具 – MySQL 命令行客户端执行此操作，以避免输入用户名和密码等。）

输入此命令：

LOAD DATA LOCAL INFILE ‘C:\\temp\\yourfile.csv’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (field1, field2);

[编辑：确保检查单引号 (') 和双引号(") 如果您复制并粘贴此代码 - WordPress 似乎正在将它们更改为一些相似但不同的字符]
完毕！

Save your Excel data as a csv file (In Excel 2007 using Save As)

Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc.

Start the MySQL Command Prompt (I’m lazy so I usually do this from the MySQL Query Browser – Tools – MySQL Command Line Client to avoid having to enter username and password etc.)

Enter this command:

LOAD DATA LOCAL INFILE ‘C:\\temp\\yourfile.csv’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (field1, field2);

[Edit: Make sure to check your single quotes (') and double quotes (") if you copy and paste this code - it seems WordPress is changing them into some similar but different characters]
Done!

回复收藏 0 原文