How to uplad csv file and save data in DataTable

Question

Hi

I have .csv file and want to upload its data in datatable. I am trying below code but it is giving Invalid File Signature

public static DataTable ReadCSVFile(string fileName)
{
    using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
    {
        // Auto-detect format, supports:
        //  - Binary Excel files (2.0-2003 format; *.xls)
        //  - OpenXml Excel files (2007 format; *.xlsx, *.xlsb)
        using (var reader = ExcelReaderFactory.CreateReader(stream))
        {
            do
            {
                while (reader.Read())
                {
                    // reader.GetDouble(0);
                }
            } while (reader.NextResult());


            DataSet result = reader.AsDataSet();
            return result.Tables[0];
        }
    }
}

Thanks

Amit Mohanty · Answer

With Tab delimeter. string filePath = @"C:\path	o\your\file.csv"; DataTable dataTable = new DataTable(); using (StreamReader streamReader = new StreamReader(filePath)) { string[] headers = streamReader.ReadLine().Split(','); foreach (string header in headers) { dataTable.Columns.Add(header); } while (!streamReader.EndOfStream) { string[] rows = streamReader.ReadLine().Split('	'); DataRow dataRow = dataTable.NewRow(); for (int i = 0; i

Tuhin Paul · Answer

A filePath variable is declared that contains the path to the CSV file to be read. A new DataTable object is created to hold the CSV data. The StreamReader class is used to open the CSV file located at filePath and read its contents. The first line of the CSV file is read using ReadLine(), which contains the column headers of the table. The column headers are split into an array of strings using the Split() method with a comma as the delimiter. A foreach loop is used to iterate over each header in the array of headers, and a new DataColumn is created with the header name and added to the DataTable using the Columns.Add() method. The remaining lines of the CSV file are read using a while loop, which continues until EndOfStream is reached. Each row is read using ReadLine(), which returns a string containing comma-separated values. The row string is split into an array of strings using the Split() method with a comma as the delimiter. A new DataRow object is created using NewRow(), and each value in the row array is assigned to the corresponding column using the Item[] property of the DataRow object. The DataRow object is added to the DataTable using the Rows.Add() method. After all rows have been read and added to the DataTable, the resulting DataTable object is returned, containing all of the data from the CSV file in a tabular format.

Tuhin Paul · Answer

string filePath = @"C:\path	o\your\file.csv"; var dataTable = new DataTable(); using (var streamReader = new StreamReader(filePath)) { var headers = streamReader.ReadLine().Split(','); dataTable.Columns.AddRange(headers.Select(h => new DataColumn(h)).ToArray()); while (!streamReader.EndOfStream) { var rows = streamReader.ReadLine().Split(','); var dataRow = dataTable.Rows.Add(); dataRow.ItemArray = rows; } }

Amit Mohanty · Answer

string filePath = @"C:\example\file.csv"; DataTable dataTable = new DataTable(); using (StreamReader reader = new StreamReader(filePath)) { // Read the first line of the CSV file to get the column names string[] columnNames = reader.ReadLine().Split(','); foreach (string columnName in columnNames) { dataTable.Columns.Add(columnName); } while (!reader.EndOfStream) { string[] fields = reader.ReadLine().Split(','); DataRow dataRow = dataTable.NewRow(); for (int i = 0; i

Naimish Makwana · Answer

It seems like you are using the ExcelReaderFactory to read a CSV file. However, as the name suggests, ExcelReaderFactory is used to read Excel files, not CSV files. To read a CSV file and load its data into a DataTable, you can use the TextFieldParser class which is available in the Microsoft.VisualBasic.FileIO namespace. Here's an example of how you can modify your code to use TextFieldParser : using Microsoft.VisualBasic.FileIO; public static DataTable ReadCSVFile(string fileName) { DataTable dataTable = new DataTable(); using (TextFieldParser parser = new TextFieldParser(fileName)) { parser.TextFieldType = FieldType.Delimited; parser.SetDelimiters(","); string[] headers = parser.ReadFields(); foreach (string header in headers) { dataTable.Columns.Add(header); } while (!parser.EndOfData) { string[] fields = parser.ReadFields(); DataRow row = dataTable.NewRow(); for (int i = 0; i Thanks

Ramco Ramco · Answer

Hi AMit I have written below . Is it o.k parser.TextFieldType = FieldType.Delimited; parser.SetDelimiters("	"); Thanks

Ramco Ramco · Answer

Hi all I think separator is tab delimited. I am downloading file from facebook. it is showing as id	created_time and so on Thanks

Ramco Ramco · Answer

Hi Amit same issue which i just mentioned I have written below line. Is hould show only id var colname = dt.Columns[0].ColumnName; and it shows like this id created_time ad_id ad_name adset_id adset_name campaign_id campaign_name form_id form_name is_organic platform enter_your_child's_name enter_your_child's_age full_name city phone_number Thanks