asked on

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.

nepaluz

show us a sample of your expected text file.
I have not used a textmatrix, but does your grid contain 50 columns?

Wilder1626

ASKER

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.

nepaluz

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

Wilder1626

ASKER

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?

nepaluz

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

Wilder1626

ASKER

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"

nepaluz

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

nepaluz

to test the code, change the line (line 5 above)

For x = 0 To row.Cells.Count - 1

Open in new window

For x = 0 To 15

Open in new window

Wilder1626

ASKER

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

Wilder1626

ASKER

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.

Nasir Razzaq

>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.

Wilder1626

ASKER

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

Nasir Razzaq

> 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?

Wilder1626

ASKER

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}}

Wilder1626

ASKER

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

nepaluz

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

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

Open in new window

nepaluz

even

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

Open in new window

Wilder1626

ASKER

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?

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

nepaluz

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.

Wilder1626

ASKER

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.