Extract DataGridView1 to text file

Hello all

I'm trying to extract my DataGridView1 to a text file like i was doing in VB6.

  Dim f%, r&, c%
        Dim iRow&, o$, p$, i&, MyFreeFile%
        Dim Grid As DataGridView ' or even MSHFlexGrid


        f = FreeFile()
  Open "C:\Documents and Settings\All Users\Desktop\tl.02" & Format(Now, "yymmddhhmmss") For Output As #f
        With Grid
            'Print #f, .FormatString
            For i = 1 To DataGridView1.Rows - 1 'not For irow = 1    'Start from top to bottom
                o$ = Space$(2048) 'I don't know the max length so we take more'n necessary
                p$ = Space$(2048) 'I don't know the max length so we take more'n necessary
                Mid$(o$, 1) = .TextMatrix(i, 0) 'H
                Mid$(o$, 2) = .TextMatrix(i, 1) 'A ou M
                Mid$(o$, 3) = .TextMatrix(i, 2) 'Number
                Mid$(o$, 15) = .TextMatrix(i, 3) ' Carrier
                Mid$(o$, 27) = .TextMatrix(i, 4) 'origine region key
                Mid$(o$, 28) = .TextMatrix(i, 5)  'origine region1
                Mid$(o$, 63) = .TextMatrix(i, 6)   'origine region2
                Mid$(o$, 98) = .TextMatrix(i, 7)   'origine country
                Mid$(o$, 118) = .TextMatrix(i, 8)   'destination region key
                Mid$(o$, 119) = .TextMatrix(i, 9)   'dest reg 1
                Mid$(o$, 154) = .TextMatrix(i, 10)  'dest reg 2
                Mid$(o$, 189) = .TextMatrix(i, 11)  'destination country
                Mid$(o$, 209) = .TextMatrix(i, 12)  'effective
                Mid$(o$, 217) = .TextMatrix(i, 13)  'single or team
                Mid$(o$, 229) = .TextMatrix(i, 14)  'cutoff time
                Mid$(o$, 234) = .TextMatrix(i, 15)  'tarif class ID
                Mid$(o$, 240) = .TextMatrix(i, 16)  'perf rating ID
                Mid$(o$, 252) = .TextMatrix(i, 17)  'free stop
                Mid$(o$, 254) = .TextMatrix(i, 18)  'first stop rate
                Mid$(o$, 265) = .TextMatrix(i, 19)  'second stop rate
                Mid$(o$, 276) = .TextMatrix(i, 20)  'third stop rate
                Mid$(o$, 287) = .TextMatrix(i, 21)  'addi stop rate
                Mid$(o$, 298) = .TextMatrix(i, 22)  'discount
                Mid$(o$, 304) = .TextMatrix(i, 23)  'tariff info
                Mid$(o$, 336) = .TextMatrix(i, 24)  'expiration
                Mid$(o$, 344) = .TextMatrix(i, 25)  'date invalide
                Mid$(o$, 352) = .TextMatrix(i, 26)  'spot rate
                Mid$(o$, 353) = .TextMatrix(i, 27)  'rate groupe
                Mid$(o$, 365) = .TextMatrix(i, 28)  'currency
                Mid$(o$, 368) = .TextMatrix(i, 29)  'radial rate ID
                Mid$(o$, 380) = .TextMatrix(i, 30)  'rr max stops
                Mid$(o$, 382) = .TextMatrix(i, 31)  'rr min charg
                Mid$(o$, 393) = .TextMatrix(i, 32)  'rr max chrg
                Mid$(o$, 404) = .TextMatrix(i, 33)  'max pck duration
                Mid$(o$, 410) = .TextMatrix(i, 34)  'max dly duration
                Mid$(o$, 416) = .TextMatrix(i, 35)  'max pickup distance
                Mid$(o$, 421) = .TextMatrix(i, 36)  'max dly distance
                Mid$(o$, 426) = .TextMatrix(i, 37)  'origin rail ramp id
                Mid$(o$, 438) = .TextMatrix(i, 38)  'destination rail ramp id
                Mid$(o$, 450) = .TextMatrix(i, 39)  'notes




                Mid$(p$, 1) = .TextMatrix(i, 40)  'rec type
                Mid$(p$, 2) = .TextMatrix(i, 41)  'trans code
                Mid$(p$, 3) = .TextMatrix(i, 42)  '0
                Mid$(p$, 15) = .TextMatrix(i, 43)  'variable rate
                Mid$(p$, 26) = .TextMatrix(i, 44)  'rate type
                Mid$(p$, 27) = .TextMatrix(i, 45)  'blank
                Mid$(p$, 38) = .TextMatrix(i, 46)  'fixed charge
                Mid$(p$, 49) = .TextMatrix(i, 47)  'capacity type id
                Mid$(p$, 61) = .TextMatrix(i, 48)  'unit qty
                Mid$(p$, 69) = .TextMatrix(i, 49)  'unloaded rate
                Mid$(p$, 80) = .TextMatrix(i, 50)  'max stops



                Print(f, Trim(o$))
                Print(f, Trim(p$))

            Next
        End With
        Close(f)
        FreeFile(f)
        MsgBox(".Dat file is on your desktop")

