读取文本文件,然后将其拆分并存储在SQL Server VB .NET中 [英] Reading text file then spliting it up in and stored in SQL server VB .NET
问题描述
所以我有一个扩展名为.txt的文本文件。这是实际的文件。 (制表符分隔)
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
我需要读取文本文件然后拆分它到6列(项目,描述,Rev,定位器,UOM和数量),当它读取然后拆分它应该存储在我的SQL Server服务器的表中。
感谢您的考虑。我有一些代码经验,但我对处理文本文件以及如何开始不熟悉。我看到了一些例子但尝试过但无法得到正确的结果。
我尝试了什么:
我点击按钮上传文本文件。
这是我试过的代码
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
推荐答案
(文件).ready(function(){
(document).ready(function(){
('#grid-div')。height(250);
('#grid-div').height(250);
.blockUI({message:'正在更新数据......',css:{border:'none ',填充:'15px',宽度:'15%',左:'40%',backgroundColor:'#000',' - webkit-border-radius':'10px',' - moz-border-radius' :'10px',不透明度:.5,颜色:'#ff'}}); 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(
这篇关于读取文本文件,然后将其拆分并存储在SQL Server VB .NET中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!