Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

Parse text file and query dabase and email missingList as CSV file

I have a pipe(|) separated text file with many rows of data. I need to parse the text file and generate a query and query oracle database and see which records are not there in database( each row of the text field corresponds to each record in database). Then I need to prepare missing List CSV file and email the user about the missing list values. I am treating whole text file as array object and individual rows as again small arrays with many values(could be string, numericals etc) separated by | symbol.
Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance
0
gudii9
Asked:
gudii9
  • 5
  • 3
1 Solution
 
sdstuberCommented:
use external table to read the file

then use MINUS to compare your external table to another table

loop through the results to construct a csv clob then email the clob as an attachment with utl_smtp

example code for doing the csv construction and emailing can be found here...

http://www.experts-exchange.com/Database/Oracle/A_7749-How-to-send-email-attachments-with-Oracle.html
0
 
gudii9Author Commented:
http://www.velocityreviews.com/forums/t390377-using-scanner-with-a-bar-delimiter.html

I checked above link.

It is separating at |.

Now I need to get first two strings and form a query with those first 2 stings to run againest the oracle database.

 Then generate missing list CSV report(with those first two strings) if those records not present in oracle databse and email the the user missing CSV file(i have the emailing part code also with me).

I am not sure on how to do following things


>>
Now I need to get first two strings and form a query with those first 2 stings to run againest the oracle database.

>>
 Then generate missing list CSV report(with those first two strings) if those records not present in oracle databse

Please advise
0
 
sdstuberCommented:
please provide sample data for a file, table and what you expect the email to look like
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gudii9Author Commented:
My class looks like this

import java.io.File;
import java.io.FileNotFoundException;
import java.util.Scanner;

public class TheClass {

      private static void readFile(String filename, String delim) {
            try {
                  File file = new File(filename);
                  Scanner scanner = new Scanner(file);
                  scanner.useDelimiter(System.getProperty("line.separator"));
                  while (scanner.hasNext()) {
                        System.out.println();
                        String theNext = scanner.next();
                        System.out.println("------>"+theNext);
                        parseline(theNext, delim);
                  }
                  scanner.close();
            } catch (FileNotFoundException e) {
                  e.printStackTrace();
            }
      }

      public static void parseline(String line, String delim) {
            /*Scanner lineScanner = new Scanner(line);
            lineScanner.useDelimiter(delim);
      //      System.out.println("delimiter is >" + lineScanner.delimiter() + "<");
            int a = lineScanner.nextInt();
            String b = lineScanner.next();
            String c = lineScanner.next();
            String d = lineScanner.next();*/
      /*      System.out.println(
                        "a = " + a +
                        ", b= " + b +
                        ", c = " + c +
                        ", d = " + d);
                        */
      }

      public static void main(String[] args) {
            readFile("C:\\Test.txt", "|");
            System.out.println();
      
      }
}


my text file looks as in attachment
Test.txt
0
 
sdstuberCommented:
what does your table look like?  and what are the expected results?

I wouldn't use java for this
0
 
gudii9Author Commented:
i have less control to do any thing on database side being java programmer.

Each of following record in text file  correspond to one record in database. It is simple table with 6 columns to host following text file values


999|5555|6666|PPP|9/9/2009 15:49|1444-AAA_111|FINISH
998|5554|6666|PPP|9/9/2009 15:49|1444-AAA_111|FINISH
997|5553|6666|PPP|9/9/2009 15:49|1444-AAA_111|FINISH
996|5552|6666|PPP|9/9/2009 15:49|1444-AAA_111|FINISH


Please advise
0
 
sdstuberCommented:
but you're looking for differences and then want to generate some kind of result

give an example of what you want to happen with the table and the file don't match

also, what would want to happen if you found nothing different?
0
 
sdstuberCommented:
but...

if you're looking for a java solution.  Simply run this query....


select  * from yourtable...


and then do whatever you want inside the java.


If you want something more complete (and not java) please provide examples as requested above.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now