asked on

Text File to Gridview VB.net

Hello all,

I'm back in my new topic. I was thinking of maybe do a loop on a work.

In a previous post, i was extracting GridView to Text box but i would like to also impost in my grid the same text file that will go in my Grid, in the same column that it was at the origin.

Extract DataGridView1 to text file

So i need to make sure that all spewcific fields in the text file, goes in the good column in my Grid.

Just like i was extracting the grid to the text file.

Dim xOut As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 1}, {2, 1}, {3, 12}, {4, 12}, {5, 1}, {6, 35}, {7, 35}, {8, 20}, {9, 1}, {10, 35}, {11, 35}, {12, 20}, {13, 8}, {14, 12}, {15, 5}}
Dim MyPath As String = "C:\Documents and Settings\All Users\Desktop\tl.02" & Format(Now, "yymmddhhmmss")
Dim xBuilder As New StringBuilder
For Each row As DataGridViewRow In Me.DataGridView1.Rows
    Dim xList As String = Nothing
    For x = 0 To row.Cells.Count - 1
        Dim xValue = row.Cells(x).Value.ToString
        If xValue.Length < xOut.Item(x) Then xValue = xValue.PadRight(xOut.Item(x))
        xBuilder.Append(xValue)
    Next
    xBuilder.Append(vbCrLf)
Next
File.WriteAllText(MyPath, xBuilder.ToString)

Open in new window

How can i do this?

Thanks again for your help.

nepaluz

get me the total of the second values in {0, 1}up to column 35 and the total from 36 to 50.

theHollow

Hello
I believe you have the code almost done.

You should read the fine instead of writing to it.
Read the whole file into a String, or read line by line, then parse the each row.
The values in the rows are probably separated with some char, so split the row and put thm back to GridViewDataRows and add them to the DataGridView.

You can use a "System.IO.TextReader" or a "System.IO.StreamReader" to read the text file.
You'll probably find good examples and documentation on this on the MSDN website.

Wilder1626

ASKER

