Need Help Splitting a string

[Brian.Osborne]

I have a string of data that I need to split into individual components. I'm trying to use Split but it doesn't seem to be working.
Here is a sample string of data from my datatable "TranLine". contained here in '
' 0817102 30 A 1,464.49 1.15778-'
Here is my code I'm trying to do this with.

Code:

myTranLine = DataTable("TranLine", dtGlobalSheet)
mySplit = Split(myTranLine, " ")
myNewTranLine = mySplit(0) & mySplit(1) & mySplit(2) & mySplit(3) & mySplit(4)
mySplit(0) = TranDate
mySplit(1) = TranCode1
mySplit(2) = TranCode2
mySplit(3) = TranAmnt
mySplit(4) = IntAdj
DataTable("TRANTYPE", dtGlobalSheet) = TranCode1 + TranCode2
DataTable("DATE", dtGlobalSheet) = TranDate
DataTable("TRAN1", dtGlobalSheet) = TranCode1
DataTable("TRAN2", dtGlobalSheet) = TranCode2
DataTable("AMT", dtGlobalSheet) = TranAmnt

Another note: sometimes the string is all spaces with no data, sometimes the last peice of data is missing.

[tdevick]

I don't understand your code. You split TranLine, separated by spaces, into array "mySplit". You build "myNewTranLine" by concatenating the "mySplit" array values. Ok so far. Then you set mySplit(0) to TranDate? Where did TranDate come from and why are you setting mySplit(0) to TranDate? same for TranCode1, TranCode2, TranAmnt, adn IntAdj. You're setting the array elements to the value of variables that aren't defined. Then you set you data table elements to those non-existant values?

I'm guessing that this is what you meant to do instead:

TranDate = mySplit(0)
TranCode1 = mySplit(1)
TranCode2 = mySplit(2)
TranAmnt = mySplit(3)
IntAdj = mySplit(4)

The next problem is that the example string you provided here has a leading space. That means that mySplit(0) will contain a space character, not your data substring. So your array will contain 5 elements in this example, not 4. That also means that the rest of the values will be wrong as well.

If you want to get rid of the leading space (if it is only there sometimes), do this:

Code:

myTranLine = Ltrim(DataTable("TranLine", dtGlobalSheet))

This gets rid of any leading spaces, if any are found in the string. This also takes care of the problem when the data string is all spaces. You can check the length of the data string and if it is 0, skip this one and move on to the next data value you want to process.

Also, the last value in the array, mySplit(5), will be "1.115778-". Do you really want "-" as part of the value?

You'll have to get rid of the trailing "-" character if you plan to use this as a numeric value. You can do this:

Code:

x=left(mysplit(5), len(mysplit(5))-1)

This gets rid of the "-" character at the end of the string.