Need to open file, reformat data and save as different file 1

[goitc7]

Hi
I need to open a space delimited timeclock file with payroll data and then reformat the data for import into into a payroll program as a txt file. Any help would be appreciated

 

[huggyboy]

try using streamreader & streamwriter - done an example for you below - at moment appends the data to the output file

Imports System.IO

        Dim strworkin As String
        Dim strworkout As String
        Dim arrwork() As String
        Dim sr As New StreamReader("C:\test.bat")
        Dim sw As New StreamWriter("C:\testout.txt", FileMode.Create)
        Do Until sr.EndOfStream
            strworkin = sr.ReadLine()
            arrwork = strworkin.Split(" ")
            strworkout = ""
            For intkount = 0 To UBound(arrwork)
                ' do your reformatting in here - in this case just put in commas between the fields
                strworkout += arrwork(intkount) + ","
            Next
            sw.WriteLine(strworkout)
        Loop
        sr.Close()
        sw.Close()

 

[goitc7]

Thank you for your help. I am trying the code and I get several errors, "Statement is not valid in a namespace". What do you think is the matter?
Thanks again

 

[huggyboy]

probably got the imports statement in the wrong place - should be above the forms class declaration ie
form with button on it should look like

Imports System.IO

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strworkin As String
        Dim strworkout As String
        Dim arrwork() As String
        Dim sr As New StreamReader("C:\test.bat")
        Dim sw As New StreamWriter("C:\testout.txt", FileMode.Create)
        Do Until sr.EndOfStream
            strworkin = sr.ReadLine()
            arrwork = strworkin.Split(" ")
            strworkout = ""
            For intkount = 0 To UBound(arrwork)
                ' do your reformatting in here - in this case just put in commas between the fields
                strworkout += arrwork(intkount) + ","
            Next
            sw.WriteLine(strworkout)
        Loop
        sr.Close()
        sw.Close()
    End Sub
End Class

 

[goitc7]

OK,
Thank you for your help.
I have it reading a text file now.
Today I will get a sample of my import file so I can work out the concatenation I need.
I will have to read through it a line at a time and extract three or four pieces of information, remove some 0s and replace them with spaces.
I will post if I run into problems.
Thanks again,
Sam

 

[goitc7]

Here is some code from the file I am importing

00000,0000000401,0000000002,0000000002,11,00800
00000,0000000401,0000000002,0000000002,10,01600
00000,0000000401,0000000002,0000000002,10,04000
00000,0000000402,0000000002,0000000002,11,00875

It will end up like this ...

401070000024.50 H1OT
401070000080.00 H1RG
402070000016.00 H1OT
402070000080.00 H1RG

NOTE: export sample wrapped because it is a fixed length (78) text file.

Can I use do until end of line to work with one line at a time?
Thanks

 

[huggyboy]

In the code example i have posted i have used the split method on the input record to populate a string array - so you can use the members in the array to build the output record ie arrwork(1) will contain 0000000401 for the first input record so a bit of string manipulation trimstart will get it into the format for the output record.
Cant see the mapping between the input & output - where do the 24.50 and H1OT fields come from?

 

[goitc7]

Hi
With your help I think I have it. The 24.50 is taken from raw time data ie 2450 and the H1OT is converted from the 10 and 11 column with this ...

' let's solve NumHours
If strworkin.Substring(45, 2) = "00" Then
NumHours = "00000" + strworkin.Substring(42, 3)
ElseIf strworkin.Substring(45, 2) = "25" Then
NumHours = "00" + strworkin.Substring(42, 3) + ".25"
ElseIf strworkin.Substring(45, 2) = "50" Then
NumHours = "000" + strworkin.Substring(42, 3) + ".5"
ElseIf strworkin.Substring(45, 2) = "75" Then
NumHours = "00" + strworkin.Substring(42, 3) + ".75"
End If

' let's convert DtlCode
If strworkin.Substring(39, 2) = "10" Then
DtlCode = "H1RG"
ElseIf strworkin.Substring(39, 2) = "11" Then
DtlCode = "H1OT"
End If

Thanks for all of your help.
Sam

 

[Andrzejek]

I would suggest:

[green]'let's solve NumHours[/green]
Select Case strworkin.Substring(45, 2)
   Case "00"
      NumHours = "00000" + strworkin.Substring(42, 3)
   Case "25" 
      NumHours = "00" + strworkin.Substring(42, 3) + ".25"
   Case "50"
      NumHours = "000" + strworkin.Substring(42, 3) + ".5"
   Case "75"
      NumHours = "00" + strworkin.Substring(42, 3) + ".75"
   Case Else
      MessageBox.Show "Error."
End Select

.... or even:

[green]'let's solve NumHours[/green]
[blue]Dim strMystr As String = strworkin.Substring(42, 3)[/blue]
Select Case strworkin.Substring(45, 2)
   Case "00"
      NumHours = "00000" + [blue]strMystr[/blue]
   Case "25" 
      NumHours = "00" + [blue]strMystr[/blue] + ".25"
   Case "50"
      NumHours = "000" + [blue]strMystr[/blue] + ".5"
   Case "75"
      NumHours = "00" + [blue]strMystr[/blue] + ".75"
   Case Else
      MessageBox.Show "Error."
End Select

It is the same logic, it's just a lot easier to read.

Have fun.

---- Andy

 

[goitc7]

I like it, thanks.
I will do some tweaking and see what happens.
Thanks again