c# - 预测 Excel 如何使用 Postgres 函数解释字符串

Question

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

例如，Excel 进行以下更改:

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

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

我有一个使用 VSTO (C#) 固定在 Excel 上的实用程序，它会输出到 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 将如何弄乱它。我相信我已经处理过带有前导零的数字以及在小数位后被截断的尾随零。

不幸的是，这并没有涵盖所有内容。我不认为 Dates 是可以预测的，所以我什至不会尝试，除非有人有好主意。但是科学计数法呢？还有其他我没有想到的场景吗？

我们的零件目录包含超过 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 非常擅长文本处理。

Answer 1

我的建议是将所有零件号提取到 excel，在完成对零件号的修改后保存文件，然后将结果作为新表(或原始表中的一列)上传回数据库。这样您就不必担心您没有处理的任何案件。如果您经常基于此进行查找，它还允许您为该列编制索引，并轻松检测出任何零件号，这些零件号在被修改后最终变成重复项。

在 excel 重新上传之前，您必须有一些方法来确定它们是什么。如果有一个替代 Id 列，您可以使用它，否则做一些简单的事情，比如在零件号前加上​​“Part-”前缀，这样 excel 就会看到一个字符串而不是触摸它。

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