how to sort text in a text file

[Shaun29]

Ok if I have a selected text file which contents rows of information like a bill of material I want to declaire and sort to an excell work book

were all S,M T rows will go to the work book and be saved in sheet 1 to end of file.

and A,O,L will be saved to sheet 2 to end of file

I have a click event witch will let me select the text file in open dialog. then an extract button that will extract to a selected excel from open dialog.

my qeustion is how do i sort the text in the text file and extract it to the excell????

 

[Andrzejek]

You don't need to sort text file. Why don't you sort in Excel after you put information there?

And it would help us to see your code, what you have so far and what you need to do next.

Have fun.

---- Andy

 

[Shaun29]

Imports System.Net.Mime.MediaTypeNames

Public Class form1

Dim ofd As OpenFileDialog
Dim WithEvents txtFile As TextBox
Dim WithEvents btnOpen As Button
Dim WithEvents btnWriteFile As Button
Const LogFile As String = "Bill Of Material Log.txt,lstbur.xls,lstsaw.xls"

Private Sub Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
ofd = New OpenFileDialog
ofd.Filter = "Text Files|*.txt|All files (*.*)|*.*"
ofd.Multiselect = True
txtFile = New TextBox
txtFile.Left = 12
txtFile.Top = 24
txtFile.Width = 160
txtFile.ReadOnly = True

Me.Controls.Add(txtFile)
btnOpen = New Button
btnOpen.Text = "Select A file"
btnOpen.Width = 75
btnOpen.Height = Me.txtFile.Height
btnOpen.Left = Me.txtFile.Left + Me.txtFile.Width + 4
btnOpen.Top = Me.txtFile.Top
Me.Controls.Add(btnOpen)
AddHandler btnOpen.Click, AddressOf OpenFileButtonClick
btnWriteFile = New Button
btnWriteFile.Width = 150
btnWriteFile.Text = "Transfer B.O.M To Text "
btnWriteFile.Top = Me.txtFile.Top + Me.txtFile.Height + 6
btnWriteFile.Left = 12
Me.Controls.Add(btnWriteFile)
AddHandler btnWriteFile.Click, AddressOf WriteFileButtonClick
End Sub

Private Sub OpenFileButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs)
Me.txtFile.Text = ""
ofd.ShowDialog()
Me.txtFile.Text = ofd.FileName
End Sub

Private Sub WriteFileButtonClick(ByVal sender As System.Object, ByVal e As System.EventArgs)

ofd.ShowDialog()
If Not Me.txtFile.Text.Trim = "" Then
'Read the existing file
Dim FileText As String = ""
FileText = System.IO.File.ReadAllText(Me.txtFile.Text)
'Write to the log file
System.IO.File.AppendAllText(LogFile, FileText)

Timer1.Start()
Timer1.Enabled = True


MessageBox.Show("B.O.M Transfered")
Else
MessageBox.Show("Please select a file.")
End If
End Sub


Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
PB1.Increment(50)
If PB1.Value = PB1.Maximum Then
'SendKeys.Send("{ENTER}")
'Timer1.Enabled = False

End If
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click



Dim objExcel As New Excel.Application

objExcel.ActiveWorkbook.Sheets(2).Activate()

''Dim RowNum As String
''RowNum = 1
''Dim ColNum As Integer
''ColNum = 1

''For X = 1 To 2000
'' objExcel.Cells(RowNum, ColNum) = X
'' ColNum = ColNum + 1
'Next

''Dim fb As String
''Dim C As String
''Dim L As String
''Dim PL As String
''Dim RB As String
''Dim S As String
''Dim SB As String
''Dim SHT As String
''Dim SR As String
''Dim TS As String
''Dim Xs As String


'ofd.ShowDialog()

'If Not Me.txtFile.Text.Trim = "" Then

' 'Read the existing file
' Dim FileText As String = ""
' FileText = System.IO.File.ReadAllText(Me.txtFile.Text)
' 'Write to the log file
' System.IO.File.AppendAllText(LogFile, FileText)
'Else
' MessageBox.Show("Sent to WorkBook")

'End If

End Sub
End Class

 

[Shaun29]

See I am trying to sort the text file becuse there are rows that will stay in the text file EX:

Qty ITEM Material
1 S 10g
4 z 10g this would stay and not extract

 

[Andrzejek]

In your original post you are talking about "S,M T rows", and "A,O,L", now you have a meesage saying: "B.O.M Transfered" - what is this all about?

Is that your text file:

Qty ITEM Material
1    S    10g
4    z    10g

so in ITEM column you may have letter SMTBOMAOL and others? And that's how you know which record to write to what file?

Or am I completely lost....?

Have fun.

---- Andy

 

[Shaun29]

yes your correct the item colum will only have those letters and a few others that i do not want to extract but i want to extract the whole row defined by the item letter.

 

[Andrzejek]

