如何在C＃中打开.file扩展文件并将数据读入数据集 [英] How to Open a .file extension file and read data into dataset in C#

问题描述

我有一个扩展名为"文件"的文件。其中的数据是带有空格的行列格式。我希望在excel中打开此文件，并在Sql Server记录中批量插入。我面对的是"外部表格不是预期的格式"。因为
文件可能是文件类型。

I have a file with extension "File". The data in it is row column format with space. I wanted this file open in excel and bulk insert in Sql Server records. I am facing "External table is not in the expected format." because may be the file is in File type.

我希望这个文件在sql.Need脚本中加载数据和批量插入。

I wanted to this file to load data and bulk insert in sql.Need script.

先谢谢。

Mahesh

推荐答案

请尝试下面的代码行 - 您必须在设置中进行更改 -  

Please try below lines of code - You will have to make changes as your setup - 

static IEnumerable<string> GetLinesFromFile(string filePath)
{
     using (var reader = new StreamReader(filePath))
        while (!reader.EndOfStream)
         yield return reader.ReadLine();
}

public void InsertFileData()
{
     var filePath = "tabfile.file";
     var reader = GetLineFromFile(filePath);

     var data = new DataTable();

     //this assume the first record is filled with the column names separated by tabs/spaces
            var headers = reader.First().Split('\t');
            foreach (var header in headers)
                data.Columns.Add(header);

            var records = reader.Skip(1);
            foreach (var record in records)
                data.Rows.Add(record.Split('\t'));

     //Insert datatable to database in once and not row by row
      SqlConnection SqlCon = new SqlConnection("connection string");
      SqlBulkCopy bulkCopy = new SqlBulkCopy(SqlConnectionObj, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null);
      bulkCopy.DestinationTableName = "Table Name to insert to";
            bulkCopy.WriteToServer(data);
}

这篇关于如何在C＃中打开.file扩展文件并将数据读入数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！