For example, when i was using VB6, i was using this code:

 .TextMatrix(lRow + 1, 0) = Trim(Left$(vSplit(lRow), 1)) 'H
              .TextMatrix(lRow + 1, 1) = Trim(Mid$(vSplit(lRow), 2, 1)) 'S
               .TextMatrix(lRow + 1, 2) = Trim(Mid$(vSplit(lRow), 3, 1)) '1
                .TextMatrix(lRow + 1, 3) = Trim(Mid$(vSplit(lRow), 15, 11)) 'Carrier_ID
               .TextMatrix(lRow + 1, 4) = Trim(Mid$(vSplit(lRow), 27, 1)) '8
              .TextMatrix(lRow + 1, 5) = Trim(Mid$(vSplit(lRow), 28, 7)) 'H4S 2B8
             .TextMatrix(lRow + 1, 6) = Trim(Mid$(vSplit(lRow), 63, 7)) 'H4S 2B8
            .TextMatrix(lRow + 1, 7) = Trim(Mid$(vSplit(lRow), 89, 20)) 'CANADA
             .TextMatrix(lRow + 1, 8) = Trim(Mid$(vSplit(lRow), 118, 1)) '
              .TextMatrix(lRow + 1, 9) = Trim(Mid$(vSplit(lRow), 119, 35)) '
               .TextMatrix(lRow + 1, 10) = Trim(Mid$(vSplit(lRow), 154, 35)) '
                .TextMatrix(lRow + 1, 11) = Trim(Mid$(vSplit(lRow), 189, 20)) '
                 .TextMatrix(lRow + 1, 12) = Trim(Mid$(vSplit(lRow), 209, 8)) '
                  .TextMatrix(lRow + 1, 13) = Trim(Mid$(vSplit(lRow), 217, 12)) '
                   .TextMatrix(lRow + 1, 14) = Trim(Mid$(vSplit(lRow), 229, 5)) '
                    .TextMatrix(lRow + 1, 15) = Trim(Mid$(vSplit(lRow), 234, 6)) '
                     .TextMatrix(lRow + 1, 16) = Trim(Mid$(vSplit(lRow), 240, 12)) '
                      .TextMatrix(lRow + 1, 17) = Trim(Mid$(vSplit(lRow), 252, 2)) '
                       .TextMatrix(lRow + 1, 18) = Trim(Mid$(vSplit(lRow), 254, 11)) '
                        .TextMatrix(lRow + 1, 19) = Trim(Mid$(vSplit(lRow), 265, 11)) '
                         .TextMatrix(lRow + 1, 20) = Trim(Mid$(vSplit(lRow), 276, 11)) '
                          .TextMatrix(lRow + 1, 21) = Trim(Mid$(vSplit(lRow), 287, 11)) '
                           .TextMatrix(lRow + 1, 22) = Trim(Mid$(vSplit(lRow), 298, 6)) '
                            .TextMatrix(lRow + 1, 23) = Trim(Mid$(vSplit(lRow), 304, 32)) '
                             .TextMatrix(lRow + 1, 24) = Trim(Mid$(vSplit(lRow), 336, 8)) '
                              .TextMatrix(lRow + 1, 25) = Trim(Mid$(vSplit(lRow), 344, 8)) '
                               .TextMatrix(lRow + 1, 26) = Trim(Mid$(vSplit(lRow), 352, 1)) '
                                .TextMatrix(lRow + 1, 27) = Trim(Mid$(vSplit(lRow), 353, 12)) '
                                 .TextMatrix(lRow + 1, 28) = Trim(Mid$(vSplit(lRow), 365, 3)) '
                                  .TextMatrix(lRow + 1, 29) = Trim(Mid$(vSplit(lRow), 368, 12)) '
                                   .TextMatrix(lRow + 1, 30) = Trim(Mid$(vSplit(lRow), 380, 2)) '
                                    .TextMatrix(lRow + 1, 31) = Trim(Mid$(vSplit(lRow), 382, 11)) '
                                     .TextMatrix(lRow + 1, 32) = Trim(Mid$(vSplit(lRow), 393, 11)) '
                                      .TextMatrix(lRow + 1, 33) = Trim(Mid$(vSplit(lRow), 404, 6)) '
                                       .TextMatrix(lRow + 1, 34) = Trim(Mid$(vSplit(lRow), 410, 6)) '
                                        .TextMatrix(lRow + 1, 35) = Trim(Mid$(vSplit(lRow), 416, 5)) '
                                         .TextMatrix(lRow + 1, 36) = Trim(Mid$(vSplit(lRow), 421, 5)) '
                                          .TextMatrix(lRow + 1, 37) = Trim(Mid$(vSplit(lRow), 426, 12)) '
                                           .TextMatrix(lRow + 1, 38) = Trim(Mid$(vSplit(lRow), 438, 12)) '
                                            .TextMatrix(lRow + 1, 39) = Trim(Mid$(vSplit(lRow), 450, 180)) '
            
            End If
            'etc.
         If Trim(Left$(vSplit(lRow), 1)) = "D" Then 'Odd rows'Even Rows
            .TextMatrix(lRow + 1, 0) = Trim(Left$(vSplit(lRow), 1))
            .TextMatrix(lRow + 1, 1) = Trim(Mid$(vSplit(lRow), 2, 1))
            .TextMatrix(lRow + 1, 2) = Trim(Mid$(vSplit(lRow), 3, 12))
            .TextMatrix(lRow + 1, 3) = Trim(Mid$(vSplit(lRow), 15, 11))
            .TextMatrix(lRow + 1, 4) = Trim(Mid$(vSplit(lRow), 26, 1))
            .TextMatrix(lRow + 1, 5) = Trim(Mid$(vSplit(lRow), 27, 11))
            .TextMatrix(lRow + 1, 6) = Trim(Mid$(vSplit(lRow), 38, 11))
            .TextMatrix(lRow + 1, 7) = Trim(Mid$(vSplit(lRow), 49, 12))
            .TextMatrix(lRow + 1, 8) = Trim(Mid$(vSplit(lRow), 61, 8))
            .TextMatrix(lRow + 1, 9) = Trim(Mid$(vSplit(lRow), 69, 11))
            .TextMatrix(lRow + 1, 10) = Trim(Mid$(vSplit(lRow), 80, 2))
         End If

Open in new window

Could that help?

Wilder1626

ASKER

I was using this at the beginning to import the text file in the grid

 With CommonDialog1
   On Error Resume Next
      .CancelError = True
      .Filter = "Microsoft Excel files (txt)"
      '.InitDir = App.Path
      .InitDir = "C:\Documents and Settings\all users\Desktop"
      .ShowOpen
      If .FileName = "" Then Exit Sub
   End With

   strText = GetFileText(CommonDialog1.FileName)
   Form7.file_name = (CommonDialog1.FileTitle)
   vSplit = Split(strText, vbCrLf)

