Does the input file have to be in .csv format?
I’d like to use it for a server log that is tab delimited and has a .log extension….
It makes life easier if its delimited somehow. I mostly use pipes "|", since you tend to have commas in your files. Tabs would work fine. The extension doesn’t necessarily matter, as long as its a readable text document and not binary.
thanks sabre. does it recognize fields like awk in unix scripting?
my log file kind of looks like this
serverlog 4 hello blahblahblah serverlog 4 goodbye dfdfdfdfdf objerror 0 hrm dfdfdfdff
with awk im able to use print $1 or print $2 to differentiate between the fields for parsing. im inserting these into an oracle database and parsing each of the fields into its column in the record.
whats making this difficult is that i have to work directly with the log file. id like to use sql loader instead of manually inserting with sqlplus in a do while loop within my unix script.
You could do it with PHP. PHP does have functions for file management.
I have not tested this, as i am at work, but this should work. You pretty much open up the file and read it line by line and insert the data into the table.
/*
DB Connection info goes here */
$filename="log.txt"
$handle = fopen("$filename", "r");
while (($data = fgetcsv($handle, 1000, "t")) !== FALSE)
{
$import="INSERT into info(name,address,food, recipe) values('$data[0]','$data[1]','$data[2]', '$data[3]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
/* DB close info goes here */
print "Import done";
One question though, will it always be the same format of 4 columns? I can test this in about an hour to make sure it works, but if you want, go ahead and give it a shot.
mysql? if so look for a program called sqlyog you can import all sorts of data formats including csv comma delimited stuff.
Haha wish it was mysql. Sorry should have specified the database is oracle 11g and the log files are from siebel and obiee. There could be more columns depending on the log file I pull. However they are pretty much all the same format of tab delimited. The related field columns also aren’t all exactly the same distance from the beginning of the line. For example the 2nd field may start 20 characters over every time except for a few records that are 21 characters over. This doesn’t seem to bother awk in unix.
The script i posted would work for 4 columns. will they always have a header line? If so, you could either find some import software that oracle offers to do it, or write a script that checks the header lines and maybe files it accordingly? I have something like that working on one of our windoze servers here. It steps through all the files in a directory and finds the output that contains a certain header then imports into the respective table. Its been ages since i’ve messed with it.
cool thanks for the info. im using LFA for the siebel stuff but it doesn’t work for OBIEE. OBIEE is rough, most of it isn’t even documented properly