Hello Friends,
Many times we require a logic by which we can import a csv file in grid or database.So don't worry its not a big issue now For this I am taking below example this example is in VB.net (windows application)
Suppose I have csv file with following values
"ADDRESSID,ACCOUNTNO,ADDRESSTYPE,CODE,NAME,COMPANYNAME,ADDR1,ADDR2,ADDR3,
ADDR4,CITY,STATE,PROVINCE,ZIPCODE,POSTALCODE,COUNTRY,PHONENO,
EXTENSION,NOTIFICATIONEMAIL"
Or we can say above is the column name in this order the csv file is arrange.
So first what we do we just read the file for this
Me.OpenFileDialog1.Multiselect = False
Me.OpenFileDialog1.ShowDialog()
Me.txtPath.Text = Me.OpenFileDialog1.FileName
Dim sReader As New StreamReader(Me.txtPath.Text)
Dim strFile As String = sReader.ReadToEnd
sReader.Close()
Now in next step what we do we create a table with column which are exist in csv file remember we have to use same order for this we will write following code
Dim tbl As New DataTable
tbl.Columns.Add(New DataColumn("ADDRESSID"))
tbl.Columns.Add(New DataColumn("ACOUNTNO"))
tbl.Columns.Add(New DataColumn("ADDRESSTYPE"))
tbl.Columns.Add(New DataColumn("CODE"))
tbl.Columns.Add(New DataColumn("NAME"))
tbl.Columns.Add(New DataColumn("COMPANYNAME"))
tbl.Columns.Add(New DataColumn("ADDR1"))
tbl.Columns.Add(New DataColumn("ADDR2"))
tbl.Columns.Add(New DataColumn("ADDR3"))
tbl.Columns.Add(New DataColumn("ADDR4"))
tbl.Columns.Add(New DataColumn("CITY"))
tbl.Columns.Add(New DataColumn("STATE"))
tbl.Columns.Add(New DataColumn("PROVINCE"))
tbl.Columns.Add(New DataColumn("ZIPCODE"))
tbl.Columns.Add(New DataColumn("POSTALCODE"))
tbl.Columns.Add(New DataColumn("COUNTRY"))
tbl.Columns.Add(New DataColumn("PHONENO"))
tbl.Columns.Add(New DataColumn("EXTENSION"))
tbl.Columns.Add(New DataColumn("NOTIFICATIONEMAIL"))
One this done then what we do we read all the rows one by one and split it with comma seprated value and create row in our table.
Dim strReadLine() As String = Split(strFile, vbCrLf)
The above line just convert single line in multiline with delimiter Vbcrlf which means Line break
One this done then we have collection of individual row in same format in our variable strReadLine
Now we read each line from strReadLine and create new row in table column
For intCount As Integer = 1 To strReadLine.Length - 1
Dim strColumn() As String = strReadLine(intCount).Split(",")
Dim DataRow As DataRow = tbl.Rows.Add()
For intCol As Integer = 0 To strColumn.Length - 1
DataRow(intCol) = strColumn(intCol)
Next
Next
Me.dgv.DataSource = tbl
So in this way we import a csv file and bind that files value in a dataGrid.
Friends if you don't want to use vb.net and want directly import csv file in SQL Server then SQL Server support a unique sql command which is Bulk Copy
Suppose the file we have to import directly in a table which having this column then we have to write follow lines
BULK
INSERT tblImportTest
FROM 'c:\rajat.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
So I think friends the above code solve our problems in many ways hope you like article.
You can download code from http://www.indiandotnet.tk
Thanks
Enjoy codeing, Enjoy dot net :D
Rajat
