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

Question

How do i read and import piple delimited files to the database. File has data which has line break.

I tried SQL Sever File Import wizzard as well as CSV Helper Library. Could you please help me how do I read the data successfully which has line break.

example,

unique_id|name|notes
123|John|Subject: Meet the new interns
Dear team,
I'm happy to inform you of the commencement of our summer internship program. We recruited 10 interns
456|Rit|Subject: Meet the new interns
Dear team,
I'm happy to inform you of the commencement of our summer internship program. We recruited 10 interns

Amit Mohanty · Answer

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(); } } } }

Jignesh Kumar · Answer

Hello Rana, You can try below code, using Microsoft.VisualBasic.FileIO; using System.IO; string filePath = @"C:\path	o\your\file.csv"; using (TextFieldParser parser = new TextFieldParser(filePath)) { parser.TextFieldType = FieldType.Delimited; parser.SetDelimiters("|"); parser.TrimWhiteSpace = true; parser.HasFieldsEnclosedInQuotes = true; parser.NewLine = "
"; // or "
" 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 } }

Istudent Rana · Answer

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

Pasang Tamang · Answer

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