Open in new window


It looks like TextMatrix is not a member of 'System.Windows.Form.DataGridView.

How can i fix this?

Thanks again for your help.
LVL 11
Wilder1626Asked:
Who is Participating?
 
nepaluzConnect With a Mentor Commented:
Indeed, you can use a StringBuilder to put together your entire file before putting it on disk. The routine below utilizes the same dictionary I gave above (and since you have not said otherwise I will again assume it is OK), but I have replaced the way I build the string (removing the VbTab delimiter in the process) and put the results to disk. NOTE: The WriteAllText method will OVERWRITE the file if it exists!
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

0
 
nepaluzCommented:
show us a sample of your expected text file.
I have not used a textmatrix, but does your grid contain 50 columns?
0
 
Wilder1626Author Commented:
Hello nepaluz

Yes, my grid have 50 columns.

I will find a text file that i did with VB6 and i will add it to this topic.

It should not be to long.

The reaspn for this is because that text file will be uploaded in another transport program and each textmatrix will send the information at the right place in the other program.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
nepaluzCommented:
I have assumed that your file is delimited by a tab, you can change this. Also, you could build the file and then finally add all the text toit, but in this case I append each line to the file as I read from the grid.
Dim MyPath As String = "C:\Documents and Settings\All Users\Desktop\tl.02" & Format(Now, "yymmddhhmmss")
For Each row As DataGridViewRow In Me.DataGridView1.Rows
    Dim xList As New List(Of String)
    For x = 0 To row.Cells.Count - 1
        xList.Add(row.Cells(x).Value.ToString)
    Next
    File.AppendAllText(MyPath, String.Join(vbTab, xList.ToArray) & vbCrLf)
Next

Open in new window

0
 
Wilder1626Author Commented:
I think we are almost there now.

Now i have a text file. But Every column from the grid need's to be in a specific space in the text file.

Ex:

Column1 will start at position 1 in the test file.
Column 2 will start at space 2
Column 3 will start at space 3
Column 4 will start at space 5
Column 5 will start at space 27....


If i can have this, that would be perfect.

Actually, that was the thing when i was usin the .TextMatrix in my VB6 code.

His that posible?
0
 
nepaluzCommented:
do you mean something like this?
Column      No of Chars
0                    1
1                    1
2                     1
3                    12
4                    12
5                     1
6                     35
7                    35
8                    20
9                    1
10                  35
11                  35

etc
0
 
Wilder1626Author Commented:
For example:

If on the first row,

column 1 = H

Column 2 = S

Column 3 = 1

Column 4 = Word1

Column 5 = Word2


Then in the text file, at position 1 you will see "H". Followed with "S" at position 2...

Final result would be:
"HS1 Word1                Word2"




0
 
nepaluzCommented:
I have added the first 15 (?) columns for this. I worked out the size of your columns from your matrix above, and if it is wrong, do correct me. here goes ..
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")
For Each row As DataGridViewRow In Me.DataGridView1.Rows
    Dim xList As New List(Of String)
    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))
        xList.Add(xValue)
    Next
    File.AppendAllText(MyPath, String.Join(vbTab, xList.ToArray) & vbCrLf)
Next

Open in new window

0
 
nepaluzCommented:
to test the code, change the line (line 5 above)
For x = 0 To row.Cells.Count - 1

Open in new window

to
For x = 0 To 15

Open in new window

0
 
Wilder1626Author Commented:
OK, I will have to look at it cause the positions are not good.

If you want to look at it.

Here is the text file.

In this file, the H and S and 1 should be one after the other like: HS1.
tl.02111223091211.txt
0
 
Wilder1626Author Commented:
I have switch to this:
 Dim xOut As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 2}, {2, 3}, {3, 15}, {4, 27}, {5, 28}, {6, 63}, {7, 98}, {8, 118}, {9, 119}, {10, 154}, {11, 189}, {12, 209}, {13, 217}, {14, 229}, {15, 234}}