Open in new window

nepaluz

try this

Dim xIn As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 1}, {2, 1}, {3, 12}, {4, 12}, {5, 1}, {6, 35}, {7, 35}, {8, 20}, {9, 1}, {10, 35}, {11, 35}, {12, 20}, {13, 8}, {14, 12}, {15, 5}}
Dim rCount As Integer = 0
Dim xList As New List(Of String)
Using sr = New Microsoft.VisualBasic.FileIO.TextFieldParser("FilePath")
    While Not sr.EndOfData
        Dim line = sr.ReadLine
        For Each x In xIn.Keys
            xList.Add(line.Substring(rCount, xIn.Item(x)))
            rCount += xIn.Item(x)
        Next
        Me.DataGridView1.Rows.Add(1, xList.ToArray)
        xList.Clear()
    End While
End Using

Open in new window

Wilder1626

ASKER

Is there a way to use the OpenFileDialog1 to import it in the grid?

Wilder1626

ASKER

I think that i can use the same approche that i did in VB6.

Ex:

.TextMatrix(lRow + 1, 0) = Trim(Left$(vSplit(lRow), 1)) 'H then...

and If Trim(Left$(vSplit(lRow), 1)) = "D" Then 'Odd rows'Even Rows Then...

But i would first import the text file. Then i would click on a button and it would re orginanized all the data like it's suppose to be.

Would that be something good?

Wilder1626

ASKER

That way if i only use the first statment .TextMatrix(lRow + 1, 0) = Trim(Left$(vSplit(lRow), 1)) 'H then... and forget about the second one, it would work also.

Does it make sence?

nepaluz

you use that to browse to the file. You then use the returned file to replace the file path. There is a good example ofusing this HERE

nepaluz

I do not understand what you are on about.
Have you tried the code I gave you?It will work on the 15 column grid data, just an addition that you may trim the data as you import it to the grid, aka change line 8 above to:

xList.Add(line.Substring(rCount, xIn.Item(x)).Trim)

Open in new window

Wilder1626

ASKER

That is good to browse. But to add it to the DataGridView1, can i add it to this code?

How can i do this?

nepaluz

for the 50 column grid, if you give the totals of the values I asked for above in my first response to the thread, then I can give you code suggestion for that too. However, to make any headway, you'll have to test the code I suggested (I think I understand your data layout a bit now) and give feedback as necessary.

nepaluz

this should read the data into the datagrid as you requested.

        Dim MyPath As String = "C:\Documents and Settings\All Users\Desktop\tl.02" & Format(Now, "yymmddhhmmss")
        Dim xIn As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 1}, {2, 1}, {3, 12}, {4, 12}, {5, 1}, {6, 35}, {7, 35}, {8, 20}, {9, 1}, {10, 35}, {11, 35}, {12, 20}, {13, 8}, {14, 12}, {15, 5}}
        Dim rCount As Integer = 0
        Dim xList As New List(Of String)
        Using sr = New Microsoft.VisualBasic.FileIO.TextFieldParser(MyPath)
            While Not sr.EndOfData
                Dim line = sr.ReadLine
                For Each x In xIn.Keys
                    xList.Add(line.Substring(rCount, xIn.Item(x)).Trim)
                    rCount += xIn.Item(x)
                Next
                Me.DataGridView1.Rows.Add(1, xList.ToArray)
                xList.Clear()
            End While
        End Using

Open in new window

Wilder1626

ASKER

I have an issue to update the file in the grid.

I like the code you gave me:

 Dim MyPath As String = "C:\Documents and Settings\All Users\Desktop\tl.02" & Format(Now, "yymmddhhmmss")
        Dim xIn As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 1}, {2, 1}, {3, 12}, {4, 12}, {5, 1}, {6, 35}, {7, 35}, {8, 20}, {9, 1}, {10, 35}, {11, 35}, {12, 20}, {13, 8}, {14, 12}, {15, 5}}
        Dim rCount As Integer = 0
        Dim xList As New List(Of String)
        Using sr = New Microsoft.VisualBasic.FileIO.TextFieldParser(MyPath)
            While Not sr.EndOfData
                Dim line = sr.ReadLine
                For Each x In xIn.Keys
                    xList.Add(line.Substring(rCount, xIn.Item(x)).Trim)
                    rCount += xIn.Item(x)
                Next
                Me.DataGridView1.Rows.Add(1, xList.ToArray)
                xList.Clear()
            End While
        End Using

