So I have a text file with default .txt extension. Here's the actual file. (Tab Delimited)
<pre>Item Description Rev Locator UOM Quantity
003R94662 Colotech Plus ANG.2.0.0 PC 8.00
004E13450 PLATEN CUSHION ANG.2.0.0 PC 1.00
004K02371 CUSHION ASSY-PLATEN ANG.2.0.0 PC 1.00
005R00712 DC5000 Cyan Developer ANG.2.0.0 PC 1.00
005R00713 DC5000 Magenta Developer ANG.2.0.0 PC 1.00
I need to read text file and then split it to 6 columns (Item, Description, Rev, Locator, UOM and Quantity) and when it read and then split it should be stored in my table in sql server.
Thanks for your consideration. I have some experience with code, but I am just unfamiliar with dealing with text files and how to start. I saw some example and tried but not able to get correct results.
What I have tried:
I have a button click for uploading text file.
Here's the code I've tried
<pre>Protected Sub ibtnTxtUpload_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
Dim jsbldr As New StringBuilder
Try
If FileUpload1.HasFile Then
Dim Extension As String = Path.GetExtension(FileUpload1.PostedFile.FileName)
If Extension = ".txt" Then
Dim Request_Val As String = ""
Dim sFileName As String = FileUpload1.FileName
Dim dv_ As New DataView : dv_ = TryCast(Session("dbCache_DataType"), DataView)
dv = New DataView : dv = TryCast(Session("dbCache_FC_Template_ID"), DataView)
Dim col As String() = dv.Table.Rows(0).Item(0).ToString.Split("|")
'Read Line in Text File
Dim fileEntries As New ArrayList()
If Not File.Exists(sFileName) Then
Exit Sub
End If
Try
' Read the file into a list...
Dim reader As StreamReader = New StreamReader(sFileName)
fileEntries.Clear()
Do Until reader.Peek = -1 'Until eof
fileEntries.Add(reader.ReadLine)
Loop
reader.Close()
Catch ex As Exception
' The file's empty.
End Try
' Now we have the whole file in a list(Array)
Dim Item As New ArrayList()
Dim Description As New ArrayList()
Dim Rev As New ArrayList()
Dim Locator As New ArrayList()
Dim UOM As New ArrayList()
Dim Quantity As New ArrayList()
For Each line As String In fileEntries
If line.Substring(0, 5) <> "Index" Then
If line.Substring(0, 7) <> "Cluster" Then
line = line.Replace(" ", " ") 'space
line = line.Replace(" ", " ")
Dim parts() As String = Split(line, "\t")
' Add the data to your lists:
Try
Item.Add(parts(1))
Description.Add(parts(2))
Rev.Add(parts(3))
Locator.Add(parts(4))
UOM.Add(parts(5))
Quantity.Add(parts(6))
Catch ex As Exception
' If this fials, we're not where we want to be in the file anyway.
End Try
End If
End If
Next
'Execute SQL Stored Procedure
Using cn As New SqlConnection(cls.strConnString)
Using cmd As New SqlCommand()
With cmd
.CommandType = CommandType.StoredProcedure
.CommandText = "USP_XML_SOH_ACCOUNT_MAINT"
.Parameters.Add("@USR_ID", SqlDbType.VarChar, 30) : .Parameters("@USR_ID").Value = cls.GetUserName
.Parameters.Add("@FILE_NAME", SqlDbType.VarChar, 255) : .Parameters("@FILE_NAME").Value = sFileName
.Parameters.Add("@RET_VAL", SqlDbType.VarChar, 255)
.Parameters("@RET_VAL").Direction = ParameterDirection.Output
.Connection = cn
cn.Open()
.ExecuteNonQuery()
Request_Val = .Parameters("@RET_VAL").Value.ToString
End With
End Using
End Using
Page.ClientScript.RegisterClientScriptBlock(Me.GetType(), "Javascript", "<script>$(document).ready(function(){$('#grid-div').height(250);$.blockUI({message: 'Updating data...',css: {border: 'none',padding: '15px', width: '15%', left: '40%',backgroundColor: '#000','-webkit-border-radius': '10px','-moz-border-radius': '10px',opacity: .5,color: '#fff'}});setTimeout($.unblockUI, 2000); " & _
" $('#dialog-message').html('<table style=""border-style:hidden;""><tr><td style=""padding:5px;""> <img src=""images/gridview/check_circle_64x64.png""/></td><td>" & Request_Val.ToString & "</td></tr></table>');" & _
"$('div#dialog-message').dialog ({ my: 'center', at: 'center', of: window}).prev ().find ('.ui-dialog-titlebar-close').hide(); " & _
"var varCounter = 0; var varName = function(){ if(varCounter < 1){ varCounter++; $('#dialog-message').dialog('open'); } else {clearInterval(varName);}};setInterval(varName, 2000);}); </script>")
Else
jsbldr.Append("<script>")
jsbldr.Append("$(document).ready(function(){")
jsbldr.Append("$('div#dialog-message').dialog ({ my: 'center', at: 'center', of: window}).prev ().find ('.ui-dialog-titlebar-close').hide();")
jsbldr.Append("$('#dialog-message').html('<table style=""border-style:hidden;""><tr><td style=""padding:5px;""> <img src=""images/gridview/exclamation_circle_64x64.png""/></td><td>Invalid file format, please use .xlsx excel file extention.</td></tr></table>');")
jsbldr.Append("$('#dialog-message').dialog('open');")
jsbldr.Append("});")
jsbldr.Append("</script>")
Page.ClientScript.RegisterClientScriptBlock(Me.GetType(), "Javascript", jsbldr.ToString)
End If
End If
Catch ex As Exception
jsbldr.Append("<script>")
jsbldr.Append("$(document).ready(function(){")
jsbldr.Append("$('div#dialog-message').dialog ({ my: 'center', at: 'center', of: window}).prev ().find ('.ui-dialog-titlebar-close').hide();")
jsbldr.Append("$('#dialog-message').html('<table style=""border-style:hidden;""><tr><td style=""padding:5px;""> <img src=""images/gridview/exclamation_circle_64x64.png""/></td><td>Uploading SOH Account file template encounter error, please check the file.</td></tr></table>');")
jsbldr.Append("$('#dialog-message').dialog('open');")
jsbldr.Append("});")
jsbldr.Append("</script>")
Page.ClientScript.RegisterClientScriptBlock(Me.GetType(), "Javascript", jsbldr.ToString)
End Try
End Sub