Dynamically generate batch files with CF?


I have the task of automating a previously manual process, one step of which is to load the contents of a .dat file into the Oracle DB using sqlldr. (This .dat file is coming from an SAP system that is behind the firewall, and there is no way that I can change that process)

The sqlldr statement looks like this:
load data
          infile filename_LOG_mmddyy.dat (this filename changes daily)
          append into table tablename
          (  field1     position (01:12) ,
             field2     position (13:112),
             field3     position (113:113) ,
             field4     position (114:123),
             field5     position (124:133),
             field6     position (134:197) ,
             field7     position (198:198),
             field8     position (199:201),
             field9     position (202:301) ,
             field10   position (302:351)

My first question is - can I just write a SQL statement to update the database by reading the .dat file dynamically? If so, how?

My second question is - If I can't programatically update the Oracle DB by using CF to read the .dat file, can I dynamically create a batch file on the fly to update the database, since the .dat file that the control file is reading will change on a daily basis, so I want to make the filename a variable that can be manipulated. If that is possible, can someone send an example of how to dynamically generate a batch file with CF?

If I am going about this all wrong, please let me know if I am missing a much easier way to do this.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

digicidalConnect With a Mentor Commented:
mrichmon is correct in his examples... however I surmise from your question that the logs are flat files with fixed positions...

In this case there is no way to directly query the file itself to my knowledge... that is why I indicated that you could then use the cffile action="read" to first capture the data and then use string functions to walk the logs and create a useable query with the QueryNew(), etc... functions in CF.  If your log files are indeed delimited in some fashion then by all means query the file directly. :)

If not I would think that you can essentially look at the information returned by the file read as a ridiculously long list of CRLF delimited values.  Each element of which could be extracted into cells by something like:

myQuery = QueryNew(col1,col2... col10);
... Then loop over the list and do...
... End your loop action when all columns for the row are populated.

... Now you can access your myQuery variable as if you had queried your database for it.

Are we missing something in your question or have one of us or both answered it already and are just having our own discussion now?  I can't say I complain either way... mrichmon is good company. LOL!

Let us know - since 3/4 of it is done by now we can probably just flesh out the script and you can cut and paste - but I 'truncated' my response short of that incase you want to work on your own (potentially better) solution.  The only thing to note if you are not familiar with string functions is that unlike your query with position start and end identifiers you are providing a start position and a count (the size of the field value itself).
If you know that the .dat file is in a folder where it is the only dat file then you can use CFFILE to look for a filename of *.bat, then read in the contents, then parse to your DB I would think....
digicidalConnect With a Mentor Commented:
Yes, I do this all the time with CSV's, but it is equally simple with Flats - just read the contents into a variable and then pull out your pieces as the fields with LEFT() or MID() as your fields.  If you want to pre-validate (incase you don't have triggers) you could even use the dynamic query functions ( QueryNew(), QueryAddRow(), QuerySetCell(), etc..) to create a regular CFQuery variable which you could then clean or check for valid formatting prior to the insert into the SQL.  Many possibilities.  It's also possible to use CFQUERY directly on a text data source file provided that the formatting is compatible.  I haven't done this myself (haven't needed to), but I have seen many examples on other sites.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

" It's also possible to use CFQUERY directly on a text data source file provided that the formatting is compatible."

Yes but I think you need the filename to do this which is where the CFFILE comes in.
You are quite correct, although perhaps I am mistaken, but I interpreted the  filename_LOG_mmddyy.dat  name to mean that the filename could also be determined programmatically with logic similar to:

varThisLog = 'filename_LOG_' & NumberFormat(DatePart('m',Now()),'00');
varThisLog = varThisLog & NumberFormat(DatePart('d',Now()),'00');
varThisLog = varThisLog & DatePart('yyyy',Now());

Yes, if the file name is not known you would have to use CFFILE and/or CFDIRECTORY to search for the most recent file of a particular type, etc.
digicidalConnect With a Mentor Commented:
Sorry... misleading snippet... correctly:

varThisLog = 'filename_LOG_' & NumberFormat(DatePart('m',Now()),'00');
varThisLog = varThisLog & NumberFormat(DatePart('d',Now()),'00');
varThisLog = varThisLog & DatePart('yyyy',Now()) & '.dat';

forgot the file extension... just academic, but in case someone actually used that for something. :)
digicidalConnect With a Mentor Commented:
Perhaps only because I've worked with a few clients that had apps dumping logs with similar names I assume that the 'filename_' part is consistent with a particular log originator: ie that there is a SRV03FINANCIAL_LOG_01012004.dat file and a SRV05HRUPLOAD_LOG_01012004.dat file etc...

Is this correct,  liltyga?  Is there anything else you are required to do with the data prior to the append or are you simply looking to automate that process exactly?  I think mrichmon and myself have provided adequate information for your solution, but let us know if there is anything that's unclear or causing problems.
mrichmonConnect With a Mentor Commented:
Oh yes I overlooked taht part.
If there is a programatic way such as the date to determine the filename then that would mean you could just query the file directly if it is well formed structure (i.e. always has every field with a delimeter)

Example of well formed:

col1, col2, col3, col4
1, 2, 3, 4
5, , 6, 7

Example of not well formed

col1    col2   col3    col4
1          2         3       4
5                    6         7

(In the second example you don't know that a value is missing because tehre are no delimeters....
INSDivision6Connect With a Mentor Commented:
If you under Windows, the following tag supports and has examples of dynamic batch file creation:


liltygaAuthor Commented:
Thanks so much for everyone's help - my email was down and I am just getting  a chance to check this post out. When I get back to work I will try out your detailed suggestions, and award the points accordingly. Thanks again!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.