Open in new window

But how do i make that code to show in my DataGridView1?

nepaluz

line 12 above puts the data into the datagridview

nepaluz

Actually, you need to add a line after line 13:

rCount = 0

Open in new window

Wilder1626

ASKER

I think i'm lost here.

This code does not use the OpenFileDialog1. How can it search the good file i want to select.

Lets start with this first. Just to make sure i follow more. :-)

Dim MyPath As String = "C:\Documents and Settings\All Users\Desktop\tl.02" & Format(Now, "yymmddhhmmss")
        Dim xIn As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 1}, {2, 1}, {3, 12}, {4, 12}, {5, 1}, {6, 35}, {7, 35}, {8, 20}, {9, 1}, {10, 35}, {11, 35}, {12, 20}, {13, 8}, {14, 12}, {15, 5}}
        Dim rCount As Integer = 0
        Dim xList As New List(Of String)
        Using sr = New Microsoft.VisualBasic.FileIO.TextFieldParser(MyPath)
            While Not sr.EndOfData
                Dim line = sr.ReadLine
                For Each x In xIn.Keys
                    xList.Add(line.Substring(rCount, xIn.Item(x)).Trim)
                    rCount += xIn.Item(x)
                Next
                Me.DataGridView1.Rows.Add(1, xList.ToArray)
                xList.Clear()
            End While
        End Using

Open in new window

Wilder1626

ASKER

i guess i need ti change this: Dim MyPath As String = "C:\Documents and Settings\All Users\Desktop\tl.02" & Format(Now, "yymmddhhmmss")

to use the OpenFileDialog1. Right?

Wilder1626

ASKER

Ok, what i did is this:

In Button2 i put this code:

 Dim myStream As Stream = Nothing
        Dim openFileDialog1 As New OpenFileDialog()

        openFileDialog1.InitialDirectory = "C:\Desktop"
        openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
        openFileDialog1.FilterIndex = 2
        openFileDialog1.RestoreDirectory = True

        If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Try
                myStream = openFileDialog1.OpenFile()
                file_name.Text = openFileDialog1.FileName
                If (myStream IsNot Nothing) Then
                    ' Insert code to read the stream here.
                End If
            Catch Ex As Exception
                MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
            Finally
                ' Check this again, since we need to make sure we didn't throw an exception on open.
                If (myStream IsNot Nothing) Then
                    myStream.Close()
                End If
            End Try
        End If

Open in new window

Then, in button8, i put this code:

 Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
        Dim MyPath As String = (file_name.Text)
        Dim xIn As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 1}, {2, 1}, {3, 12}, {4, 12}, {5, 1}, {6, 35}, {7, 35}, {8, 20}, {9, 1}, {10, 35}, {11, 35}, {12, 20}, {13, 8}, {14, 12}, {15, 5}}
        Dim rCount As Integer = 0
        Dim xList As New List(Of String)
        Using sr = New Microsoft.VisualBasic.FileIO.TextFieldParser(MyPath)
            While Not sr.EndOfData
                Dim line = sr.ReadLine
                For Each x In xIn.Keys
                    xList.Add(line.Substring(rCount, xIn.Item(x)).Trim)
                    rCount += xIn.Item(x)
                Next
                Me.DataGridView1.Rows.Add(1, xList.ToArray)
                xList.Clear()
            End While
        End Using
    End Sub

Open in new window

But if i do this, i have an error with : xList.Add(line.Substring(rCount, xIn.Item(x)).Trim)

error: Index and length must refer to a location within the string.
Parameter name: length

But if i do this,

nepaluz

you are getting yourself in a muddle by asking two questions in one. Lets totally forget about the OpenFileDialog1 part of the question for now.
To read your file into the grid, use:

Dim MyPath As String = "C:\Documents and Settings\All Users\Desktop\tl.02" & Format(Now, "yymmddhhmmss")
Dim xIn As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 1}, {2, 1}, {3, 12}, {4, 12}, {5, 1}, {6, 35}, {7, 35}, {8, 20}, {9, 1}, {10, 35}, {11, 35}, {12, 20}, {13, 8}, {14, 12}, {15, 5}}
Dim rCount As Integer = 0
Dim xList As New List(Of String)
Using sr = New Microsoft.VisualBasic.FileIO.TextFieldParser(MyPath)
While Not sr.EndOfData
    Dim line = sr.ReadLine
    For Each x In xIn.Keys
        xList.Add(line.Substring(rCount, xIn.Item(x)).Trim)
        rCount += xIn.Item(x)
    Next
    Me.DataGridView1.Rows.Add(1, xList.ToArray)
    xList.Clear()
    rCount = 0
