Read the CSV Data and Save the Data in DataSet.

In this first we will read the data from csv file using the select query and load the data into a dataset.

Query To read data from CSV file:

SELECT * FROM [test.csv]

OLEDB Connectionstring:
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\Lakhan\Projects\Testweb\Test\;Extended
Properties=""text;HDR=Yes;FMT=Delimited"""


if you want to consider first row as column then mention HDR=Yes otherwise no.
using System.Data.OleDb;
for(Int32 i=0; i
{
Response.Write(dataSetFromCSV.Tables[0].Columns[i].
ColumnName + "");
}

the above code is used to print all the column names


Sample Code:
using System.Data.OleDb;
private void ReadCSVFile()
{
string cnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=E:\Lakhan\Projects\Testweb\Test\;Extended
Properties=""text;HDR=Yes;FMT=Delimited""";
OleDbConnection ExcelConnection =
new OleDbConnection(cnStr);
OleDbCommand ExcelCommand = new OleDbCommand
(@"SELECT * FROM [test.csv]",
ExcelConnection);
OleDbDataAdapter ExcelAdapter =
new OleDbDataAdapter(ExcelCommand);
ExcelConnection.Open();

DataSet dataSetFromCSV = new DataSet();
ExcelAdapter.Fill(dataSetFromCSV);
ExcelConnection.Close();
for(Int32 i=0; i

{
Response.Write(dataSetFromCSV.Tables[0].
Columns[i].ColumnName + "");
}
}


CSV File's Content:
Customer Number,Last Name,First Name,Address,City,Province,
Postal Code,Balance10001,Smith,Dave,123 Parkside Ave.,London,
ON,N6J 4G6,125.3510002,Pearson,Anne,44 Northside Road,Toronto,
ON,N0M 5L8,38.1210003,Carson,Ronald,12 Talbot Road,London,
ON,N6U 3G8,1024.5610006,Davis,Albert,19 Southam Road,Ajax,
ON,N7J 5H7,-8.5510007,Anderson,Theresa,118 Sarnia Road,
London,ON,N6G 5C6,1181.1210009,Jones,Jason,1008,
Carver Place,Ottawa,ON,N8K 8H4,0.00

0 comments: