Solved

Data comparison

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

This is an explanation of a simple data model to help parse a JSON feed
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
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…

809 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