如何将输入文件中的行分成两部分,然后与c#中数据库表的两列数据进行比较
本文关键字:数据库 两列 比较 数据 然后 文件 输入 两部分 | 更新日期: 2023-09-27 18:19:23
给定一个文本文件,如何读取一行中的特定数字?
比如说,我有一个123.txt文件。我该如何读取行号并将前5位存储在不同的变量中,并将后6位存储在另一个变量中?
我所看到的都是涉及将整个文本文件存储为字符串数组的东西。但也有一些复杂的地方:文本文件非常大,我编写的应用程序所在的机器也不是一流的系统。速度不是最重要的,但它绝对是一个主要问题。
//Please Help here
// Want to compare data of input file with database table columns.
// How to split data in to parts
// Access that split data later for comparison.
// Data in input file is like,
//
// 016584824684000000000000000+
// 045787544574000000000000000+
// 014578645447000000000000000+
// 047878741489000000000000000+ and so on..
string[] lines = System.IO.File.ReadAllLines("F:''123.txt"); // Input file
// How can I divide lines from input file in 2 parts (For ex. 01658 and 4824684) and save it in variable so that I can use it for comparing later.
string conStr = ConfigurationManager.ConnectionStrings["BVI"].ConnectionString;
cnn = new SqlConnection(conStr);
cnn.Open();
// So I want to compare first 5 digits of all lines of input file (ex. 01658)with Transit_ID and next 6 digits with Client_Account and then export matching rows in excel file.
sql = "SELECT Transit_ID AS TransitID, Client_Account AS AccountNo FROM TCA_CLIENT_ACCOUNT WHERE Transit_ID = " //(What should I put here to comapare with first 5 digits of all lines of input file)" AND Client_Account = " ??" );
我所看到的只是将整个文本文件存储为字符串数组
大型文本文件应该一次流式处理一行,这样您就不会不必要地分配大量内存
using (StreamReader sr = File.OpenText(path))
{
string s;
while ((s = sr.ReadLine()) != null)
{
// How would I go about reading line number and store first 5
// digits in different variable and next 6 digits to another variable.
string first = s.Substring(0, 5);
string second = s.Substring(6, 6);
}
}
https://msdn.microsoft.com/en-us/library/system.io.file.opentext (v = vs.110) . aspx
使用Substring(int32, int32)
来获取合适的值,如下所示:
string[] lines = System.IO.File.ReadAllLines("F:''123.txt");
List<string> first = new List<string>();
List<string> second = new List<string>();
foreach (string line in lines)
{
first.Add(line.Substring(0, 5));
second.Add(line.Substring(6, 6));
}
虽然Eric的回答更简洁。这只是使用示例数据的一个快速而粗糙的概念证明。你一定要按照他的建议使用using
语句和StreamReader
。
first
将包含lines
中每个元素的前5位数字,second
将包含后面的6位数字。
然后构建SQL,你可以这样做;
sql = "SELECT Transit_ID AS TransitID, Client_Account AS AccountNo FROM TCA_CLIENT_ACCOUNT WHERE Transit_ID = @TransitId AND Client_Account = @ClientAcct");
SqlCommand cmd = new SqlCommand(sql);
for (int i = 0; i < lines.Count; i++)
{
cmd.Parameters.AddWithValue("@TransitId", first[i]);
cmd.Parameters.AddWithValue("@ClientAcct", second[i]);
//execute your command and validate results
}
这将循环N次,并为lines
中的每个值运行一个命令。