Edit a .txt file and splitting a record with 12 columns of data into 12 records

[CJets]

Hello Everyone,

First off, thank you taking the time to read into my issue. My first post here, I apologize in advance if I do not follow all normal protocol. My experience is more in fixing VB/VBA for finance groups when something is broken or needs to be adjusted. This situation is a little more advanced since I'm starting something from the ground up. I have to use VB to grab a pipe delimited text file and change the organization of the records. The source file looks something like this:

Acct_Number|Acct_Description|Client_Code|Client_Name|Job_Type|Job_Type_Description|Acct_Type|Opening _Balance|Period_1|Period_2|Period_3|Period_4|Period_5|Period_6|Period_7|Period_8|Period_9|Period_10| Period_11|Period_12|Account_Total|
1100-100|NET BILLINGS |NA |Not available |NA|Not available |P| 00000000.00000|-00000013.12000|-00000010.55364| 00000000.00000| 00000000.00000| 00000000.00000| 00000000.00000| 00000000.00000| 00000000.00000| 00000000.00000| 00000000.00000| 00000000.00000| 00000000.00000|-00000023.67364|
1130-100|COST OF BILL |NA |Not available |NA|Not available |P| 00010000.00000| 00000013.12000| 00000010.55364| 00000000.00000| 00000000.00000| 00000000.00000| 00000000.00000| 00000600.00000| 00000000.00000| 00000000.00000| 00000000.00000| 00000000.00000| 00000000.00000| 00000023.67364|

I need to take the records and change their organization. For each line I am going to need to insert more lines for the columns from "Opening_Balance" to "Account_Total" and have one value for each line of metadata. For example the header and data will end up looking like:

Acct_Number|Acct_Description|Client_Code|Client_Name|Job_Type|Job_Type_Description|Acct_Type|PERIOD| VALUE|
1100-100|PRODUCTION NET BILLINGS |NA |Not available |NA|Not available |P|OPENING Balance| 00000000.00000|
1130-100|PRODUCTION COST OF BILL |NA |Not available |NA|Not available |P| Period_1| 00000013.12000|

So for each record I need to loop through and split the data out into 14 lines with the beginning pieces repeated and the period and correlating value assigned to the correct line.

Any help or pointers in the right direction would be immensely helpful and appreciated.

Thank You.

[dday9]

Take a look at this function I wrote a while back, it converts delimited text files into DataTables: http://www.vbforums.com/showthread.p...le-to-textfile

You'd call it like this:

Dim dt As DataTable = Text_To_DataTable("my_text_file.txt", "|"c, True)

Edit - Once you have the DataTable you're able to change the records in a control such as a DataGridView. Then you're able to use the other function in that link that converts the DataTable back to a delimited text file.

[CJets]

Thanks for responding! Would converting it into a data table keep it in text form? Unfortunately, this is not the data's last stop. This is really just a formatting excercise so that it can be loaded into an FDM process to eventually be loaded into a hyperion essbase database.

[dday9]

Take a look at my edit, you can convert it back.

[stanav]

I agree with dday9... For what you're trying to do, it's will be a lot easier if you load the original file to a datatable first then work the datatable instead of dealing directly with string manipulation. Use dday9's function to load your text file to datable, then pass the datatable to this function

 Private Function ManipulateData(ByVal dt1 As DataTable) As DataTable
        'Create a datatable to hold manipulated data to return
        Dim dt2 As New DataTable
        'Add the 1st 6 columns using column name and type from dt1
        For i As Integer = 0 To 5
            dt2.Columns.Add(dt1.Columns(i).ColumnName, dt1.Columns(i).DataType)
        Next
        'Add 2 extra columns
        dt2.Columns.Add("PERIOD", GetType(String))
        dt2.Columns.Add("VALUE", GetType(Decimal))

        'Now loop through each row in dt1 and build new row for dt2
        Dim row1, row2 As DataRow
        Dim idx As Integer = 6
        For i As Integer = 0 To dt1.Rows.Count - 1
            'For each row of dt1, create new row for dt2
            row2 = dt2.NewRow
            'Copy the values from 1st 6 columns of row1 to row2
            row1 = dt1.Rows(i)
            For j As Integer = 0 To idx - 1
                row2(j) = row1(j)
            Next
            'Now set the last 2 fields for row2
            If i < dt1.Columns.Count - idx Then
                row2("PERIOD") = dt1.Columns(idx + i).ColumnName
                row2("VALUE") = row1(idx + i)
            End If
            'And add that row2 to dt2
            dt2.Rows.Add(row2)
        Next
        Return dt2
    End Function

Then use dday9's code again to write the returned datable back to a text file.

Code is not tested but the point is to show you the algorithm.

[CJets]

Thanks for replying! I'm currently trying to debug dday9's and put in the required variables. I'll respond with how the finished project looks when its up and running. Thanks for all your help so far!!