Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Data comparison

Posted on 2004-09-28
3
Medium Priority
?
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 21

Accepted Solution

by:
MogalManic earned 500 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
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…
Introduction to Processes

722 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