Solved

Extract DataGridView1 to text file

Posted on 2011-09-23
21
233 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:Wilder1626
  • 10
  • 9
  • 2
21 Comments
 
LVL 17

Expert Comment

by:nepaluz
ID: 36590161
show us a sample of your expected text file.
I have not used a textmatrix, but does your grid contain 50 columns?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36590361
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
 
LVL 17

Expert Comment

by:nepaluz
ID: 36590428
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 36590596
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
 
LVL 17

Expert Comment

by:nepaluz
ID: 36590749
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 36590779
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
 
LVL 17

Expert Comment

by:nepaluz
ID: 36590841
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
 
LVL 17

Expert Comment

by:nepaluz
ID: 36590848
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 36590881
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 36590918
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36592338
>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
 
LVL 11

Author Comment

by:Wilder1626
ID: 36592404
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
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 36592413
> 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
 
LVL 11

Author Comment

by:Wilder1626
ID: 36592422
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
 
LVL 11

Author Comment

by:Wilder1626
ID: 36592458
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
 
LVL 17

Expert Comment

by:nepaluz
ID: 36592709
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
 
LVL 17

Expert Comment

by:nepaluz
ID: 36592710
even
File.AppendAllText(MyPath, String.Join(String.Empty, xList.ToArray) & vbCrLf)

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 36592839
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
 
LVL 17

Accepted Solution

by:
nepaluz earned 500 total points
ID: 36592850
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
 
LVL 17

Expert Comment

by:nepaluz
ID: 36592871
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
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 36592907
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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now