将数据从 CSV 排序到 TXT
本文关键字：TXT 排序 CSV 数据 | 更新日期: 2023-09-27 18:33:10
我正在编写一个应用程序，它将数据从csv整理到txt。
我有智慧，但我无法获得所需的输出。
有人可以帮忙吗，我没有看到我错了。
我最初认为是File.WriteAllLines是问题所在，但即使我写入控制台，我也得到了相同的结果。
我的文件看起来像这样
乔治娜，辛克莱，408999703657，支票，第一国民银行，Fourways，275.00,12/01/2012 Zachary，Whitehead，409122372301，支票，ABSA，Irene，70.25,12/01/2012 托比，亨德森，401255489873，支票，第一国民银行，伊登维尔，181.03,12/13/2012 凯瑟琳，库克，409155874935，储蓄，ABSA，南唐斯，975.89,01/01/2013 布拉德利，詹姆斯，409254998，储蓄，ABSA，梅尔维尔，207.74,12/09/2012 苏菲，莱恩，409771987，储蓄，ABSA，罗德普特，207.74,2012/12/31
我的输出应该是这样的
First National B0020000045603
GSinclair 408999703657 CH Fourways 002750001122012
THenderson 401255489873 CH Edenvale 001810313122012
ABSA 0040000146162
ZWhitehead 409122372301 CH Irene 000702501122012
KCooke 409155874935 SAVSouthdowns009758901012013
BJames 409254998 SAVMelville 002077409122012
SLane 409771987 SAVRoodepoort002077431122012
我目前拥有的代码仅返回标题和 2 行，如下所示。
ABSA 0040000146162
KCooke 409155874935 SAVSouthdowns 009758901012013
请协助。
我的代码如下所示
string text = @"C:''Test''output.txt";
var inputEntries = File.ReadLines(@"C:''Test''debitorders.csv").Select(line =>
{
var values = line.Split(',');
return new
{
accountholder = values[0].Trim().Substring(0, 1) + values[1].Trim(),
accountnumber = long.Parse(values[2].Trim()),
accounttype = values[3].Trim(),
bankname = values[4].Trim(),
branch = values[5].Trim(),
amount = 100 * double.Parse(values[6].Trim()),
date = DateTime.Parse(values[7].Trim())
};
});
var banks = inputEntries
.OrderBy(e => e.bankname)
.GroupBy(e => e.bankname, e => e);
foreach (var bank in banks)
{
var AccountName = bank.Key;
if (AccountName.Length >= 20)
{
AccountName = AccountName.Substring(0, 16);
}
else
{
AccountName += new string(' ', 20 - AccountName.Length);
}
var NumberOfAccounts = bank.Count();
var TotalAmount = bank.Select(acc => acc.amount).Sum();
var Header = AccountName + "'t" + NumberOfAccounts.ToString("000") + TotalAmount.ToString("0000000000");
var sortedAccounts = bank
.OrderBy(acc => acc.accountholder)
.OrderByDescending(acc => acc.amount);
foreach (var account in sortedAccounts)
{
var outputLine =
account.accountholder + "'t" +
account.accountnumber + "'t" +
//get first 2 characters
account.accounttype.Substring(0, 3).ToUpper() + account.branch + "'t" + "00" +
account.amount +
account.date.ToString("ddMMyyyy");
for (int i = 0; i < 15; i++)
{
File.WriteAllText(text, Header + Environment.NewLine + outputLine);
Console.WriteLine(Header + outputLine);
Console.ReadLine();
}
}
}
更好、更干净的解决方案是利用添加文本的List<string>。在代码结束时，只需将列表转换为数组并将所有行写入文件即可。
List<string> outputLine = new List<string>(); //note this addition to the code
foreach (var bank in banks)
{
//do header formatting stuff here
var Header = somecode
outputLine.Add(Header); //Add Header to outputLine
var sortedAccounts = bank.OrderBy(acc => acc.accountholder)
.OrderByDescending(acc => acc.amount);
foreach (var account in sortedAccounts)
{
var tempStringBuilder =
account.accountholder + "'t" +
account.accountnumber + "'t" +
//get first 2 characters
account.accounttype.Substring(0, 3).ToUpper() + account.branch + "'t" + "00" +
account.amount +
account.date.ToString("ddMMyyyy");
outputLine.Add(tempStringBuilder); //Add tempStringBuilder to outputLine
}
}
File.WriteAllLines("destination path", outputLine.ToArray()); //Write everything to your output file in one go
替代Excel解决方案：
Microsoft Excel有一个非常强大的工具称为 Pivot Tables ，非常适合您的需求。如果您不熟悉它，请阅读一些有关它的教程。起初，这是一个了解工作流程以使用它的过程，但一旦掌握了它，它就非常简单了。您只需拖放要分组的字段。
您可能还需要考虑使用 Data Connections 链接到原始数据，考虑到您拥有的数据集，这也非常简单。
我想
我找到了解决方案：
File.AppendAllText(text, Header + Environment.NewLine + outputLine + Environment.NewLine);
使用 File.AppendAllText 而不是 File.WriteAllText。使用WriteAllText，您总是删除旧内容。但是考虑在开始写入之前清理文件(File.WriteAllText(text, "");(，否则您还将包含上次运行的旧数据。
尝试使用String.Format("{0,-10}", name)这意味着名称的长度被最多 10 长度的空格填充。减号表示左对齐，正表示右对齐。
我将您的代码更新为：
string text = @"D:'C#'output.txt";
File.WriteAllText(text, "");
var inputEntries = File.ReadLines(@"D:'c#'debitorders.csv").Select(line =>
{
var values = line.Split(',');
return new
{
accountholder = values[0].Trim().Substring(0, 1) + values[1].Trim(),
accountnumber = long.Parse(values[2].Trim()),
accounttype = values[3].Trim(),
bankname = values[4].Trim(),
branch = values[5].Trim(),
amount = 100 * double.Parse(values[6].Trim()),
date = DateTime.ParseExact(values[7].Trim(), "MM/dd/yyyy", CultureInfo.InvariantCulture)
};
});
var banks = inputEntries.OrderBy(e => e.bankname)
.GroupBy(e => e.bankname, e => e);
foreach (var bank in banks)
{
var AccountName = bank.Key;
var NumberOfAccounts = bank.Count();
var TotalAmount = bank.Select(acc => acc.amount).Sum();
var Header = String.Format("{0,-20} {1,-10} {2}", AccountName, NumberOfAccounts.ToString("000"), TotalAmount.ToString("0000000000"));
var sortedAccounts = bank.OrderBy(acc => acc.accountholder)
.OrderByDescending(acc => acc.amount);
File.AppendAllText(text, Header + Environment.NewLine);
Console.WriteLine(Header);
foreach (var account in sortedAccounts)
{
var outputLine = String.Format("{0,-11} {1,15} {2,-3} {3,-10} {4,7} {5,-10}",
account.accountholder,
account.accountnumber,
account.accounttype.Substring(0, 3).ToUpper(),
account.branch,
account.amount,
account.date.ToString("ddMMyyyy")
);
//get first 2 characters
//account.accounttype.Substring(0, 3).ToUpper() + account.branch + "'t" + "00" +
// what are the "00" for? didn't include them you may this do by yourself
File.AppendAllText(text, outputLine + Environment.NewLine);
Console.WriteLine(outputLine);
}
File.AppendAllText(text, Environment.NewLine);
Console.WriteLine();
}
输出为：
ABSA 004 0000146162
KCooke 409155874935 SAV Southdowns 97589 01012013
BJames 409254998 SAV Melville 20774 09122012
SLane 409771987 SAV Roodepoort 20774 31122012
ZWhitehead 409122372301 CHE Irene 7025 01122012
First National Bank 002 0000045603
GSinclair 408999703657 CHE Fourways 27500 01122012
THenderson 401255489873 CHE Edenvale 18103 13122012