So not going into details, when ITEM is S M T you write this record to one (xls) file, when ITEM is A O L you write to another (xls) file. I don't see where it is in the code, but your problem is with whatever is left over and how to write it into a text file, but have it sorted. Right?

Instead just writing it straight into text file, write all left over records into a String array, which then you can sort (many ways to sort array you can just Google), and after your string array is sorted, write it into text file. Done.

Have fun.

---- Andy

 

[Shaun29]

well i was actully trying to write it to an excel file but i am having trouble writing the code so that all the text goes into a row in the excell sheet

like

Qty Item Material
1 a 10g-------these to excel cells?


that is why im saying when Open the text file with this in it

Qty Item Material
1 a 10g-----select
1 s 10g
1 a 10g
1 a 10g------select
1 b 10g
1 a 10g
1 z 10g-------do not select

1.I want to click one button on my for to select this text file.
2. select another button that will sort thru this text and export the selected text rows into excel???

see what I am saying.

so i have this button event that i need to click to such a thing any ideas I would like to streamwrite using system IO
instead of DIM on everything.


here is what i got in the button field..........
===========================================================


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click



Dim objExcel As New Excel.Application
objExcel.Visible = False
objExcel.Worksheets.Add()
objExcel = Me.Text.Contains







' ''Dim RowNum As String
' ''RowNum = 1
' ''Dim ColNum As Integer
' ''ColNum = 1

' ''For X = 1 To 2000
' '' objExcel.Cells(RowNum, ColNum) = X
' '' ColNum = ColNum + 1
''Next

'Dim fb As String
'Dim C As String
'Dim L As String
'Dim PL As String
'Dim RB As String
'Dim S As String
'Dim SB As String
'Dim SHT As String
'Dim SR As String
'Dim TS As String
'Dim Xs As String


If txtFind <> "" Then
If InStr(txtFile, txtFind) <> 0 Then
txtFile.SelStart = InStr(txtFile, txtFind) - 1
txtFile.SelLength = Len(txtFind)

'Dim txtline As String
'Dim Words As String()
'Dim delimiters() As String = (fb, C, L, RB, S, SB, SHT, SR, TS, Xs)
'txtline = Str.read to end

















ofd.ShowDialog()
If Not Me.txtFile.Text.Trim = "" Then
'Read the existing file
Dim FileText As String = ""
FileText = System.IO.File.ReadAllText(Me.txtFile.Text)
'Write to the log file
System.IO.File.AppendAllText(LogFile, FileText, objExcel)

Timer1.Start()
Timer1.Enabled = True


MessageBox.Show("B.O.M Transfered")
Else
MessageBox.Show("Please select a file.")
End If

 

[Andrzejek]

This is just a quick code,

If your text file is something like:

1      a      10g
1      s      10g
1      a      10g
1      a      10g
1      b      10g
1      a      10g
1      z      10g

4 spaces between columns, you can do something like:

        Dim strTextLine As String
        Dim strArray() As String
        Dim i As Short = 0

        FileOpen(1, "C:\test.txt", OpenMode.Input)
        FileOpen(2, "C:\SMT.txt", OpenMode.Output)
        FileOpen(3, "C:\AOL.txt", OpenMode.Output)
        FileOpen(4, "C:\other.txt", OpenMode.Output)

        Do While Not EOF(1)
            strTextLine = LineInput(1)
            strArray = Split(strTextLine, "      ")
            Select Case UCase(strArray(1))
                Case "S", "M", "T"
                    PrintLine(2, strTextLine)
                Case "A", "O", "L"
                    PrintLine(3, strTextLine)
                Case Else
                    [blue]PrintLine(4, strTextLine)[/blue]
            End Select
        Loop

        FileClose(4)
        FileClose(3)
        FileClose(2)
        FileClose(1)

[blue]
sort array here for left over records, 
write to text file left over records[/blue]

That would be a start. Of course, instead of just text file you could do csv file which is read by Excel just fine. Also, the blue line of text would be changed to writing to an array for sorting.

Have fun.

---- Andy

 

[Shaun29]

how would you call out an excell sheet to write to?

 

[Andrzejek]

Since you take information from text file, it would be easy to create a csv file which Excel reads just fine.

If you need Excel, add a reference to Microsoft Excel XXX Object Library

Dim xl As New Excel.Application

With xl
   .Workbooks.Add()
   [green]'here you can put the code from Excel macro
   'that you record in Excel[/green]
   .Visible = True
End With

Have fun.

---- Andy

 

[Shaun29]

how would i use an xls sheet i created and is on my desktop

 

[Andrzejek]

How do you use it now? If you can do it 'by hand', you can do it in code. Record a macro in Excel and you will see the code to use to open existing excel file.

Have fun.

---- Andy

 

[Shaun29]

how do I open the excel sheet to write from open dialog?

 

[Shaun29]

ok so how would I use openfiledialog to open the first file?