Edit one column in pipe delimited files 2

[jbradley]

I have 30 pipe delimited files that are 40 columns wide. Together they contain over 27,000 records. I need to devide the value in the 10th column by 100. I know that I can use InStr to find the position of the first occurance of a character in a line. What can I use to find the 9th or 10th occurance?

 

[SkipVought]

hi,

Try using the Split() function...

MyValue = Split(ThisRow, "|")(9) / 100

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Instead of InStr, consider this:

Dim ary() As String
Dim str As String
Dim i As Integer

str = "1|123|abcd|XYZ|666|987"

ary = Split(str, "|")

For i = LBound(ary) To UBound(ary)
    Debug.Print ary(i)
Next i

You will get:
[tt]
1
123
abcd
XYZ
666
987
[/tt]

Have fun.

---- Andy

 

[taupirho]

If you're interested you can do something like this quite easily from a DOS batch file

For example assume you have a bunch of text files, test1.txt, test2.txt, ... testN.txt which contain pipe separated fields. the following will read each line of each file, dividing the 10th field by 100 and outputting the data into new files test1.dat, test2.dat, ...testN.dat

setlocal EnableDelayedExpansion
for %%v in (test*.txt) do (
for /f "tokens=1-10,* delims=|" %%a in (%%v) do (
set /A x=%%j/100
echo %%a^|%%b^|%%c^|%%d^|%%e^|%%f^|%%g^|%%h^|%%i^|!x!^|%%k >> %%~nv.dat
)
)


In order to understand recursion, you must first understand recursion.

 

[jbradley]

Thanks for all the tips. Here is what I ended up doing . . .

[COLOR=#204A87][b]
        strOutput = ""
        strArray = Split(strInput, "|")
        For i = 0 To 9
            If i = 9 Then
                strArray(i) = strArray(i) / 100
            End If
        Next
        
        strOutput = strArray(0)
        For intFields = 1 To UBound(strArray)
            strOutput = strOutput & "|" & strArray(intFields)
        Next
        tsDestFile.WriteLine (strOutput)[/color][/b]

 

[Golom]

You are dropping the first element by using

[blue]For intFields = [red]1[/red] To UBound(strArray)[/blue]

strArray is zero-based. Is that what you intend?

If not, you can make it shorter

strArray = Split(strInput, "|")
strArray(9) = strArray(9) / 100
tsDestFile.WriteLine (Join (strArray, "|"))

 

[jbradley]

Thanks, Golom. I should have seen that. Split and Join, now that you said it I could kick myself for missing it. As for the "0 to UBound", yes, that is what I wanted.

Brad