Readfile and large files

[mightycpa]

Hi,

My last post was about SQL2CSV and 3 million records. Yes, I got that to work, but then I tried

Code: Select all

Readfile>file,result
Separate>result,delim,lines

and it blew up because of the size.

I returned to my SQL and modified it to get 50,000 records at a time, resulting in 69 files of records. Now I'm trying to take those files and convert them into files of INSERT statements, so that I can load my data. (Bulk load isn't working right now, and that is a problem for another day).

The trouble I'm having is that readfile takes some time, depending on how big the file is. When I use the code above, separate runs immediately, and I'm wondering whether it waits for Readfile to finish, or if it is asynchronous, and things are getting jumbled and broken because I'm not testing to see if Readfile is done. Basically, what happens is that MS gets "lost" and appears as if it is not responding.

I'm stepping through it now, and this is what I see:



While the stepper has moved on to the next line, I don't see the result, my_csv, in the Watch List. Also, why is CF_RESULT in there?

CF_RESULT
False if a CopyFile, MoveFile or RenameFile command was aborted

CF_RESULT_CODE
Numeric result code of a CopyFile, MoveFile or RenameFile command (0 is success).

I'm not copying, moving or renaming any file. Is that from the DELETEFILE> that I do?

Maybe ReadLn is more appropriate, I'll see. But I wonder if anybody has insight into using these functions with large files of text.

I'll post what I find out.

[mightycpa]

It is working... I was placing my new files in a different folder, which is why I thought I didn't see any progress. It just takes a while to write these files line by line.

Anyway, the question regarding ReadFile> stands. Is the way I'll know that it's working is that either it blows up (like the 3 million record file did) or it works?

[mightycpa]

Writing one line at a time is slow as molasses. 50K records took about an hour.

I modified the code to place a %CRLF% after each line, then accumulated them in a variable, and writing to the file once, when I'm done modifying each line.

In my small 10K line test, it ran a ton faster, and I guess that's because it avoids all that I/O of opening and closing the file 50K times.

I could really use that visible progress bar right around now.

[mightycpa]

I started monitoring the counter and noticed that the counter would slow down tremendously once the count > 15000. Now, I've backed off the write to every 10K records... we'll see how that goes.