Verify Integrity of CSV File

FOPEN( ) Function
http://msdn2.microsoft.com/en-us/library/0h6zba4x(VS.80).aspx

FEOF( ) Function
http://msdn2.microsoft.com/en-us/library/ws7ect2c(VS.80).aspx

FGETS( ) Function
http://msdn2.microsoft.com/en-us/library/f4ytfbz9(VS.80).aspx

FCLOSE( ) Function
http://msdn2.microsoft.com/en-us/library/8daa42kc(VS.80).aspx

You can also create the cursor and use the APPEND FROM data.csv TYPE CSV command. And then search the fields in the cursor for missing fields.

I have examples of data around 32,000 to 65,000 lines and it parses it in less than a minute.

A really easy way is to create a cursor with one memo field, append one blank record to it then issue Append Memo <fieldname> From <filename> . You now have the entire file in a memo field that you can step thorugh a line at a time and examine. For example you can use ALINES() to load it into an array or just step through it with MLINE. You can count commas in each line with OCCURS() to locate records with incorrect field counts. You can throw "bad" lines into the memo field of another cursor to review later and append all the "good" lines together and re-export them as csv and then feed the "scrubbed" version to your regular import process. We do this a lot.

Can you give me an example of how the data is represented in the file, the delimiters ( what seperates one column from another ) ect.  This verification routine will be about 10 lines or less.

Each field is enclosed in quotes, i.e. "field" and separated by a comma, i.e. "field1","field2"
Some fields do contain quotes within the field data.

I am not sure of the difference between a line feed or a return, or how to tell which is used, but one is used to end each line.

Additionally, there are three lines of junk data (column names, etc) at the top of the file that I would like to ignore before processing, if possible.

Sample File:

junk (file date, etc)
more junk (file origination, etc.)
columnname1, columnname2
"record1-field1","record1-field2"
"record2-field1","record2-field2"

Usually at the end of each line you find CRLF which is Carriage Return and Line Feed

CHR(13)+CHR(10)

If you read the file with APPEND FROM or FGETS you will not need to worry about CRFL.

OR

#DEFINE CRLF CHR(13)+CHR(10)

This example is a complete program that will read and store your data from your text file to an array in memory.  The array litterly becomes dymanic in that it will expand as your file grows larger - I hate to waste space.  Additionally, it will read and store up to ten columns from the file as well.  This program could easily be scaled down but I wanted to give you plenty of exmples and comments to help you down your road.

LOCAL   xmFileHandle, xmFileResult, xmStringIn, xmAtPos, xmPointer, xmArray, xmMax, xmColumn, xmLoop, ymLoop, xmNewString
 
xmFileHandle = FOPEN( 'C:\test.txt' )   && Open the input file
 
xmMax = 10   && Maximum array size allowed - this is dymanic in this example
xmPointer = 0	&& Variable to hold the current array pointer
DECLARE xmArray [ xmMax, 10 ]  && Declare an array to place the column values read from the file
 
FOR xmLoop = 1 TO xmMax   && Always a good idea to initilize your variables and arrays
    FOR ymLoop = 1 TO 10
        xmArray [ xmLoop, ymLoop ] = ''
    NEXT 
NEXT 
 
DO WHILE FEOF ( xmFileHandle ) = .F.	  && Start at the top of the file all the way to the end
   xmStringIn = ALLTRIM( FGETS( xmFileHandle ) )  && Read a line from the test file
   
    IF LEFT( xmStringIn, 1 ) = '"' .AND. RIGHT( xmStringIn, 1 ) = '"'	&& Only Conerned about the lines with DATA
      xmStringIn = xmStringIn + ','										&& Add a comma to the end of the string so all data is formatted "DATA",
      xmPointer  = xmPointer + 1										&& Move to the next Array Position to place the data values
 
      IF xmPointer > xmMax						&& This will resize the array if it needs to be large
         xmMax = xmMax + 10						&& lets resize by an additional 10 elements
         DECLARE xmArray [ xmMax, 10 ]			&& Resize the Array to Make it Larger - it leaves the origional elements alone!
 
         FOR xmLoop = xmPointer TO xmMax		&& Always a good idea to initilize your variables and arrays
             FOR ymLoop = 1 TO 10
                 xmArray [ xmLoop, ymLoop ] = ''
             NEXT 
         NEXT 
      ENDIF 
 
      xmColumn   = 0													&& Reset the column counter for placing data read into the array
 
      DO WHILE AT( '",' , xmStringIn ) > 0				&& The Search Delimiter is ",
         xmAtPos  = AT( '",' , xmStringIn )				&& What is the location of the delimiter
         xmColumn = xmColumn + 1						&& Which Array column do you want to place the value in?
 
         IF xmColumn <= 10								&& Only for array columns <= 10
            xmArray [ xmPointer, xmColumn ] = LEFT( xmStringIn, xmAtPos )		&& Store the data to the array
            xmNewString = SUBSTR( xmStringIn, xmAtPos + 2, LEN( xmStringIn ) )	&& Remove the Stored Data from the remaining data
            xmStringIn = xmNewString											&& Resultant data is no thw search string again
            
			&& This line will remove the heading and trailing " from the data
            && xmArray [ xmPointer, xmColumn ] = SUBSTR( xmArray [ xmPointer, xmColumn ], 2, LEN( xmArray [ xmPointer, xmColumn ] ) - 2 )
         ENDIF 
 
      ENDDO 
 
   ENDIF 
ENDDO 
 
IF xmPointer > 0
   DECLARE xmArray [ xmPointer, 10 ]	&& Last - Resize the Array down to the exact size of the data read so there is no wasted space.
ENDIF 
 
xmFileResult = FCLOSE( xmFileHandle )	&& Close the input file
 
Clear									&& Clear the output device - you would drop this on a form
FOR xmLoop = 1 TO xmPointer				&& Want to see the results?
    ? 'Row: ' + ALLTRIM( STR( xmLoop ) )
    FOR ymLoop = 1 TO 10
        ? '     Column: ' + STR( ymLoop,2 ) + '    Value:' + xmArray [ xmLoop, ymLoop ]
    NEXT 
    ?
NEXT 

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:

CharlesWard, I really appreciate your efforts.
I did create a prg, however when I ran it, it looked as though somewhere something got stuck, perhaps in a loop somewhere. Let me explain.

The FP prog I use was originally started by someone who is no longer with us (not dead, just no longer woks here ...). In this program, I do not get the typical foxpro window, so output that prints to the screen never appears.
I know somehow the FP window is supressed in the main prg, and I have tried to re-enable it so I can run the prg in the standard FP window, but with no luck.

As such I am also unable to use any of the debugging tools, which I'm sure would have saved me much time over the years. But that is a different matter...

In any event, I placed a button on a form to call the prog that you provided. However, once the prog started running, it just never ended (I let it run for over two hours while I was gone). With no visible output I can't really tell what or where something is happening or not happening.

I am guessing perhaps the problem has to do with the large file size - the csv file contains 26 fields and about 11,000 records. Can my PC hold all this data in an array with only 1GB RAM (the csv filesize itself is just 6.5MB)?

That file size is not large at all, nor the record count or the field count, especially considering the fact that you are using VFP9.  I do these types of quicky programs myself using the low lefile file functions, whicj is why I recommended it to begin with.

One area in the low level file functions I'm particularly sensitive to is in the use of the FGETS() function, which I depend on heavily because it automatically grabs the next chunk of data until it finds a line feed and carriage return combination.  The caveat though is to keep in mind that by default it grabs 254 bytes.  If you hapen to have some long text strings within those 26 fields that force the byte count over 254 bytes, you might need to provide a higher number to the FGETS() function so that it doesn't limit itself to only 254 bytes before it stops looking for that line feed/carriage reeturn combination.  VFP9 allows us to specify up to 8,192 bytes at a time.  I don't know if this is part of the problem with the above code.  But, as I said, I am sensitive to it as I have been caught by the lower 254 byte default in the past.  Just add a number higher than the assumed default and give the code another try.  At least you can then rule that part out as the only problem if it doesn't work or behaves differently.

FGETS(xmFileHandle ,1024)

FGETS( ) Function
http://msdn2.microsoft.com/en-us/library/f4ytfbz9(VS.80).aspx

FGETS( ) returns a maximum of 8192 bytes. If you omit nBytes, FGETS( )returns 254 bytes by default.

Still nothing. I tried 1024, then 2048, then all the way to 8192.
So I added a wait window counter and it appears to be hanging on the very first line.

Any ideas?

Honestly, I tried to look, but your code is decades advanced over the little bit I can do, so I haven't a clue...

It is looping forever somewhere in here:


    IF xmColumn <= 10                                                && Only for array columns <= 10
            xmArray [ xmPointer, xmColumn ] = LEFT( xmStringIn, xmAtPos )            && Store the data to the array
            xmNewString = SUBSTR( xmStringIn, xmAtPos + 2, LEN( xmStringIn ) )      && Remove the Stored Data from the remaining data
            xmStringIn = xmNewString                                                                  && Resultant data is no thw search string again
       
                  && This line will remove the heading and trailing " from the data
            && xmArray [ xmPointer, xmColumn ] = SUBSTR( xmArray [ xmPointer, xmColumn ], 2, LEN( xmArray [ xmPointer, xmColumn ] ) - 2 )
         ENDIF

First off, that's not my code.  I would try something different.  But, it always depends on the problem I'm trying to solve and the file I have at hand.

The counter reached 50,000 in just a matter of seconds. There are only 11,000 records so I'm assuming this must be where the problem lies (unless where I placed the counter would result in it counting something other than actual records).  


    DO WHILE AT( '",' , xmStringIn ) > 0                        && The Search Delimiter is ",
         xmAtPos  = AT( '",' , xmStringIn )                        && What is the location of the delimiter
         xmColumn = xmColumn + 1                                    && Which Array column do you want to place the value in?
WAIT WINDOW NOWAIT "Processing, please wait..." + ALLTRIM(STR(lnCount))  
         IF xmColumn <= 10                                                && Only for array columns <= 10
            xmArray [ xmPointer, xmColumn ] = LEFT( xmStringIn, xmAtPos )            && Store the data to the array
            xmNewString = SUBSTR( xmStringIn, xmAtPos + 2, LEN( xmStringIn ) )      && Remove the Stored Data from the remaining data
            xmStringIn = xmNewString                                                                  && Resultant data is no thw search string again
       
                  && This line will remove the heading and trailing " from the data
            && xmArray [ xmPointer, xmColumn ] = SUBSTR( xmArray [ xmPointer, xmColumn ], 2, LEN( xmArray [ xmPointer, xmColumn ] ) - 2 )
         ENDIF
  lnCount = lnCount + 1
      ENDDO

The loop cycles thru Search Delimiters. Therefore your counter counts Search Delimiters, not records.
If there is a record with more than 10 Search Delimiters xmColumn will eventually reach 11. After that xmStringIn will not be changed, and the loop will not terminate.

Please use the code snippet. It makes code reading easier.

Sorry, I've just recently noticed the snippet option but didn't know what it was for. I'll be sure to make use of it from now on.

I know I'm missing something here, as I'm sure this should be fairly simple. I've changed all the 10s throughout the file to 26 to match the number of fields in the file.

Now it begins to process the file, however it hangs on record #1754 every time. And it doesn't matter what the contents of line 1754 are, I have deleted it over and over, deleted it along with 5 preceding and succeeding lines, still it hangs on line 1754.

LOCAL   xmFileHandle, xmFileResult, xmStringIn, xmAtPos, xmPointer, xmArray, xmMax, xmColumn, xmLoop, ymLoop, xmNewString, lnCount
lnCount = 0
xmFileHandle = FOPEN( 'C:\sourcedata\feeds\list.csv' )   && Open the input file
 
xmMax = 26   && Maximum array size allowed - this is dymanic in this example
xmPointer = 0	&& Variable to hold the current array pointer
DECLARE xmArray [ xmMax, 26 ]  && Declare an array to place the column values read from the file
 
FOR xmLoop = 1 TO xmMax   && Always a good idea to initilize your variables and arrays
    FOR ymLoop = 1 TO 26
        xmArray [ xmLoop, ymLoop ] = ''
    NEXT 
NEXT 
 
