从CSV删除重复的字符串

[tdignan87]

你好，
我有来自ERP系统的CSV文件，尽管系统存在错误（等待修复），偶尔它会发送包含重复行的CSV文件的文件，这会导致其他系统混乱它导入到。
但是，我应该在几周内解决ERP的问题。

我如何创建一个简单的控制台文件，该文件检查文件夹中是否有CSV文件并读取该文件，并检查第二行是否与前两行匹配？如果是这样，它只会删除最后一行。
下面的csv示例
"P0755","R190830022","2021-08-30","POSITIVE RELEASE",57.000,"TRUE"
"P0755","R190830022","2021-08-30","POSITIVE RELEASE",14.500,"TRUE"

我基本上希望应用程序删除第二行作为"" match.

任何帮助将不胜感激或示例。

 

[羊皮]

我不了解...您能在我向您弹出不必要/错误的答案之前更详细地说明吗？此文件夹中将有一个或多个文件吗？这需要澄清：

查看第二行是否与前两个文本行匹配；

您定义为什么行？
如果您有这个：

"P0755","R190830022","2021-08-30","POSITIVE RELEASE",57.000,"TRUE"
"P0755","R190830022","2021-08-30","POSITIVE RELEASE",14.500,"TRUE"

应用程序删除第二行后，输出应该是什么？

 

[tdignan87]

啊，输出应该只是第一行，如果可能，将第二行放在一个新文件中。
我应该这么说；
如果我不能将其放在另一个文件中，则只需删除第二行。

此文件夹中最多包含两个或三个文件。系统处理后，将CSV格式并再次移动它。
行是CSV文件中的数据行。
通常，一个文件中最多可容纳5行。

 

[羊皮]

好的，您可以选择任何一种方式。您想要一个新文件还是删除第二行？
我认为删除第二行会更容易，更干净。

所以基本上你想删除第二行 如果 "P0755","R190830022"被列出两次？所以从本质上讲，您要删除重复项？

如果可以的话，这里可能会建议使用正则表达式。

 

[tdignan87]

是的，请删除。

是的，那是 如果 "P0755","R190830022" is listed twice.

 

[羊皮]

它为您添加了注释，因此您应该能够了解代码中的情况。您需要做的就是将其中之一放在按钮或方法中，它会开始在您的桌面中查找名为csvfile.csv的CSV文件，但是我建议根据您的需要进行更改。请注意，此代码将覆盖您正在读取的当前csv文件，并将其替换为具有非重复行的新文件。这里还有改进的空间！如果"P0755"不相关，您可以选择从使用哈希集改为使用字典来保存<key,value> as <line, R190830022>使行成为唯一键。该代码按测试方式工作，并在6ms处执行，可以通过优化代码加以改进。

            /* Combines the desktop folder with the file name of my CSV file */
            string pathToFile = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "csvfile.csv");
            Dictionary<string, string> kvp = new Dictionary<string, string>();
            HashSet<string> eachLine = new HashSet<string>();
            /* Above are self explanatory, while below csvReader creates an array of strings from all lines in the file */
            string[] csvReader = File.ReadAllLines(pathToFile);
            /* Loop the string array of lines */
            foreach (string line in csvReader)
            {
                /* Split at first comma */
                var partA = line.Split(',').FirstOrDefault();
                /* Split at second comma, by skipping the first one */
                var partB = line.Split(',').Skip(1).FirstOrDefault();
                /* TryGetValue returns a bool, so we set the bool if the key exists */
                bool kvpHasValue = kvp.TryGetValue(partB, out string kvpValue);
                /* If value isn't returned, this will be false */
                if (kvpHasValue == false)
                /* If it isn't added, we will add it below */
                {
                    /* partB seems to be unique, so I used this for the key, and partA as the value since it's not unique according to CSV */
                    kvp.Add(partB, partA);
                    /* Next we add partB and partA to the dictionary, next we add the line to the hashset */
                    eachLine.Add(line);
                }
            }
            File.WriteAllText(pathToFile, string.Empty);
            /* Start with an empty file, then write to it */
            eachLine.ToList().ForEach(func_line => File.AppendAllText(pathToFile, string.Concat(func_line, Environment.NewLine)));
            /* Lastly write the file back with only the entries we added, and no duplicates */

