Solved

Dynamically generate batch files with CF?

Posted on 2004-10-04
11
520 Views
Last Modified: 2013-12-24
Hello,

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.
0
Comment
Question by:liltyga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 12221983
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....
0
 
LVL 9

Assisted Solution

by:digicidal
digicidal earned 400 total points
ID: 12222608
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.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 12222659
" 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.
0
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 
LVL 35

Expert Comment

by:mrichmon
ID: 12222660
Or at least CFDIRECTORY
0
 
LVL 9

Expert Comment

by:digicidal
ID: 12222934
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:

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

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.
0
 
LVL 9

Assisted Solution

by:digicidal
digicidal earned 400 total points
ID: 12222944
Sorry... misleading snippet... correctly:

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

forgot the file extension... just academic, but in case someone actually used that for something. :)
0
 
LVL 9

Assisted Solution

by:digicidal
digicidal earned 400 total points
ID: 12222989
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.
0
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 50 total points
ID: 12227476
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....
0
 
LVL 7

Assisted Solution

by:INSDivision6
INSDivision6 earned 50 total points
ID: 12229012
If you under Windows, the following tag supports and has examples of dynamic batch file creation:

http://www.cftagstore.com/tags/cfxexec.cfm

0
 
LVL 9

Accepted Solution

by:
digicidal earned 400 total points
ID: 12234826
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...
QueryAddRow(myQuery,1);
QuerySetCell(myQuery,'col1',Mid(varMyCurrentRow,1,12);
... 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).
0
 

Author Comment

by:liltyga
ID: 12234984
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!
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question