The following code will:
- Import csv file into database
- Validate csv file before import
- Create Schema.ini ==> Schema.ini is a information file, used to define the data structure and format of each column that contains data in the CSV file.
- Check Duplicate Rows
- Check Column Header Exist
Private Function GetCSV() As System.Data.DataTable
If ViewState("SaveLocation") Is Nothing Then
Return Nothing
End If
Dim SaveLocation As String = ViewState("SaveLocation").ToString()
Using conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " & Path.GetDirectoryName(SaveLocation) & "; Extended Properties = ""Text;HDR=YES;FMT=Delimited""")
conn.Open()
Dim strQuery As String = "SELECT * FROM [" & Path.GetFileName(SaveLocation) & "]"
Dim adapter As New System.Data.OleDb.OleDbDataAdapter(strQuery, conn)
Dim ds As New System.Data.DataSet("CSV File")
adapter.Fill(ds)
Return ds.Tables(0)
End Using
End Function
Private Function UploadCSV() As Boolean
If Not Path.GetExtension(fileUploadCSV.PostedFile.FileName).ToLower.Equals(".csv") Then
lblMsg.Text = MessagesDAL.Instance.Get_MessageByID(Messages.MessageCodes.CSVExtensionRequired)
lblMsg.ForeColor = Drawing.Color.Red
Return False
End If
If Not fileUploadCSV.PostedFile Is Nothing And fileUploadCSV.PostedFile.ContentLength > 0 Then
Dim fn As String = Path.GetFileName(fileUploadCSV.PostedFile.FileName)
Dim SaveLocation As String = Path.Combine(Server.MapPath("CSVUploaded"), fn)
Try
fileUploadCSV.PostedFile.SaveAs(SaveLocation)
CreateSchemaFile(fn)
ViewState("SaveLocation") = SaveLocation
Catch Exc As Exception
lblMsg.Text = "Error: " & Exc.Message
lblMsg.ForeColor = Drawing.Color.Red
Return False
End Try
Else
lblMsg.Text = MessagesDAL.Instance.Get_MessageByID(Messages.MessageCodes.UploadFileRequired)
lblMsg.ForeColor = Drawing.Color.Red
Return False
End If
Return True
End Function
Private Function IsColumnHeaderExist(ByVal columnName As String) As Boolean
If ViewState("SaveLocation") Is Nothing Then
Return Nothing
End If
Dim SaveLocation As String = ViewState("SaveLocation").ToString()
Dim sr As StreamReader = New StreamReader(SaveLocation)
Dim headerline As String = sr.ReadLine()
sr.Close()
Dim values As String()
values = headerline.Split(",")
For i As Integer = 0 To values.Length - 1
If values(i).ToString.Equals(columnName) Then
Return True
End If
Next
Return False
End Function
Private Function CheckDuplicateRows() As Integer
If ViewState("SaveLocation") Is Nothing Then
Return Nothing
End If
Dim SaveLocation As String = ViewState("SaveLocation").ToString()
Dim FileContent As String() = File.ReadAllLines(SaveLocation)
Dim al As New ArrayList()
Dim lineNo As Integer = 1
'ignore the header row
For i As Integer = 1 To FileContent.Length - 1
lineNo += 1
If al.Contains(FileContent(i)) Then
Return lineNo
Else
al.Add(FileContent(i))
End If
Next
Return 1
End Function
Private Sub CreateSchemaFile(ByVal fileName As String)
'Create a schema.ini file to define data types for every csv file uploaded
'Otherwise the OleDbDataAdapter will convert e.g. 01/01/09 to datetime automatically rather than string
Dim strFilePath As String = Server.MapPath("CSVUploaded")
Using filestr As New FileStream(strFilePath & "\schema.ini", FileMode.Create, FileAccess.Write)
Using writer As New StreamWriter(filestr)
writer.WriteLine("[" & fileName & "]")
writer.WriteLine("ColNameHeader=True")
writer.WriteLine("Format=CSVDelimited")
writer.WriteLine("Col1=""Agent Registration Number"" Text")
writer.WriteLine("Col2=""Agent Family Name"" Text")
writer.WriteLine("Col3=""CPD Activity Item Number"" Long")
writer.WriteLine("Col4=""CPD Activity Start Date"" Text")
writer.WriteLine("Col5=""CPD Activity Start Time"" Text")
writer.WriteLine("Col6=""CPD Activity End Date"" Text")
writer.WriteLine("Col7=""CPD Activity End Time"" Text")
writer.WriteLine("Col8=Location Text")
writer.Close()
writer.Dispose()
End Using
filestr.Close()
filestr.Dispose()
End Using
End Sub
References:
Importing CSV file into Database with Schema.ini
