Parse Uploaded text file.
So I created a container. I set up the ability to upload a test file. Now what I want to do is parse the text file. There are some symbols in the text file that can help me define areas. Here is an example:
["DATA1"]
["SYSTEM"]="|Data|h[Store Place]|h|r"
Basically I want to grab the data between the |h[ and ]|h|r
Then I want to dump it in a field in my database.
Does anyone have any idea on how I could do this?
Thanks,
William
Ok so I switched it to a text field and just copy and pasted the data into it.
If all the lines of the text file are imported into 1 field in 1 record, you'll need a script to loop through the lines. To grab the portion of the line you want, you could store it in a variable, like:
Let ( [ $start = Position ( $current_line, "|h[", 1, 1 ) + 3; $end = Position ( $current_line, "]h", 1, 1 ) - 1; $length =max ( $end - $start, 0 )];
Middle ( $current_line, $start, $length )
)
Thanks
How do I determine the last line in my Exit Loop if [] Statement?
Most likely, you'll want to begin your script by finding out how many lines are in the text field, using ValueCount. Then, in the loop that chooses the lines, use a counter Variable to keep track of where you're at. Use the Exit Loop If [$counter = $num_lines] script step. Putting it all together, in simplest form:
Set Error Capture [On]
Set Variable [$num_lines, ValueCount(table::text_field)]
Loop
Set Variable [$counter, $counter+1]
Set Variable [$current_line, GetValue ( table::text_field, $counter )]
Set Variable [$extract,
Let ( [ $start = Position ( $current_line, "|h[", 1, 1 ) + 3; $end = Position ( $current_line, "]h", 1, 1 ) - 1; $length =max ( $end - $start, 0 )];
Middle ( $current_line, $start, $length )
)
]...
do your stuff with the extracted data
...
Exit Loop If [$counter = $num_lines]
End Loop
If every line of the text is the same type of data, you can have a calculated field to parse out the portion of the line you indicated. If that is not the case, a loop will work.
You do not need to know how many lines are in the file, or count. There is an option to Exit after Last. Just click that. Works great.
Most likely, you'll want to begin your script by finding out how many lines are in the text field, using ValueCount. Then, in the loop that chooses the lines, use a counter Variable to keep track of where you're at. Use the Exit Loop If [$counter = $num_lines] script step. Putting it all together, in simplest form:
Set Error Capture [On]
Set Variable [$num_lines, ValueCount(table::text_field)]
Loop
Set Variable [$counter, $counter+1]
Set Variable [$current_line, GetValue ( table::text_field, $counter )]
Set Variable [$extract,
Let ( [ $start = Position ( $current_line, "|h[", 1, 1 ) + 3; $end = Position ( $current_line, "]h", 1, 1 ) - 1; $length =max ( $end - $start, 0 )];
Middle ( $current_line, $start, $length )
)
]...
do your stuff with the extracted data
...
Exit Loop If [$counter = $num_lines]
End Loop