如何在C＃中读取没有标题列的REQ文件 [英] How do I read REQ files without header column in C#

问题描述

我有这个c＃控制台应用程序代码来读取文件夹中的REQ文件，它可以使用标题列，但是我的REQ文件没有标题列，例如我的意思是：

I have this c# console application code to read REQ files from folder and it work with header column ,but my REQ file is without header column this e.g. of what I mean :

123123*JAN*100001*[e-mail]*CREDITADVICE*SDG*1000000*20170620*F0000999*10.105.1.20

此代码：

this the code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data.SqlClient;
using System.Data;


namespace readREQ
{
    class Program
    {
        static void Main(string[] args)
        {

            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            string LogFolder = @"C:\Log\";
            try
            {

                //Declare Variables and provide values
                string SourceFolderPath = @"C:\Source\"; //Provide the Source Folder where files are present
                string FileExtension = ".txt"; //Provide the extension of files you need to load, can be .txt or .csv
                string FileDelimiter = ","; // provide the file delimiter such as comma or pipe
                string ArchiveFolder = @"C:\Archive\"; //Provide the archive folder path where files will be moved
                string TableName = "dbo.Customer"; //Provide the table name in which you would like to load the files.


                //Create Connection to SQL Server in which you like to load files
                SqlConnection SQLConnection = new SqlConnection();
                    SQLConnection.ConnectionString = "Data Source = (local); Initial Catalog =TechBrothersIT; "
                       + "Integrated Security=true;";

                //Reading file names one by one
                string[] fileEntries = Directory.GetFiles(SourceFolderPath, "*" + FileExtension);
                foreach (string fileName in fileEntries)
                {

                    //Writing Data of File Into Table
                    int counter = 0;
                    string line;
                    string ColumnList = "";

                    System.IO.StreamReader SourceFile =
                    new System.IO.StreamReader(fileName);
                    SQLConnection.Open();
                    while ((line = SourceFile.ReadLine()) != null)
                    {
                        if (counter == 0)
                        {
                            //By using Header Row, Build Column List
                            ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";

                        }
                        else
                        {

                            //Build and Execute Insert Statement to insert record
                            string query = "Insert into " + TableName + " (" + ColumnList + ") ";
                            query += "VALUES('" + line.Replace(FileDelimiter, "','") + "')";

                            SqlCommand SQLCmd = new SqlCommand(query, SQLConnection);
                            SQLCmd.ExecuteNonQuery();
                        }

                        counter++;
                    }

                    SourceFile.Close();
                    SQLConnection.Close();
                    //move the file to archive folder after adding datetime to it
                    File.Move(fileName, ArchiveFolder + "\\" + 
                        (fileName.Replace(SourceFolderPath, "")).Replace(FileExtension, "")
                        + "_" + datetime + FileExtension);                   
                }
            }
            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                   
                }

            }

        }
    }
}

我尝试过：



我试图删除这些行

What I have tried:

I have try to delete these line

if (counter == 0)
                        {
                            //By using Header Row, Build Column List
                            ColumnList = "[" + line.Replace(FileDelimiter, "],[") + "]";

                        }

但仍有错误

but still there is error

推荐答案

就我而言可以看到代码依赖于以下事实：

- 文件有一个标题行

- 文件中的标题与目标表列名相匹配



所以只是删除读取标题行不起作用，因为没有信息应该将数据插入哪些列。



也许您可以在INSERT命令中修复列名，然后在INSERT语句中获取适当的值。由于我不知道文件背后的逻辑，我可以做的唯一假设是，文件中数据列的顺序可能是已知的，您可以使用此信息来匹配数据库中的列。



作为旁注，除非在SQL命令中使用参数，否则最终会遇到问题。例如，数据中包含字符'会破坏您的SQL语句。请查看正确执行数据库操作 [ ^ ]

As far as I can see the code relies on the fact that:
- the file has a header row
- the headers in the file match the target table column names

So just removing reading the header row doesn't work since there is no information into which columns the data should be inserted into.

Perhaps you could fix the column names in your INSERT command and then fetch appropriate values into INSERT statement. Since I do not know the logic behind the files the only assumption I can make is that perhaps the order of the data columns in the file is known and you can use this information to match the columns in the database.

As a side note, you're going to end up into problems unless you use parameters in your SQL commands. For example having a character ' in the data would break your SQL statement. Have a look at Properly executing database operations[^]

这篇关于如何在C＃中读取没有标题列的REQ文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！