How read .text file line by line and the stored in database in SQL server

Question

0.00/5 (No votes)

See more:

I have a module which upload report and then stored in database in sql server. Now I have come up with this code to read .txt file line by line.

Here's the actual text file, in every end of the record there is an end symbol, I'm trying to put here but it can't capture.

  Item     Description              Rev      Locator          UOM     Quantity
  -------------------------   --- -------------------------    --- ------------
  604K23660     KIT-FDR HCF MSI              ANG.2.0.0        PC          1.00

  604K23670     KIT FEEDER TM                ANG.2.0.0        PC          3.00

  604K26021     LEN ASSY                     ANG.2.0.0        PC          1.00

  CT200541      AP/DCC4400/4300              ANG.2.0.0        Unt         3.00
                (Magenta)
  CT200542      AP/DCC4400/430               ANG.2.0.0        Unt         6.00

end symbol

 Subinventory: NEI-OLO    Description: Non-Equipment Inventory of Olongapo

Item     Description              Rev      Locator             UOM     Quantity
  --------------------------   --- ------------------------- --- ----------------
  CT350769      DC-III 3007/2007                               PC          7.00

  CT350851      AP/DC-IV C5570                                 PC          2.00
                Cartridge (1 piece)

end symbol

What I have tried:

I put some condition in my For Each for the file. Now the condition read each line where to start reading and where to end and then add each row in my datatable, it loop until lStart = FALSE but I have an error on my condition if the record reached the ending symbol lSTart = False it should loop again to find the "Item" but instead it read again every line.

Apparently the symbol can't capture here,but on visual studio it can.

For Each sline In sFileLines

                If sline.TrimStart.TrimEnd <> "" Then

                    If lStart = True Then

                        If sline.Substring(0, 10).TrimStart.TrimEnd <> "-------" Then

                            Dim newRow As DataRow = dt.NewRow

                            newRow("Item") = sline.Substring(0, 32).TrimStart.TrimEnd
                            newRow("Description") = sline.Substring(33, 53).TrimStart.TrimEnd
                            newRow("Rev") = sline.Substring(86, 3).TrimStart.TrimEnd
                            newRow("Locator") = sline.Substring(89, 26).TrimStart.TrimEnd
                            newRow("UOM") = sline.Substring(115, 3).TrimStart.TrimEnd
                            newRow("Quantity") = sline.Substring(124, 7).TrimStart.TrimEnd

                            dt.Rows.Add(newRow)

                        End If

                    End If

                    If sline.Substring(0, 32).TrimStart.TrimEnd = "Item" Then
                        lStart = True
                    End If

                    If sline.TrimStart.TrimEnd = "" Then
                                lStart = False
                    End If

After reading all the data, I need an XMLData to write this on the database in sql server. So I have a stored procedure for this. It is not working yet because the error on my For Each Condition.

Here's the whole code:

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


                'Read Line by Line

                Using sRead As New StreamReader(FileUpload1.PostedFile.FileName)
                    sFileName = sRead.ReadToEnd
                End Using

                Dim sFileLines() As String = sFileName.Split(vbCrLf)

                'Trim the line
                Dim dt As New DataTable("XmlData")

                dt.Columns.Add("Item", GetType(String))
                dt.Columns.Add("Description", GetType(String))
                dt.Columns.Add("Rev", GetType(String))
                dt.Columns.Add("Locator", GetType(String))
                dt.Columns.Add("UOM", GetType(String))
                dt.Columns.Add("Quantity", GetType(Double))

                Dim writer As New StringWriter
                Dim sline As String

                Dim i As Integer = 0
                Dim lStart As Boolean = False

                For Each sline In sFileLines

                    If sline.TrimStart.TrimEnd <> "" Then

                        If lStart = True Then

                            If sline.Substring(0, 10).TrimStart.TrimEnd <> "-------" Then

                                Dim newRow As DataRow = dt.NewRow

                                newRow("Item") = sline.Substring(0, 32).TrimStart.TrimEnd
                                newRow("Description") = sline.Substring(33, 53).TrimStart.TrimEnd
                                newRow("Rev") = sline.Substring(86, 3).TrimStart.TrimEnd
                                newRow("Locator") = sline.Substring(89, 26).TrimStart.TrimEnd
                                newRow("UOM") = sline.Substring(115, 3).TrimStart.TrimEnd
                                newRow("Quantity") = sline.Substring(124, 7).TrimStart.TrimEnd

                                dt.Rows.Add(newRow)

                            End If

                        End If

                        If sline.Substring(0, 32).TrimStart.TrimEnd = "Item" Then
                            lStart = True
                        End If

                        If sline.TrimStart.TrimEnd = "" Then
                            lStart = False
                        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("@XMLdata", SqlDbType.Xml) : .Parameters("@XMLdata").Value = writer.ToString
                            .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

I appreciate the suggestion and help, feel free to right my codes. Thanks

Posted 8-Feb-18 13:39pm

Member 13661245

Updated 8-Feb-18 17:08pm

v3

Add a Solution

Comments

Graeme_Grant 8-Feb-18 20:00pm

"It is not working yet because the error on my For Each Condition."

What is the error? Which line does it occur on?

Have you looked up what the error message is telling you? Have you googled it? Do you understand what the error message is telling you?

Member 13661245 8-Feb-18 20:46pm

I put some condition in my For Each for the file. Now the condition read each line where to start reading and where to end and then add each row in my datatable,
if sline <> "" then it will loop to If lStart = True, it will read or find sline = "Item" and then start reading each line and add row on my datatable up to the end symbol. I put some condition if lStart = "ending symbol" lstart will then be FALSE and it should start finding again the sline = 'Item' but instead it continuous reading each line and my error is that it reads the header on my datatable which every data table I set a substring.

Graeme_Grant 8-Feb-18 22:14pm

What is the error? Which line does it occur on?

Member 13661245 8-Feb-18 22:24pm

If sline.TrimStart.TrimEnd = "" Then
lStart = False
end if

Because the code can't identify the end symbol, it continuously looping and read also the header. Which got me an error on string length because I only have 6 row.

Graeme_Grant 8-Feb-18 22:43pm

So a logic error then and not a runtime error. See solution below.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Graeme_Grant · Answer 1 · 2018-02-08T17:08:00

Here is a logic error fix for you for identifying the line items:

VB

Dim isLineCapture = False

For Each line In IO.File.ReadAllLines(FileUpload1.PostedFile.FileName) _
                        .Select(Function(x) x.TrimStart())

    If isLineCapture AndAlso item.StartsWith("end symbol") Then
        ' stop processing immediately
        isLineCapture = False
    End If

    If isLineCapture Then
        ' process lines here
    End If

    If Not isLineCapture Then
        ' start processing from next line
        isLineCapture = item.StartsWith("---")
    End If

Next