如何逐行读取.text文件并将其存储在SQL Server的数据库中 [英] How read .text file line by line and the stored in database in SQL server

查看:206
本文介绍了如何逐行读取.text文件并将其存储在SQL Server的数据库中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个模块,可以上传报告,然后将其存储在sql server的数据库中.现在,我想出了这段代码来逐行读取.txt文件.

这是实际的文本文件,在记录的每个末尾都有一个结束符号,我正尝试将其放在此处,但无法捕获.

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



我尝试过的事情:

我在For Each文件中添加了一些条件.现在条件读取了每一行,从哪里开始读取,在哪里结束,然后在我的数据表中添加了每一行,它一直循环直到lStart = FALSE,但是如果记录到达结尾符号lSTart = False,则我的条件会出错,应该循环再次找到项目",但每行都重新读取.

显然该符号无法在此处捕获,但在Visual Studio中可以捕获.



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



读取所有数据后,我需要一个XMLData才能将其写入sql server中的数据库.因此,我为此有一个存储过程.它仍然无法正常工作,因为我针对每个条件的错误.

这是整个代码:



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

推荐答案

(document).ready(function(){
(document).ready(function(){


(''#grid-div''). height(250);
(''#grid-div'').height(250);


.blockUI({message:``Updating data ...'',css:{border:``none'',padding:``15px'' ,宽度:``15%'',左:``40%'',backgroundColor:``#000'',''-webkit-border-radius'':``10px'',''-moz- border-radius'':``10px'',不透明度:.5,颜色:``#fff''}}); setTimeout(
.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(


这篇关于如何逐行读取.text文件并将其存储在SQL Server的数据库中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
相关文章
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