Solved

Data comparison

Posted on 2004-09-28
3
191 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This is an explanation of a simple data model to help parse a JSON feed
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
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…

937 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now