这是我刚开始使用的方法，但是通过使用以下内容，它可以使速度提高3ms：

            /* Combines the desktop folder with the file name of my CSV file */
            string pathToFile = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "csvfile.csv");
            Dictionary<string, string> kvp = new Dictionary<string, string>();
            StringBuilder sb = new StringBuilder();
            /* Above are self explanatory, while below csvReader creates an array of strings from all lines in the file */
            string[] csvReader = File.ReadAllLines(pathToFile);
            /* Loop the string array of lines */
            foreach (string line in csvReader)
            {
                /* Split at first comma */
                var partA = line.Split(',').FirstOrDefault();
                /* Split at second comma, by skipping the first one */
                var partB = line.Split(',').Skip(1).FirstOrDefault();
                /* TryGetValue returns a bool, so we set the bool if the key exists */
                bool kvpHasValue = kvp.TryGetValue(partB, out string kvpValue);
                /* If value isn't returned, this will be false */
                if (kvpHasValue == false)
                /* If it isn't added, we will add it below */
                {
                    /* partB seems to be unique, so I used this for the key, and partA as the value since it's not unique according to CSV */
                    kvp.Add(partB, partA);
                    /* Next we add partB and partA to the dictionary, next we add the line to the string builder */
                    sb.Insert(sb.Length, string.Concat(line, Environment.NewLine));
                }
            }
            /* Lastly write the file back with only the entries we added, and no duplicates */
            File.WriteAllText(pathToFile, sb.ToString());

所不同的是，我相信文件的末尾是方法1在写入文件之前将文件打开两次。

<<< Method 1

<<< Method 2
在这两种方法中，直到现在，代码都以相同的速度运行：

            File.WriteAllText(pathToFile, string.Empty);
            eachLine.ToList().ForEach(func_line => File.AppendAllText(pathToFile, string.Concat(func_line, Environment.NewLine)));

我知道您说过您不会执行很多行，但是如果以后改变并且您正在执行更多行，请考虑到这一点，因为上述行将花费3毫秒的​​执行时间。看起来似乎并不多，但是加班加起来。希望这是您想要的？经过测试和工作，但可以大大改善。如果要查找目录中的所有文件，建议您迭代目录路径，然后将文件位置中的每个文件传递给 pathToFile 并让其执行该方法。这也会给您一些事情。如果遇到问题，请发回

 

[贾加洛·布罗塔（JuggaloBrotha）]

您可以访问Sql Server或类似服务器吗？
您可以做的是将所有数据加载到表中，然后使用查询从重复数据中提取一条记录，这样做的好处是您可以添加更细粒度的条件，对于重复条件之一抓取而不是文件中的第一个或最后一个。您将使用Row_Number（）作为（按[P0755的列划分]，[R190830022的列]按[P0755的列]，[R190830022的列]，[57.000的列]进行排序）作为[RowNum]和您所在的位置的子句将[RowNum] = 1。

 

[tdignan87]

羊皮非常有效，谢谢。

 

[tdignan87]

您可以访问Sql Server或类似服务器吗？
您可以做的是将所有数据加载到表中，然后使用查询从重复数据中提取一条记录，这样做的好处是您可以添加更细粒度的条件，对于重复条件之一抓取而不是文件中的第一个或最后一个。您将使用Row_Number（）作为（按[P0755的列划分]，[R190830022的列]按[P0755的列]，[R190830022的列]，[57.000的列]进行排序）作为[RowNum]和您所在的位置的子句将[RowNum] = 1。

嗨JuggaloBrotha
抱歉，它具有数据的AS400旧PRMS系统。我无权访问数据库。
不过谢谢

 

[羊皮]

Sheepings非常有效，谢谢。

我认为可能会。不客气。乐于帮助。

 

[tdignan87]

如果您想要一盒好东西，请告诉我
该代码适用于苏格兰的食品制造商（脆饼）

 

[tdignan87]

string pathToFile = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), @"C:\Users\tdignan\Documents\CSV Script", " *.csv");

对不起，最后一个问题。我如何获取代码来查看目录中的csv文件，而不仅仅是特定文件？
谢谢

 

[羊皮]

我现在正在上班。因此，我不在PC上进行输入。如果到今天晚上没有其他人答复您，我将在您回国时告诉您。同时，您可以发布CSV文件的位置吗？