DO WHILE FEOF ( xmFileHandle ) = .F.	  && Start at the top of the file all the way to the end
WAIT WINDOW NOWAIT "Processing, please wait..." + ALLTRIM(STR(lnCount))  
   xmStringIn = ALLTRIM( FGETS( xmFileHandle, 1024 ) )  && Read a line from the test file
 	
    IF LEFT( xmStringIn, 1 ) = '"' .AND. RIGHT( xmStringIn, 1 ) = '"'	&& Only Concerned about the lines with DATA
      xmStringIn = xmStringIn + ','										&& Add a comma to the end of the string so all data is formatted "DATA",
      xmPointer  = xmPointer + 1										&& Move to the next Array Position to place the data values
 
      IF xmPointer > xmMax						&& This will resize the array if it needs to be large
         xmMax = xmMax + 26						&& lets resize by an additional 26 elements
         DECLARE xmArray [ xmMax, 26 ]			&& Resize the Array to Make it Larger - it leaves the origional elements alone!
 
         FOR xmLoop = xmPointer TO xmMax		&& Always a good idea to initilize your variables and arrays
             FOR ymLoop = 1 TO 26
                 xmArray [ xmLoop, ymLoop ] = ''
             NEXT 
         NEXT 
      ENDIF 
 
      xmColumn   = 0									&& Reset the column counter for placing data read into the array
 
      DO WHILE AT( '",' , xmStringIn ) > 0				&& The Search Delimiter is ",
         xmAtPos  = AT( '",' , xmStringIn )				&& What is the location of the delimiter
         xmColumn = xmColumn + 1						&& Which Array column do you want to place the value in?
 
         IF xmColumn <= 26								&& Only for array columns <= 26
            xmArray [ xmPointer, xmColumn ] = LEFT( xmStringIn, xmAtPos )		&& Store the data to the array
            xmNewString = SUBSTR( xmStringIn, xmAtPos + 2, LEN( xmStringIn ) )	&& Remove the Stored Data from the remaining data
            xmStringIn = xmNewString											&& Resultant data is no thw search string again
       
			&& This line will remove the heading and trailing " from the data
            && xmArray [ xmPointer, xmColumn ] = SUBSTR( xmArray [ xmPointer, xmColumn ], 2, LEN( xmArray [ xmPointer, xmColumn ] ) - 2 )
         ENDIF 
 
      ENDDO 
 
   ENDIF 
  lnCount = lnCount + 1 
ENDDO 
 
IF xmPointer > 0
   DECLARE xmArray [ xmPointer, 26 ]	&& Last - Resize the Array down to the exact size of the data read so there is no wasted space.
ENDIF 
 
xmFileResult = FCLOSE( xmFileHandle )	&& Close the input file
 
Clear	
CREATE TABLE (pcMainData) + "csv_result" (rowcount N(4,0))

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:

trash the very bottom line, it's just junk that somehow got copied in when I pasted.

Thank you so much. I have been pulled off of this for today - working with some php stuff right now - but will give the new info a run later this evening and see how things work out.

 I really do appreciate the help.

CharlesWard,
Thanks again for the suggestions. I tried changing to 1000, but it still hangs at exactly 1074 like before. I'm not sure what to try next.

temdley,
I then gave your suggestion a whirl. All looked good, especially since I had been unable to get past 1074 records before, but then at around 2200 records the loop started slowing down dramatically, and at around 2800 records I got:
File c:\.....  temp is too large.
at which point I had to cancel. Tried it again, same results.

Any suggestions?

tmedley,
Have worked with yours some more, placing the data into tables instead of cursors. I can now process the entire file, however I wind up with around 2550 error lines (there are actually about 4) in the errors table and 1713 in the scrubbed table. The other 7500 or so lines are not in either.

I am not very skilled in FP, but I was wondering if the problem might be related to the fact that the last field one each line does not end with the "," that is used to separate all the other fields, and that perhaps when detecting/counting the delimiters this should be taken into account?

"record1-field1","record1-field2","record1-field3"
"record2-field1","record2-field2","record2-field3"

Since some fields contain commas withing the data, I changed from looking just for commas to looking for "," .


CharlesWard,
I am still working with yours as well, as I have not yet been able to get either of the solutions presented to work in my particular situation/with my data.

Sadly I have worked for more than a week with many, many incarnations of the suggestions here, but still have nothing that works and no way to verify the file.

Because this must be completed as it is delaying other work, I am turning this over to a contract programmer. I do, as always, appreciate the efforts and help that were provided.

Sorry you need to do that but fully understand. This is generally something VFP is really good at but when you are trying to import data and the values in fields sometimes contain the delimiter itself it always gets messy. If you want to email me the raw file that is stopping at 1074 with 1 method and generating way to many reported errors with the other method I would be glad to give it a look and try to post a snippet back here that works or at least tell you what is generating the bad behavior.
tmedley@goibs.com

Using email to resolve a problem
http://www.experts-exchange.com/help.jsp#hi15

Trying this again...

Some of the suggestions here were very helpful in my previous attempt at rectifying this problem, and I felt like we were on the verge of victory. Sadly though to date there is still no working solution that will allow me to verify the integrity of the csv file prior to it simply failing.

I am once again working with the script that this error affects, so I am revisiting this problem in hopes of finding a suitable solution.

Any help greatly appreciated!