need advice on check data...

[Asha]

hello...
i'm uploading data from an excel file to the server, and to check the data
values; i loop though excel file row by row and compare the value with
database value. if anything does not match i throws an error. this makes it
pretty slow if the record is big.. can someone please advice me how to
overcome this problem?

[Lucas Tam]

Re: need advice on check data...

"=?Utf-8?B?QXNoYQ==?=" <Asha@discussio ns.microsoft.co m> wrote in
news:66B34C63-CC6C-472A-8F94-EFF524E30232@mi crosoft.com:
[color=blue]
> hello...
> i'm uploading data from an excel file to the server, and to check the
> data values; i loop though excel file row by row and compare the value
> with database value. if anything does not match i throws an error.
> this makes it pretty slow if the record is big.. can someone please
> advice me how to overcome this problem?[/color]


There's not much you can do speed up your process as it is a table scan.

However, there are other methods you can use... you could create a
checksum on your data.

To create a checksum, you would pass all your excel data into a checksum
function (i.e. SHA1 or MD5 - examples in VBscript for both are online).
Perhaps the spreadsheet could feed all the values as a single long
string into the checksum function. Then store the checksum value in a
predefined cell on the excel spreadsheet (or perhaps append the checksum
to the filename?).

On the server side, you would pass all the relavant database data into a
checksum algorithm in the same format as the excel spreadsheet (i.e. in
a long string). If both checksums (Excel and Database) match, there is a
very very high probabilty the data is identical. There are rare times
when different data can create the same checksum. Certain checksums are
more unique than others. CRC32 is the most lightweight but the least
unique (likely to return a duplicate if you were to run random data
through it for a couple of weeks). MD5 or SHA1 should return values that
are pretty much unique.



--
Lucas Tam (REMOVEnntp@rog ers.com)
Please delete "REMOVE" from the e-mail address when replying.

http://members.ebay.com/aboutme/coolspot18/

[Asha]

Re: need advice on check data...

thanks... very helpful... how about if a particular column in the excel sheet
has an error. can we identify that?

"Lucas Tam" wrote:
[color=blue]
> "=?Utf-8?B?QXNoYQ==?=" <Asha@discussio ns.microsoft.co m> wrote in
> news:66B34C63-CC6C-472A-8F94-EFF524E30232@mi crosoft.com:
>[color=green]
> > hello...
> > i'm uploading data from an excel file to the server, and to check the
> > data values; i loop though excel file row by row and compare the value
> > with database value. if anything does not match i throws an error.
> > this makes it pretty slow if the record is big.. can someone please
> > advice me how to overcome this problem?[/color]
>
>
> There's not much you can do speed up your process as it is a table scan.
>
> However, there are other methods you can use... you could create a
> checksum on your data.
>
> To create a checksum, you would pass all your excel data into a checksum
> function (i.e. SHA1 or MD5 - examples in VBscript for both are online).
> Perhaps the spreadsheet could feed all the values as a single long
> string into the checksum function. Then store the checksum value in a
> predefined cell on the excel spreadsheet (or perhaps append the checksum
> to the filename?).
>
> On the server side, you would pass all the relavant database data into a
> checksum algorithm in the same format as the excel spreadsheet (i.e. in
> a long string). If both checksums (Excel and Database) match, there is a
> very very high probabilty the data is identical. There are rare times
> when different data can create the same checksum. Certain checksums are
> more unique than others. CRC32 is the most lightweight but the least
> unique (likely to return a duplicate if you were to run random data
> through it for a couple of weeks). MD5 or SHA1 should return values that
> are pretty much unique.
>
>
>
> --
> Lucas Tam (REMOVEnntp@rog ers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/
>[/color]

[Lucas Tam]

Re: need advice on check data...

"=?Utf-8?B?QXNoYQ==?=" <Asha@discussio ns.microsoft.co m> wrote in
news:EDE4A6FA-AC4D-47B8-A771-F5F5411A1D91@mi crosoft.com:
[color=blue]
> thanks... very helpful... how about if a particular column in the
> excel sheet has an error. can we identify that?[/color]

Unfortuately not with a checksum - A checksum is a calculation on all the
data.

You could create a checksum for each column - then you can narrow down
which column has the error (in which case you can specifically check the
rows in that column for an error).

--
Lucas Tam (REMOVEnntp@rog ers.com)
Please delete "REMOVE" from the e-mail address when replying.

http://members.ebay.com/aboutme/coolspot18/