同时，您可以在等待时尝试自己做，因为我已经在上面解释了如何做。

 

[tdignan87]

如果成功，我将自己尝试配对并发布。如果不是到家的时候，那你就知道我需要帮助
\\ 192.168.7.33 \ Vantage_Hist

多数民众赞成在文件所在的路径

 

[羊皮]

我将以另一种方式进行操作...我将拥有它，因此您可以自己在控制台中输入位置。尝试 ：

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace TestConsoleApp
{
    internal class Program
    {
        static string pathToFolder = string.Empty;
        private static void Main(string[] args)
        {
            Console.WriteLine("Press Ctrl+V to paste in your path and press enter key :");
            /* After you Ctrl+V, and hit enter, pathToFile will be set to the directory of your CSV files */
            pathToFolder = Console.ReadLine();
            DirectoryInfo directory = new DirectoryInfo(pathToFolder);
            foreach (FileInfo file in directory.GetFiles("*.csv"))
            {
                RemoveDuplicates_InEachFile(file.FullName, file.Name);
            }
        }
        private static void RemoveDuplicates_InEachFile(string pathToFile, string filename)
        {
            HashSet<string> hashSet = new HashSet<string>();
            /* Above are self explanatory, while below csvReader creates an array of strings from all lines in the file */
            string[] csvReader = File.ReadAllLines(pathToFile);
            /* Loop the string array of lines */
            foreach (string line in csvReader)
            {
                /* Split at second comma, by skipping the first one */
                string partB = line.Split(',').Skip(1).FirstOrDefault();
                bool hasText = hashSet.Any(Func_Partial => Func_Partial.Contains(partB));
                if (hasText == false)
                {
                    /* If it isn't added, we will add it below */
                    hashSet.Add(line);
                    /* Next we add line to the hash set */
                }
            }
            /* Delete the file, and recreate it by appending it */
            File.Delete(pathToFile);
            hashSet.ToList().ForEach(func_line => File.AppendAllText(pathToFile, string.Concat(func_line, Environment.NewLine)));
            /* Lastly write the file back with only the entries we added, and no duplicates */
        }
    }
}

如果您想阅读代码在做什么，则可以使用此详细模式。它添加的全部内容是评论，可在屏幕上写出正在执行的操作...

扰流板： 详细版本（点击我）

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;

namespace TestConsoleApp
{
    internal class Program
    {
        static string pathToFolder = string.Empty;

        private static void Main(string[] args)
        {
            Console.WriteLine("Press Ctrl+V to paste in your path and press enter key :");
            /* After you Ctrl+V, and hit enter, pathToFile will be set to the directory of your CSV files */
            pathToFolder = Console.ReadLine();
            DirectoryInfo directory = new DirectoryInfo(pathToFolder);
            foreach (FileInfo file in directory.GetFiles("*.csv"))
            {
                Console.WriteLine($"REPORT: Console found file {file.Name} : Processing file content....");
                RemoveDuplicates_InEachFile(file.FullName, file.Name);
            }
            Console.WriteLine("Console is finished processing CSV files");
            Console.WriteLine("Press any key to exit. Are you still looking for the any key? :)");
            Console.ReadKey();
        }
        private static void RemoveDuplicates_InEachFile(string pathToFile, string filename)
        {
            Console.WriteLine($"Processing file: {filename}");
            HashSet<string> hashSet = new HashSet<string>();
            /* Above are self explanatory, while below csvReader creates an array of strings from all lines in the file */
            string[] csvReader = File.ReadAllLines(pathToFile);
            Console.WriteLine($"{filename} contains {csvReader.Length} lines");
            int cycle = 1;
            /* Loop the string array of lines */
            foreach (string line in csvReader)
            {
                Console.WriteLine($"Processing line ({cycle}) : Outputting line content: {line}");
                /* Split at second comma, by skipping the first one */
                string partB = line.Split(',').Skip(1).FirstOrDefault();
                Console.WriteLine($"Getting keyword from line ({cycle}) : Searching line for keyword ({partB})");
                bool hasText = hashSet.Any(Func_Partial => Func_Partial.Contains(partB));
                if (hasText == false)
                {
                    Console.WriteLine($"({partB}) keyword was not found on the hashset, continuing process...");
                    /* If it isn't added, we will add it below */
                    hashSet.Add(line);
                    Console.WriteLine($"Adding line ({cycle}) to the hashset");
                    /* Next we add line to the hash set */
                }
                else
                {
                    Console.WriteLine($"Duplicate line has been identified and skipped");
                }
                cycle++;
            }
            /* Delete the file, and recreate it by appending it */
            Console.WriteLine($"Deleting old file : {filename}");
            File.Delete(pathToFile);
            Console.WriteLine($"({filename}) deleted successfully, standby while compiling new hashset file...");
            hashSet.ToList().ForEach(func_line => File.AppendAllText(pathToFile, string.Concat(func_line, Environment.NewLine)));
            Console.WriteLine($"({filename}) successfully rebuilt!");
            cycle = 1;
            /* Lastly write the file back with only the entries we added, and no duplicates */
        }
    }
}

