如何在XML版本中删除CSV文件中的无效行 [英] How to remove invalid lines from a CSV file during XML conersion

问题描述

我正在创建一个BizTalk解决方案来处理一个采购订单。

 客户，orderno，orddate，prodcode，proddesc，proddesc2，数量，价格，deliverydt，delivery1  
700 KIDMAN WAY，66109，03/03/15，CTV834， CTV834 GOSSIPS WHITE Cask Dry，4x5L 29313748009606，1200.0000,0.0000，16/03/15，700 KIDMAN WAY
700 KIDMAN WAY，66109，03/03/15， ，**** run 18083 ****，，0.0000,0.0000，03/03/15，700 KIDMAN WAY
700 KIDMAN WAY，66109，03 / 03/15，CTV835，CTV835 GOSSIPS RED Cask Dry，4x5L 29313748009590，1200.0000,0.0000，16/03/15，700 KIDMAN WAY
700 KIDMAN WAY， 66109，03/03/15，，**** run 18084 ****，，0.0000,0.0000，03/03/15，700 KIDMAN WAY

我只想要替换行（1,3等等）来映射并创建XML文件。



第二行（第4行等等）无效，因为没有可用的产品代码。



因此，输出XML文件应仅包含那些l ines有产品代码，在同一时间行号应该是正确的。



我们可以使用BizTalk地图来做，还是需要使用csharp代码？



请帮忙。



谢谢



Haris

解决方案

您可以使用这样的正则表达式来检查行是否符合您的条件。

 Regex LineExpression =  new 正则表达式（  \ （？< customer> [\\ S] *？）\，（？< orderno> \\\\ *），\（？< orddate> [\\ S] *？）\，\（？< prodcode> [\\ S] +？）\，\（？< proddesc> [\\ S] *？）\\ \\，\（？< proddesc2> [\\ S] *？）\，（？< qty> \\d + \\.\\\\ *）， （小于？价格> \\d + \\.\\d *），\（小于？deliverydt> [\\ S] *？）\，\（？< delivery1> [\\ S] *？）\）; 

http://www.regular-expressions.info/ [ ^ ]



请注意，对于产品代码，表达式使用'+'代替'*'作为量词。

这告诉正则表达式引擎在双引号内至少应该有字符。因此，没有产品代码的行将不匹配。



然后你可以读取逐行读取文件并检查它是否匹配或读取整个文件和循环浏览所有比赛。



C＃示例1

  foreach （ string  line  in  File.ReadAllLines（  filename.txt））
 {
匹配m = LineExpression.Match（line）; 
  if （m.Success）
 {
  string  customer = m.Groups [  customer]。 
  int  orderNumber =  int  .Parse（m.Groups [  orderno]。Value）; 
 DateTime orderDate = DateTime.ParseExact（m.Groups [  orddate]。价值，   MM / dd / yy， null ）; 
  //  等等。 
} 
} 

C＃示例2

  string  lines = File.ReadAllText（  filename.txt）; 
 
 
  foreach （匹配m   LineExpression.Matches（行））
 {
  string  customer = m.Groups [ 客户]值。 
  int  orderNumber =  int  .Parse（m.Groups [  orderno]。Value）; 
 DateTime orderDate = DateTime.ParseExact（m.Groups [  orddate]。价值，   MM / dd / yy， null ）; 
  //  等等。 
} 

Hi,

I'm creating a BizTalk solution to process one purchase order.

customer,orderno,orddate,prodcode,proddesc,proddesc2,qty,price,deliverydt,delivery1
"700 KIDMAN WAY",66109,"03/03/15","CTV834","CTV834 GOSSIPS WHITE Cask Dry","4x5L 29313748009606",1200.0000,0.0000,"16/03/15","700 KIDMAN WAY"
"700 KIDMAN WAY",66109,"03/03/15","","**** run 18083 ****","",0.0000,0.0000,"03/03/15","700 KIDMAN WAY"
"700 KIDMAN WAY",66109,"03/03/15","CTV835","CTV835 GOSSIPS RED Cask Dry","4x5L 29313748009590",1200.0000,0.0000,"16/03/15","700 KIDMAN WAY"
"700 KIDMAN WAY",66109,"03/03/15","","**** run 18084 ****","",0.0000,0.0000,"03/03/15","700 KIDMAN WAY"

I want only alternate lines(1, 3.. etc) to get mapped and create XML file.

second line (4th line and so on) is invalid since there is no product code available.

So, the output XML file should contain only those lines having product code, in the same time line number should be proper.

Can we do it using BizTalk map or do we need to use csharp code?

Please help.

Thanks

Haris

解决方案

You can use a regular expression like this to check if a row matches your criteria or not.

Regex LineExpression  = new Regex("\"(?<customer>[\\S ]*?)\",(?<orderno>\\d*),\"(?<orddate>[\\S ]*?)\",\"(?<prodcode>[\\S ]+?)\",\"(?<proddesc>[\\S ]*?)\",\"(?<proddesc2>[\\S ]*?)\",(?<qty>\\d+\\.\\d*),(?<price>\\d+\\.\\d*),\"(?<deliverydt>[\\S ]*?)\",\"(?<delivery1>[\\S ]*?)\"");

http://www.regular-expressions.info/[^]

Notice that for the product code, the expression uses a '+' instead of a '*' as a quantifier.
This tells the regex engine that there should be at least on character inside the double quotes. Hence, lines without a product code will not match.

Then you can read either read the file line by line and check if it is match or read the whole file and loop through all matches.

C# example 1

foreach (string line in File.ReadAllLines("filename.txt"))
{
    Match m = LineExpression.Match(line);
    if (m.Success)
    {
        string customer = m.Groups["customer"].Value;
        int orderNumber = int.Parse(m.Groups["orderno"].Value);
        DateTime orderDate = DateTime.ParseExact(m.Groups["orddate"].Value, "MM/dd/yy", null);
        // etc. etc.
    }
}

C# example 2

string lines = File.ReadAllText("filename.txt");


foreach (Match m in LineExpression.Matches(lines))
{
    string customer = m.Groups["customer"].Value;
    int orderNumber = int.Parse(m.Groups["orderno"].Value);
    DateTime orderDate = DateTime.ParseExact(m.Groups["orddate"].Value, "MM/dd/yy", null);
    // etc. etc.
}

这篇关于如何在XML版本中删除CSV文件中的无效行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！