Solved

Data comparison

Posted on 2004-09-28
3
216 Views
Last Modified: 2010-04-17
Hi,

Good day to you.


1)I  have data feed in excel which I am converting to xml.And then I validate the xml data and insert data into sql server 2000.

2)Say If i am getting data for january  , My data feed will have data for january only.

But for February I will be getting the data both for jauray and february.For march I will be getting from january till march.SO this happenens for every month.SO the data feed will keep growing.

Questions for expert
------------------------
1)Since I am validating the data  in xml (as mentioned in step 1 above) Whats the best way I can Ignore the previous months record.Please note I am having data with 27000 records.

2)Then i have to update database ,SO whats the best way to ignore data not changed.

NOte this application is written in c#.net and sql server 2000.

Thanks for your help.

Narendra








0
Comment
Question by:NarendraNisha
3 Comments
 
LVL 21

Accepted Solution

by:
MogalManic earned 125 total points
ID: 12167960
1) The XML validation is on the whole file, so I don't think you can validate part of the file.  Here are your options
       a. Split the file into two XML files
            1. Update.xml - Update data from Months from Januray to N-1 (don't validate)
            2. Insert.xml - Insert data for month N (Validate)
       b. Assuming that you are creating the XML through a well defined process, the XML will be valid.  This means that you are just validating the data.  If so, then just validate as you insert and abort the transaction on bad data:
(pseudocode)
    Read XML Data into Insert array and update array (could be any object actually)
    begin transaction
    try
          for each Row in data insert array
               Validate row data
                    If invalid, throw DataNotValidException()
               Insert Row data
          Commit data inserted
    catch All exceptions (SQL or validation)
          Rollback data
    finally
          end transaction

2) The problem is identifing the data that was updated in previous months
If your XML data has some some sort of transaction date it could be something like this:
For each Row in update data
    If updateDate>=1st of current month
        Update Row
For that matter this could be a solution to problem 1 too.  Only add to the XML if the record has changed in current month
For each Row in Excel spreadsheet
    If dataDate in current month
        add row to XML for insert
    else If updateDate>=1st of month
        Add row  to XML for update

If the XML does NOT have some sort of transaction date, you will just have to compare the data for differences.
You can compare in Oracle (probably slow)
    For each Row in Update XML
        UPDATE dataTable set a=xmlA, b=xmlB, c=xmlC
        WHERE keyCol=xmlKey and (a<>xmlA or b<>xmlB or c<>xmlC)
OR you could save the previous months XML data and compare the two files
Iterate OldData
Iterate NewData
While iterators not done
    oldData.next
    newData.next
    If oldData <> new Data
       Update row using new Data
end while
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

808 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