Reading text file then spliting it up in and stored in SQL server VB .NET

Question

5.00/5 (1 vote)

See more:

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

Posted 5-Feb-18 15:48pm

Member 13661245

Updated 6-Feb-18 15:39pm

v6

Add a Solution

Comments

Laiju k 5-Feb-18 23:34pm

is file on some default format like .txt/.docx ?

or can we use any files .Please provide more information so people can help.

Member 13661245 6-Feb-18 0:21am

@laiju File is in default format .txt (Text Document)

PIEBALDconsult 6-Feb-18 0:06am

Have you tried BCP ?

Member 13661245 6-Feb-18 0:23am

No, I'm not familiar with BCP.

Maciej Los 6-Feb-18 2:04am

Can you post an exact form of your data? What's a delimiter (space or tab)?

Member 13661245 6-Feb-18 2:35am

Hi I Update my question and sample data which is the one that I'm generating. It is a text file tab delimited

Richard MacCutchan 6-Feb-18 4:04am

I explained how to do this in your post of the same question yesterday.

Member 13661245 6-Feb-18 4:29am

Yes, I followed your suggestion but still didn't get the right one. I can't load my text file.

Richard MacCutchan 6-Feb-18 4:34am

I don't understand. You had code in your post of yesterday which loaded the text file, and split each line into its separate fields. Why can you not do it now?

Member 13661245 6-Feb-18 4:39am

yes I have code but like I said it didn't load in my database.

Richard MacCutchan 6-Feb-18 4:54am

Well that has nothing to do with reading text files. Show us the code you have to add records to the database and explain what the problem is.

Member 13661245 6-Feb-18 5:01am

and that's the reason why my question now is how to stored in sql server database

Richard MacCutchan 6-Feb-18 5:20am

Well you also had some SQL code yesterday. As I already suggested, please edit your question, show the code that you have and explain exactly what is not working.

Member 13661245 6-Feb-18 21:43pm

Hi I update my question, I also put the code that I come up with. The scenario is I browse the .text file then click the upload button. The whole program just hang up. I've tried to put the debugger on the button click but after loading the file the program just hang up.

Richard Deeming 8-Feb-18 14:18pm

Dim sFileName As String = FileUpload1.FileName
If Not File.Exists(sFileName) Then
    Exit Sub
End If

The FileName property returns the path - or sometimes just the name - of the file on the client.

Your code is running on the server. Even if you happen to have a file with the same path name name, it almost certainly won't contain the same contents as the one on the client.

It might appear to work when you debug your code in Visual Studio. But that's only because, in that specific case, the client and server are the same computer. Once you deploy your code to a real server, your code is never going to work.

You can either save the uploaded file to a path on the server, using the file upload control's SaveAs method; or you can read the contents of the uploaded file directly, using the FileContent property.

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)

OriginalGriff · Answer 1 · 2018-02-05T21:42:00

Solution 1

If it's delimited data - and TAB counts - you can treat it as CSV, and there are plenty of readers for those that will convert your file directly into a DataTable.
Here is a good one: A Fast CSV Reader[^]
All you have to do is tell it to use TAB instead of comma, and it should just read your data.

Posted 5-Feb-18 21:42pm

OriginalGriff

Comments

OriginalGriff 6-Feb-18 4:42am

" but didn't work " is about the most useless error report you can give: it tells us nothing!
What did it do that you didn't expect, or not do that you did?
What did the debugger show you was going on?
What data did you feed it? How do you know it didn't work?
Were there any error messages?

Give us information to work with instead of just dumping a load of code on us and saying "it don't work".

Member 13661245 6-Feb-18 5:03am

my code work on reading .text file then splitting it, I put some message when loading to database but it takes time to load until the program hang up.

I load the my sample data here in my program.

OriginalGriff 6-Feb-18 5:11am

And what does the debugger show you?
Where is it hanging up?

Member 13661245 6-Feb-18 5:14am

It is hanging up on the portion of loading the data in sql server database.

OriginalGriff 6-Feb-18 5:21am

At the risk of repeating myself for the third time:
"And what does the debugger show you?"

Member 13661245 6-Feb-18 21:45pm

Hi I update my code above. The scenario is I browse the .text file then click the upload button. The whole program just hang up. I've tried to put the debugger on the button click but after loading the file the program just hang up.

OriginalGriff 7-Feb-18 4:46am

I wouldn't use reader.Peek at all: look at File.ReadAllLines which will return you an array of strings, each containing one line. A For Each on the array will give you each line in turn (and probably be more efficient as well).

Laiju k 6-Feb-18 23:29pm

after which line of code it is hanging up.

Laiju k 6-Feb-18 23:26pm

try to use less data instead of using bulk one or two rows are just fine.

Member 13661245 7-Feb-18 0:33am

I'm using two rows of data instead loading all. I'm already hanging up in reading the .txt file.

Laiju k 7-Feb-18 1:13am

can you be more specific like after which line of code it hang up.It will be more easier to get an idea what can be wrong.

Member 13661245 7-Feb-18 1:46am

When clicking the upload button if will identity if the file extension is .txt so basically the code is okay up to 'Reading Line in Text File, the program hang up here '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...

Laiju k 7-Feb-18 3:46am

can you try this

Do While sr.Peek() > -1
Console.WriteLine(sr.ReadLine())
Loop

insted of

Do Until reader.Peek = -1 'Until eof
fileEntries.Add(reader.ReadLine)
Loop