如何逐行读取.text文件并将其存储在SQL Server的数据库中 [英] How read .text file line by line and the stored in database in 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屋!