Open in new window


But still, it does not fix at the right place in the text file.

0
 
CodeCruiserCommented:
>But still, it does not fix at the right place in the text file.
Bare in mind that you would not get the text aligned when you open the text file in notepad etc. Even though the text is not aligned properly visually, you would get the column at the expected position when you parse the text file so it would work with other programs.
0
 
Wilder1626Author Commented:
Hello CodeCruser

I have tried to upload this flat file in my software and it was failing because of the fields (Position) not respected.

If you take a look at the screen shot 1 bellow, you will see at what position the second text is at.

Also, if you take a look at the 2nd screen shot, from my VB6 toll i did, to create the same text file, you will see the diffrence.
Position-9.jpg
VB6.jpg
0
 
CodeCruiserCommented:
> Dim xOut As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 2}, {2, 3}, {3, 15}, {4, 27}, {5, 28}, {6, 63}, {7, 98}, {8, 118}, {9, 119}, {10, 154}, {11, 189}, {12, 209}, {13, 217}, {14, 229}, {15, 234}}

>Column 2 will start at space 2

Is that not what you want?
0
 
Wilder1626Author Commented:
Yes, this is what i need, just like the VB6 screen shot in my previous post.

But is you look at the first screen shot from my previous post you will see that the "S" is at position 9 with this code:
Dim xOut As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 2}, {2, 3}, {3, 15}, {4, 27}, {5, 28}, {6, 63}, {7, 98}, {8, 118}, {9, 119}, {10, 154}, {11, 189}, {12, 209}, {13, 217}, {14, 229}, {15, 234}}
0
 
Wilder1626Author Commented:
This is so strange.

I did a test and change the second column to be at {1, 10}

 Dim xOut As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 10}, {2, 3}, {3, 15}, {4, 27}, {5, 28}, {6, 63}, {7, 98}, {8, 118}, {9, 119}, {10, 154}, {11, 189}, {12, 209}, {13, 217}, {14, 229}, {15, 234}}

Open in new window


The result is the same.

S is still at position 9. (See screen shot)

This is so strange.

wrong-position.jpg
0
 
nepaluzCommented:
It seems to me that the "extra" space introduced by the delimter is causing the issue. I assumed as stated above that your file is delimited (by a tab) clearly, this is not the case and thefile is not delited by character by rather byfield length, thus, this is the line you need to change, from
File.AppendAllText(MyPath, String.Join(vbTab, xList.ToArray) & vbCrLf)

Open in new window

to
File.AppendAllText(MyPath, String.Join(Etring.Empty, xList.ToArray) & vbCrLf)

Open in new window

0
 
nepaluzCommented:
even
File.AppendAllText(MyPath, String.Join(String.Empty, xList.ToArray) & vbCrLf)

Open in new window

0
 
Wilder1626Author Commented:
Oh, thios is perfect
File.AppendAllText(MyPath, String.Join(String.Empty, xList.ToArray) & vbCrLf)

Open in new window


I just have to modify:
Dim xOut As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 2}, {2, 3}, {3, 15}, {4, 27}, {5, 28}, {6, 63}, {7, 98}, {8, 118}, {9, 119}, {10, 154}, {11, 189}, {12, 209}, {13, 217}, {14, 229}, {15, 234}}

Open in new window


Cause {1, 2} does not put the second column text at position 2.

Like this, it work perfectly:
 Dim xOut As New Dictionary(Of Integer, Integer) From {{0, 1}, {1, 1}, {2, 12}, {3, 12}, {4, 1}, {5, 35}, {6, 98} ......  }

Open in new window


i don't understand why but its working.

I will do some text and i will let you know.

The second number between the braquets are actually empty spaces between column text and not fields position for every column.

Does it make sence?

0
 
nepaluzCommented:
In the dictionary I gave, the second number in the brackets is the TOTAL length of the field INCLUDING the values.
For example {3, 12} refers to the 4th column. If the value in that column is "ABC" then the code will produce:"ABC         ", i.e with 9 spaces.

Hope that iswhat youwererefering to in your last post.
0
 
Wilder1626Author Commented:
Thanks, this is perfect. Just want i needed.

I will open another topic for the same type of request but with one extra setting.

For example:

From column 1 to 35 on one row and from column 36 to 50 just under that row.

And this, for every grid row.

Is that posible to do?

If yes, let me open another topic so you would get points for this also.

Thanks again for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.