End While
End Using

Open in new window

I added the line to reset rCount to zero but you missed that somehow. Try the code above now and we can go foward.

Wilder1626

ASKER

Good idea. You are right.

Ok, i have tried this code:"C:\Documents and Settings\All Users\Desktop\tl.02112024062011"

But i have put i real text file path:

       Dim MyPath As String = "C:\Documents and Settings\All Users\Desktop\tl.02112024062011"
        Dim xIn As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 1}, {2, 1}, {3, 12}, {4, 12}, {5, 1}, {6, 35}, {7, 35}, {8, 20}, {9, 1}, {10, 35}, {11, 35}, {12, 20}, {13, 8}, {14, 12}, {15, 5}}
        Dim rCount As Integer = 0
        Dim xList As New List(Of String)
        Using sr = New Microsoft.VisualBasic.FileIO.TextFieldParser(MyPath)
            While Not sr.EndOfData
                Dim line = sr.ReadLine
                For Each x In xIn.Keys
                    xList.Add(line.Substring(rCount, xIn.Item(x)).Trim)
                    rCount += xIn.Item(x)
                Next
                Me.DataGridView1.Rows.Add(1, xList.ToArray)
                xList.Clear()
                rCount = 0
            End While
        End Using

Open in new window

I have an error on this part of the code:

xList.Add(line.Substring(rCount, xIn.Item(x)).Trim)

Open in new window

error.jpg

nepaluz

the code above will work on the 15 column file that you used in your question.
1. If you are trying to read the real file with 50 columns, then you will get that error.
2. If you want to read the 50 column file, then use the dictionary that you used to write the 50 column file (from the previos question threads that we resolved earlier)

If the above two situations do not apply, can you get me the values of the following variables when the error occurs:
a) line
b) rCount
c) xIn.Item(x)
d) x

nepaluz

You can get the values by hovering over the variables when the error occurs pinning them and then taking a screen shot as you did with the error.

Wilder1626

ASKER

Well actully, i'm using the text file itself.

So there is no column at all in the text file.

I think i did not explain well then.

Let say that in the test file, on the first row, i have this result:

HS1VISION_LTL  3J3L                                CANADA                             6                   67Test7                              Test8

Open in new window

If i import in the grid the text file i have created, then it will update the grid by taking the first letter from the text file and put that in the column 1, i will have the valut: "H (Position 1 in text file)

In column 2 = "S" (Position 2 in text file)

Column 3 = "1" (Position 3 in text file)

Column 4 = VISION_LTL (Position 4 to 16 in text file).........

ASKER CERTIFIED SOLUTION

nepaluz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Sorry for all this time waist.

Ok, i will respond to this:

Q: To recap, if you are reading from a file with 15 columns (and not 50 columns) and still get the error would you get the variable values I asked for to enable me to proceed with code suggestion(s) to remedy the error?

A: The file i have is a text file (.Dat file). So there is no column in that file.

See file in attachement. (Just remove the ".xls" to the file. It will make the dat file i want to upload in the grid.)

Is that what you where understanding about the text file?
tl.02110124080114.xls

Wilder1626

ASKER

Thanks for all your time. What i will do it re open another topic the right way.

Thanks again

nepaluz

I had actually totally forgoten about this! Thanks for the points anyhow but not sure I will participate in your new thread ......
In this case, the file that you attached is clearly NOT in the format expected, thus you are getting the error.
For the record, the error occurs on the last column (column 15) and the total string length is 230 and rCount is 229. Column 15 is supposed to be 5 characters long thus the expected string length should be 234.

I looked further into the file layout and there in front of me was the un-disputable fact that the file was composed using a different dictionary. If you look at the file in conjunction with the dictionary, columns 1 - 4 should be 27 characters but there are an extra 24 characters (spaces) before the next item.

So here is my advice for what it is worth. From the other threads where we coded the function to write the file, use that code to write your file and then use the exact / same dictionary for the code suggested here and all should be OK.

I think I have gone the extra mile to help you resolve your issue and will rest my keyboard for now.