如何自动化EXCEL表，Mysql和XML交互

I will given an Excel sheet with many records(more than 1000 records).

我将给出一张包含许多记录（超过1000条记录）的Excel表格。

I have to upload few fields of this excel sheet into Mysql. For each record a unique id will be assigned using an auto increment field in Mysql.

我必须将这个excel表的几个字段上传到Mysql中。对于每条记录，将使用Mysql中的自动增量字段分配唯一ID。

Now i have to create a single XML file for the Excel sheet along with the unique id assigned in Mysql table.

现在我必须为Excel工作表创建一个XML文件以及在Mysql表中分配的唯一ID。

Is there a way i can automate the process or what will be a simplest way to do it?

有没有办法让我自动化这个过程，或者最简单的方法是什么？

3 个解决方案

#1

Automation will be somewhat limited because you need to validate the input against your business rules. Some fields are required, formatting may be important for some and the allowed entries may have a constrained list. You can just shove the data in, but it will break on invalid entries. That is probably not acceptable.

自动化将受到一定限制，因为您需要根据业务规则验证输入。某些字段是必需的，格式化对于某些字段可能很重要，并且允许的条目可能具有约束列表。您可以将数据推入，但它会在无效条目中中断。这可能是不可接受的。

If the following is helpful, I have a PHP function that I wrote that makes it easy to convert a delimited upload file (tabs in the example) into an array keyed to the column names. Once you have this you can iterate the data structure to do validation and do inserts. For very large data sets a LOAD DATA MySQL command will be more performant though.

如果以下是有用的，我有一个我编写的PHP函数，可以很容易地将分隔的上载文件（示例中的选项卡）转换为键控到列名称的数组。完成后，您可以迭代数据结构以进行验证并进行插入。对于非常大的数据集，LOAD DATA MySQL命令将更具性能。

  function buildUploadArray($File) 
  {
    $handle = fopen($File, "r");
    $fields = fgetcsv($handle, 1000, "\t");

    while($data = fgetcsv($handle, 1000, "\t")) 
    {
      $detail[] = $data;
    }

    $x = 0;
    $y = 0;

    foreach($detail as $i) 
    {
      foreach($fields as $z) 
      {
        $result[$x][trim(strtolower($z))] = trim($i[$y]);
        $y++;
      }
      $y = 0;
      $x++;
    }

    return $result;
  }

This does mean that your users upload a tab-delimited file instead of Excel, but it is easy for them to save in that format. You can likely use the http://phpexcel.codeplex.com/ if you need the upload format to be Excel.

这意味着您的用户上传了制表符分隔文件而不是Excel，但他们很容易以该格式保存。如果您需要将上传格式设置为Excel，则可以使用http://phpexcel.codeplex.com/。

#2

Using Perl you can use:

使用Perl你可以使用：

Excel::Reader A mySQL module and a XML writer

Excel :: Reader一个mySQL模块和一个XML编写器

I don't specify what modules because your mileage may vary. This is the kind of thing Perl is excellent for. Of course you can do this in PHP or any other language but hey, it's called Practical Extraction and Report Language, (Pathologically Eclectic Rubbish Lister is another one :) )

我没有指定哪些模块，因为您的里程可能会有所不同。这是Perl非常适合的事情。当然你可以用PHP或任何其他语言来做到这一点，但嘿，它被称为实用提取和报告语言，（病态折衷垃圾Lister是另一个:)）

#3

You might try Automation Anywhere for this. It does not require programming or scripting, if that's the way you want to go. There's a free trial as well. -Tom

您可以尝试使用Automation Anywhere。它不需要编程或脚本，如果这是你想要的方式。还有一个免费试用版。 -Tom

#1

  function buildUploadArray($File) 
  {
    $handle = fopen($File, "r");
    $fields = fgetcsv($handle, 1000, "\t");

    while($data = fgetcsv($handle, 1000, "\t")) 
    {
      $detail[] = $data;
    }

    $x = 0;
    $y = 0;

    foreach($detail as $i) 
    {
      foreach($fields as $z) 
      {
        $result[$x][trim(strtolower($z))] = trim($i[$y]);
        $y++;
      }
      $y = 0;
      $x++;
    }

    return $result;
  }

#2