perl导入文本至mysql,使用Perl将数据从Excel导入MySQL

Ok, let me preface this thread by saying that I cannot provide a real sample of the data that I am working with. The reason is, because it contains a lot of private, confidential information (for work) that I cannot divulge. But, I can provide the details of the database coding that I have been working on. I have been racking my brains the last few days with this and I am at a point that I believe I need some guidance.

I am working on a project for work that will be a database of all of our client information. It is quite an ambitious project, but it is something that is desperately needed by my group. We have a couple thousand clients (currently) and that is growing by leaps and bounds at the moment, so getting this project completed sooner rather than later is a bit critical.

The initial load of client data was provided to me in an excel spreadsheet. It has 39 columns of information and almost 2000 rows, 1 row of information per client. In order to work with the data, I initially started using the Spreadsheet::ParseExcel module, but after attempting to pull out the data, noticed there were only 1300 lined of data in the file, instead of the almost 2000 lines that exist in the file. So, I changed my tactics. I saved the spreadsheet out to a file, but chose .csv as the format. I chose a ":" as the field delimiter and it seemed to export fine, showing the correct number of lines when I checked.

I then wrote the below code to go through the file, line by line, put each value in a variable, prepare the INSERT statement, and then execute. Here is the code I have:

Expand|Select|Wrap|Line Numbers

解决方案There are a number of issues with the code you posted, but I''ll focus on a portion of the prepare/execute statement.

Your prepare statement is using only 1 place holder, but you''re passing 40 values in the execute statement. The number of placeholders needs to match the number of values passed in the execute statement.

You might want to look at using:

DBD::AnyData -- DBI access to XML, CSV and other formats

http://search.cpan.org/~jzucker/DBD-....09/AnyData.pm

Or, you may want to look at using the built-in features of mysql for importing data.

I haven''t used it myself, but this might help.

LOAD DATA INFILE

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Thanks Ron! I will take a look at what you have posted and get back to this thread. I appreciate the suggestions/help.

Regards,

Jeff

好的，让我先说这个帖子，说我无法提供我正在使用的数据的真实样本。原因是，因为它包含许多我无法透露的私人机密信息(用于工作)。但是，我可以提供我一直在研究的数据库编码的详细信息。最近几天我一直在绞尽脑汁，我相信我需要一些指导。

我正在开发一个工作项目，它将成为我们所有客户信息的数据库。这是一个非常雄心勃勃的项目，但这是我的团队迫切需要的东西。我们有几千个客户(目前)，而且目前正在实现跨越式发展，因此尽快完成这个项目有点关键。

客户端数据的初始加载是在excel电子表格中提供给我的。它有39列信息和近2000行，每个客户端有1行信息。为了处理这些数据，我最初开始使用Spreadsheet :: ParseExcel模块，但在尝试提取数据之后，注意到文件中只有1300行数据，而不是文件中存在的近2000行数据。文件。所以，我改变了我的策略。我将电子表格保存到文件中，但选择.csv作为格式。我选择了一个"："作为字段分隔符，它似乎导出正常，当我检查时显示正确的行数。

然后我编写了下面的代码来逐行浏览文件，将每个值放在变量中，准备INSERT语句，然后执行。这是我的代码：

展开 | 选择 | Wrap | 行号

解决方案

有很多问题使用您发布的代码，但我将专注于准备/执行声明的一部分。

您的准备声明仅使用1个占位符，但您正在通过execute语句中有40个值。占位符的数量需要与执行语句中传递的值的数量相匹配。

您可能希望使用：

DBD :: AnyData - DBI访问XML，CSV和其他格式

http://search.cpan.org/~jzucker/DBD-....09/AnyData.pm

或者，你可能想看看使用mysql的内置功能导入数据。

我自己没有使用它，但这可能会有所帮助。

LOAD DATA INFILE < br /> http：//dev.mysql .com / doc / refman / 5.1 / en / load-data.html

谢谢Ron！我将看看你发布的内容并回到这个主题。我很感激你的建议/帮助。

问候，

Jeff