详细模式在屏幕上输出以下内容：

扰流板： 控制台输出

Press Ctrl+V to paste in your path and press enter key :
C:\Users\user\Desktop
REPORT: Console found file csvfile - Copy (2).csv : Processing file content....
Processing file: csvfile - Copy (2).csv
csvfile - Copy (2).csv contains 7 lines
Processing line (1) : Outputting line content: "P0755","R190830022","2021-08-30","POSITIVE RELEASE",57.000,"TRUE"
Getting keyword from line (1) : Searching line for keyword ("R190830022")
("R190830022") keyword was not found on the hashset, continuing process...
Adding line (1) to the hashset
Processing line (2) : Outputting line content: "P0755","R190830088","2021-08-30","POSITIVE RELEASE",18884.500,"TRUE"
Getting keyword from line (2) : Searching line for keyword ("R190830088")
("R190830088") keyword was not found on the hashset, continuing process...
Adding line (2) to the hashset
Processing line (3) : Outputting line content: "P0755","R190830089","2021-08-30","POSITIVE RELEASE",58887.000,"TRUE"
Getting keyword from line (3) : Searching line for keyword ("R190830089")
("R190830089") keyword was not found on the hashset, continuing process...
Adding line (3) to the hashset
Processing line (4) : Outputting line content: "P0755","R190830777","2021-08-30","POSITIVE RELEASE",57.000,"TRUE"
Getting keyword from line (4) : Searching line for keyword ("R190830777")
("R190830777") keyword was not found on the hashset, continuing process...
Adding line (4) to the hashset
Processing line (5) : Outputting line content: "P0755","R190830777","2021-08-30","POSITIVE RELEASE",57.000,"TRUE"
Getting keyword from line (5) : Searching line for keyword ("R190830777")
Duplicate line has been identified and skipped
Processing line (6) : Outputting line content: "P0755","R190830777","2021-08-30","POSITIVE RELEASE",14.500,"TRUE"
Getting keyword from line (6) : Searching line for keyword ("R190830777")
Duplicate line has been identified and skipped
Processing line (7) : Outputting line content: "P0755","R190830777","2021-08-30","POSITIVE RELEASE",57.000,"TRUE"
Getting keyword from line (7) : Searching line for keyword ("R190830777")
Duplicate line has been identified and skipped
Deleting old file : csvfile - Copy (2).csv
(csvfile - Copy (2).csv) deleted successfully, standby while compiling new hashset file...
(csvfile - Copy (2).csv) successfully rebuilt!

To have your folder location hard coded change pathToFolder = Console.ReadLine(); - to equal the 文件夹 放在双引号中，并删除第13行。

 

[贾加洛·布罗塔（JuggaloBrotha）]

Hi JuggaloBrotha
抱歉，它具有数据的AS400旧PRMS系统。我无权访问数据库。
不过谢谢

不，我在问您是否有权访问Sql Server或类似服务器，而不是您是否有权访问数据来自的系统。

 

[tdignan87]

我得到的值不能为null？

 

[羊皮]

请在此处上传CSV文件

 

[tdignan87]

伴侣

 

[羊皮]

当CSV包含空白行时，这是完全正常的。因此，当其迭代或解析空白内容时 线，导致异常发生。在注释上方的第25行之后添加此代码：

            IEnumerable<string> eachBlankLine = File.ReadAllLines(pathToFile).Where(emptyLine => !string.IsNullOrWhiteSpace(emptyLine));
            File.WriteAllLines(pathToFile, eachBlankLine);