This discussion is archived
5 Replies Latest reply: Nov 13, 2013 2:08 AM by TriTechWorks RSS

Problem in handling complex CSV file

Anindya Gayen Newbie
Currently Being Moderated

Hi,

 

I am facing a strange problem in handling a complex CSV file.

The content of the file is as follows. It is getting executed through an XSD and the target is a table.

First time when the interface is executed ( for e.g session id - 19001) it is getting completed successfully.

 

Now when making changes in the CSV file suppose adding ( for e.g.-

DEPT,D05,Books,ABC Store

CUST,C01,Sayantan,sayantan@retail.com

CUST,C02,Shubham,shubham@retail.com)

 

or deleting ( for e.g.-

CUST,C02,Sarbajit,sarbajit@retail.com)

 

and running the interface I am not getting the added D05 in the target table nor my C02 data is getting removed i.e.- the updated data in CSV file is not getting fetched and I am getting the same records as i got when i ran interface of session id 19001.

 

I am not getting why it is happening??

 

The CSV file used in session id -19001 is:

 

DEPT,D01,Retail,World Mart

CUST,C01,Anindya,anindya@retail.com

CUST,C02,Rashni,Rashni@retail.com

DEPT,D02,Food,Food Bazar

CUST,C01,Abhijit,abhijit@retail.com

CUST,C02,Anirban,anirban@retail.com

CUST,C03,Sharmistha,sarmistha@retail.com

DEPT,D03,Water,SW

CUST,C01,Nirmalya,nirmalya@retail.com

DEPT,D04,Clothes,City style

CUST,C02,Sarbajit,sarbajit@retail.com

CUST,C03,Abhishek,abhishek@retail.com

  • 1. Re: Problem in handling complex CSV file
    Anindya Gayen Newbie
    Currently Being Moderated

    Can pls anyone help me with the above problem.

     

    Thanks.

  • 2. Re: Problem in handling complex CSV file
    _Phil Journeyer
    Currently Being Moderated

    I think you need to synchronize your file.

     

    The first time you run the interface, ODI creates the tables based on your XSD and populates them with the data.  If you change the data in the XML file, then you need to synchronize the file for the changes to be picked up.  See B.4.9 SYNCHRONIZE in the XML driver documentation:

     

    Oracle Data Integrator Driver for XML Reference

  • 3. Re: Problem in handling complex CSV file
    Anindya Gayen Newbie
    Currently Being Moderated

    Hi,

     

    Thanks for the reply.

    I have got the solution.

    Synchronization is not needed over here.

     

    If we configure the Data Server in the following manner, any update or delete in CSV file will reflect in the target.

     

     

    jdbc:snps:complexfile?f=C:\cstdb\cstdb.csv&d=C:\cstdb\cstdb.xsd&re=Root&ro=true&dod=yes


    Here dod means drop on disc. It automatically drops the schema when we close the JDBC connection.

  • 4. Re: Problem in handling complex CSV file
    _Phil Journeyer
    Currently Being Moderated

    Glad you got sorted.  Yep, DOD will do the trick too.  I personally prefer to maintain the tables post-load and use synchronize, especially if it's a reusable process and I expect the files I'm loading to be in a consistent format.

     

    But as always, whatever works best for your process.

  • 5. Re: Problem in handling complex CSV file
    TriTechWorks Newbie
    Currently Being Moderated

    Here's what you can do to handle CSV files using HSQL.

     

    Say the CSV file contains order data. Each order record contains data about a particular order like order number, product code, quantity, ordered by, date etc. Now lets take a hypothetical requirement which says, the user needs to know how many orders were ordered per product.


    Option 1]  At the basic level, read the order file, parse each line and write the logic to get count of orders per product.

    Option 2] Load this CSV file into a database like MySQL and write database queries to get the orders per product.

     

    So where does this leave us? Have we run out of options? I am sure we would have tried & used the above two options . But I wanted a different approach. Following questions were lingering in my mind:

    1] Why can’t I write SQL queries against the CSV file itself. After all it’s like any RDBMS table.

    2] Why should I load the CSV file into some database before I query it?

    3] Why can’t I create a database table & attach the CSV file to it?

     

    Continue Reading Here: Handling CSV Files

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points