Dave Watts, CTO, Fig Leaf Software
That is exactly the problem Dave, if some rows do not have line feed, they're not being inserted into DB, it skips other steps and go right to finish. It looks
like the file is done processing.
So I need to do early rejection to this kind of file.
I used cfloop to catch anything else that do not follow the formatting rules.
Here is what I already done:
Right after the file got uploaded, I have these codes:
<cfloop index="x" file="#MyFile#" >
MyLine = Replace(x,chr(9),"|", "ALL")>
I'm using a few functions, such as, Left(), Mid(), Right(), etc to make sure certain rules are followed.
Up till here, it's done and tested, everything is working.
The only problem is when 1 or more rows don't end up with line feed and or carriage return.
I plan to add some codes to check the end of each row for chr(10) since it is looping for every row anyway.
If detected, process file normally otherwise stop processing.
So this can't be done in CF?
That part can be done in cf. In fact, we have a similar scheduled job in place. Our problem is not so much missing carraige returns, but extra ones.
We do something like this:
read the file into a variable. let's call it theFile
loop through the file and
get the position of what we expect to see at the start of each line. In our case, it's a 7 digit number so we use refind().
Use left() to create a variable called thisRow for everything up to that point.
Use right() to take thisRow out of theFile
process this row.
If we get rows with bad data, we set them aside in another variable and then mail that variable to ourselves.
My row has to end up with either Chr(10) OR chr(13) & chr(10) and the difficulty that I'm currently facing is how to detect if chr(10) or chr(13) and chr(10) does not exist following the last item on the list.
The last item on the list is always a blank space or an emtpry string.
Ilustration 1, is the structure of accepted row:
UU| value | value | value | value | value | value | value | value | value | blank (empty string) then follows by chr(10) or chr(13)chr(10)
Ilustration 2, is the structure of not-accepted row:
UU| value | value | value | value | value | value | value | value | value | blank
So my logic should be:
If chr(10) is detected, Proceed to normal steps
show error to user and abort
My difficulty is on what should I use to detect chr(10). I tried to use getToken() but it doesn't seem to work
In your situation, your last item on the list may always have value. On my case, the last item on the row is always blank value
If your only problem is a missing carraige return on the last line of the file, you might be overengineering something. If you do this:
<cffile action="read" variable="theFileContents">
<cfloop list="#theFileContents#" delimiters="#chr(10)#">
Then you will always get the what follows the last carraige return in the file.
First, make sure you're talking about the correct ASCII codes -
CHR(10) = New Line Feed
CHR(13) = Carriage Return
Dan's solution should work for you as long as you do the following:
1) Remove all chr(10)'s from your text using a replace statement
2) Loop over your lines one at a time using the list/delimeters method that Dan suggested (I suggest using chr(13) as your delimeter)
3) In each iteration of your loop, check the list length of you row using ListLen() with the delimeter set for "|". If the item count in your list does not match your expected value, you know that either the data columns are messed up or someone forgot a line break somewhere. Either way, you'll probably want to handle that error before you try and process it.