预测Excel将如何使用Postgres函数解释字符串

本文关键字：函数 解释 字符串 Postgres 何使用 Excel 预测 | 更新日期: 2023-09-27 18:10:58

在我的工作场所，我们有零件号(项目号)，当输入到Excel中时，它经常被转换成Excel 认为用户的意思。

例如，Excel做了以下更改:

00001234   =>   1234
005678.0   =>   5678
1234.560   =>   1234.56

由于电子表格来自我们控制之外的来源，我们无法尝试控制Excel本身的行为。

我有一个实用程序，是螺栓连接到Excel，使用VSTO (c#)，出去到Postgres表，并试图查找"Excel零件号"，并将其转换回实际零件号。简单地说，它看起来像这样:

create table mdm.excel_lookup (
    actual_part_number text not null,
    excel_part_number text not null,
    lookup_priority integer not null,
    constraint excel_lookup_pk primary key (actual_part_number)
);

为了填充这个表，我用plperl编写了一个函数，它试图接受任何给定的字符串，并预测Excel将如何把它弄乱。我相信我已经处理过前导零和后置零的数字，这些数字在小数点后被截断。

不幸的是，这并没有涵盖所有内容。我不认为日期是可以预测的，所以我可能甚至不会尝试，除非有人有一个好主意。但是科学记数法呢?还有其他我没想到的场景吗?

我们的零件目录有超过150万个零件，所以可能发生的事情有很多可能性。如果我能抓住他们的一部分，我就很高兴了。

这是我目前的功能。如果有人对我能做些什么来捕捉Excel可能做的其他事情有什么想法，我将欢迎反馈。注意，到目前为止，这只处理我上面列出的场景。

CREATE OR REPLACE FUNCTION excel_part(part_number text)
  RETURNS text AS
$BODY$
  my ($input) = @_;
  if ($input =~ /[A-Za-z]/) {
    return $input;
  } elsif ($input =~ /^0+('d+)$/) {
    return $1;
  } elsif ($input =~ /^('d+'.'d*)0+$/) {
    return $1 + 0;
  } else {
    return $input;
  }  
$BODY$
  LANGUAGE plperl VOLATILE
  COST 100;

而且，我没有嫁给plperl。我使用它只是因为我知道Perl非常擅长文本处理。

预测Excel将如何使用Postgres函数解释字符串

我的建议是将所有零件号提取到excel中，在完成修改数字后保存文件，然后将结果上传到数据库作为新表(或原始表中的列)。这样你就不用担心你没处理过的案子了。如果您经常在此基础上进行查找，它还允许您对列进行索引，并轻松检测到在更改后最终成为重复的任何部件号。

你必须有一些方法来确定它们在excel得到它们之前是什么，尽管重新上传。如果有一个代理Id列，你可以使用它，否则做一些简单的事情，比如用"part -"作为零件号的前缀，这样excel就会看到一个字符串，而不会碰它。

如果simbabque是正确的，不同的excel版本做不同的事情，你可以通过多个不同版本的excel运行这个过程，并保存唯一的munges

作为对Tim Tom的回答的回应，这是一个实现他的建议的c#程序…我觉得效果很好!

    NpgsqlConnection conn = new NpgsqlConnection();
    conn.Open();
    Excel.Application xl = new Excel.Application();
    xl.Visible = true;
    Excel.Workbook wb = xl.Workbooks.Add(1);
    Excel.Worksheet ws = (Excel.Worksheet)wb.Sheets[1];
    List<string> parts = new List<string>();
    NpgsqlCommand cmd = new NpgsqlCommand("select prod_id from mdm.global_item_master",
        conn);
    NpgsqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
        parts.Add(reader.GetString(0));
    reader.Close();
    NpgsqlCopyIn copy = new NpgsqlCopyIn(
        "copy mdm.excel_item_id from STDIN WITH NULL AS '' CSV;", conn);
    copy.Start();
    NpgsqlCopySerializer cs = new NpgsqlCopySerializer(conn);
    cs.Delimiter = ",";
    foreach (string part in parts)
    {
        ws.Cells[1, 1].Value2 = part;
        cs.AddString(part);
        cs.AddString(ws.Cells[1, 1].Text);
        cs.EndRow();
    }
    cs.Close();
    copy.End();
    conn.Close();
相关文章：