Send and receive information between Excel/VBA and C

[jari.toukkari]

Hi

I use a large txt file of 1,5m lines which VBA reads into memory, and
then can searches certain values out of that data. Seach could be
faster and I wonder if c or some other language could be used for the
search engine.

So, Excel/VBA would send a variable and a big array to C, which would
search given variable from the array, and return much smaller array of
hits back to VBA.

Is this possible? How? Any kind of help needed

Regards, Jari

 

[Ivan Novick]

Hi

I use a large txt file of 1,5m lines which VBA reads into memory, and
then can searches certain values out of that data. Seach could be
faster and I wonder if c or some other language could be used for the
search engine.

So, Excel/VBA would send a variable and a big array to C, which would
search given variable from the array, and return much smaller array of
hits back to VBA.

Regards, Jari

Before changing languages... how about changing your algorithm? Is it
possible for the data to be maintained in sorted order or some other
more clever order so that you don't need to do a linear "search" for
the hits?

Ivan Novick
http://www.0x4849.net

 

[jari.toukkari]

Thanks Ivan for replying.

I have done a simple index when data is read into memory to make
searches faster. In this case index is based on manufacturer name,
which limits searched items to max 150.000, which is fast enough, and
small enough number of lines for VBA.
Text file has been sorted using a sort table of around 10 criterias to
make a fast hit, and find the more suitable items.

Though quite often manufacturer name is not known and all 1,5M lines
are searched from. This is where I need something faster and smarter
than I have today.

I thought it would be a good idea so send the searched item to a small
program created with C or any fast language. That would do the dirty
work and send a result table back to VBA.

Maybe I've seen wet daydreams again

Jari

 

[CBFalconer]

I have done a simple index when data is read into memory to make
searches faster. In this case index is based on manufacturer name,
which limits searched items to max 150.000, which is fast enough,
and small enough number of lines for VBA. Text file has been
sorted using a sort table of around 10 criterias to make a fast
hit, and find the more suitable items.

Though quite often manufacturer name is not known and all 1,5M
lines are searched from. This is where I need something faster
and smarter than I have today.

I suggest you take a look at hash tables. I have published a
library for general use (GPL licensed, but other arrangements can
be made with me), written in portable standard C, which has various
features for portability. You can find it at:

<http://cbfalconer.home.att.net/download/>

which may (or may not) be suitable for your application.

Please do not top-post. Your answer belongs after (or intermixed
with) the quoted material to which you reply, after snipping all
irrelevant material. I fixed this one. See the following links:

--
<http://www.catb.org/~esr/faqs/smart-questions.html>
<http://www.caliburn.nl/topposting.html>
<http://www.netmeister.org/news/learn2quote.html>
<http://cfaj.freeshell.org/google/> (taming google)
<http://members.fortunecity.com/nnqweb/> (newusers)

 

[Ivan Novick]

I suggest you take a look at hash tables. I have published a
library for general use (GPL licensed, but other arrangements can
be made with me), written in portable standard C, which has various
features for portability. You can find it at:

<http://cbfalconer.home.att.net/download/>

If you are going to go this route why not use Apache Portable Runtime
library?

http://apr.apache.org/docs/apr/1.2/group__apr__hash.html

Ivan Novick
http://www.0x4849.net

 

[CBFalconer]

If you are going to go this route why not use Apache Portable
Runtime library?

http://apr.apache.org/docs/apr/1.2/group__apr__hash.html

I took a cursory look at your reference. I think hashlib is much
more flexible, and simpler to understand and use. I KNOW it uses
only pure ISO standard code, suitable for both C90 and C99
compilers. Here is a short excerpt from the documentation
(hshusage.txt) file:

Using hashlib
=============

First, we need some way to refer to the table. So we must
have a data item of type hshtbl* to hold it. We will initialize
that by calling hshinit. This is much like opening a file. For
convenience here is the prototype for hshinit again:

/* initialize and return a pointer to the data base */
hshtbl *hshinit(hshfn hash, hshfn rehash,
hshcmpfn cmp,
hshdupfn dupe, hshfreefn undupe,
int hdebug);

Now this following is a fragment from your code:

hshtbl *mytable;

/* initialize and return a pointer to the data base */
mytable = hshinit(myhash, myrehash,
mycmp,
mydupe, myundupe,
0);

which tells hashlib all about the customizing functions you have
created. Note that all those functions can be static, unless
you have other uses for them outside your source file. You can
use those functions yourself as you please.

Don't forget the final 0 in the call to hshinit. That parameter
provides for future extensions and debugging abilities, and
passing a zero here will maintain compatibility.

You can create more than one hash table if you desire. If they
handle the same data format you can just do exactly the same
call as above, except you will need a new variable of type
hshtbl* to hold the table identification. If they don't hold
the same data type you can supply different functions to
hshinit. It is up to you.

*** end of excerpt ***

 

[Ivan Novick]

I took a cursory look at your reference. I think hashlib is much
more flexible, and simpler to understand and use. I KNOW it uses
only pure ISO standard code, suitable for both C90 and C99
compilers. Here is a short excerpt from the documentation
(hshusage.txt) file:

The advantage to using APR or some other widely used library, is it
has been peer reviewed and tested in production by many people all
over the world.

Ivan Novick
http://www.0x4849.net

 

[CBFalconer]

The advantage to using APR or some other widely used library, is
it has been peer reviewed and tested in production by many people
all over the world.

So has hashlib. I made a trivial adjustment last year. Before
that the last bug report was in 2002. I have had no further bug
reports. You obviously haven't looked at it.

 

[Ivan Novick]

So has hashlib. I made a trivial adjustment last year. Before
that the last bug report was in 2002. I have had no further bug
reports. You obviously haven't looked at it.

My question is: is hashlib solely your code?

I am not doubting the quality of your code... but its different to
have a solution from a single developer available from their personal
site, than to have one from a relatively large and reputable
organization like the apache foundation.

If the code was at least available as a package in the major linux
distros (redhat, suse, debian, etc) than I would be fine with it.

Regards,
Ivan Novick
http://www.0x4849.net

 

[CBFalconer]

My question is: is hashlib solely your code?

I am not doubting the quality of your code... but its different to
have a solution from a single developer available from their personal
site, than to have one from a relatively large and reputable
organization like the apache foundation.

If the code was at least available as a package in the major linux
distros (redhat, suse, debian, etc) than I would be fine with it.

You are being ridiculous. The source is there, you can evaluate
the quality for yourself.

Before I wrote it I discussed the interface with various members of
the c.l.c group. This resulted in some interface modifications.
The code is mine. I issue the licenses. Everybody gets a free GPL
license.