How do I read csv file which has data with line break.

Hello Rana,

You can try below code,

using Microsoft.VisualBasic.FileIO;
using System.IO;

string filePath = @"C:\path\to\your\file.csv";

using (TextFieldParser parser = new TextFieldParser(filePath))
{
    parser.TextFieldType = FieldType.Delimited;
    parser.SetDelimiters("|");
    parser.TrimWhiteSpace = true;
    parser.HasFieldsEnclosedInQuotes = true;
    parser.NewLine = "\r\n"; // or "\n" depending on the line break character used in the file
    
    while (!parser.EndOfData)
    {
        string[] fields = parser.ReadFields();
         // Here you will get all fields in string array
        // process the data to save SQL Server or any other database
    }
}

You can use a library like CsvHelper to read the data from your CSV file into a list of strings.
Here's an example code:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand())
    {
        command.Connection = connection;
        // Read the data from the CSV file using CsvHelper
        using (var reader = new StreamReader("path/to/file.csv"))
        using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
        {
            csv.Configuration.Delimiter = "|"; // Set the delimiter to pipe
            var records = csv.GetRecords().ToList();
            foreach (var record in records)
            {
                command.CommandText = $"INSERT INTO MyTable (Column1, Column2, Column3) VALUES ('{record[0]}', '{record[1]}', '{record[2]}')";
                command.ExecuteNonQuery();
            }
        }
    }
}

@Pasang Tamang, I can not do that way as the last data has line break.

Hi,

You can read the file using FileStream and split the file content with '|'. Please check below code

using System;
using System.IO;

class Program
{
    static void Main(string[] args)
    {
        string filePath = "";
        using (var fileStream = new FileStream(filePath, FileMode.Open))
        using (var reader = new StreamReader(fileStream))
        {
            string headerLine = reader.ReadLine();
            while (!reader.EndOfStream)
            {
                string dataLine = reader.ReadLine();
                string[] data = dataLine.Split('|');
                string uniqueId = data[0];
                string name = data[1];
                string notes = data[2];
				//you can add your database logical code here
                Console.WriteLine($"ID: {uniqueId}, Name: {name}, Notes: {notes}");
            }
        }
    }
}

Regards,
Pasang