Sign in to follow this  
Followers 0
Lord Maim

Excel Sheet vs ?

7 posts in this topic

Thanks for all your coding help so far, you guys have saved me a lot of time. Now I need some advice.

The AutoIT script that I am trying to write needs to access a substantial amount of data. Basically, I'm trying to automate the information lookup for a large list of locations, approximately 450 sites, each having about ten to twelve attributes, such as name, location, etc. Currently I have this data in an Excel sheet. I'd like the data to be able to be updated in a seperate file that could be re-distributed to all the users who will run the script if the data changes.

Now I've read a little about ExcelCOM and ini files, maybe there's other possibilities I haven't considered. What I'm looking for is advice as to which would run the fastest overall, and the easiest way to translate the Excel sheet into whatever form you think is most efficient. Thanks much.

Share this post


Link to post
Share on other sites



Hi,

Not sure; but probably a once-off array read (use the script in _XLArrayRead()) and save as csv to distribute.

Then you need only read to array to have an easy 1D delimited file to read quckly, or a 2D array.

Best, Randall

Share this post


Link to post
Share on other sites

What has seemed to work quickly for me is save the excel file as a tab delimited txt file. Open the file with autoit and read the entire file as a text string. If you do a file readline it takes awhile but as a single text stream you can search for a key and then just parse it out by the chr(9)'s and chr(10)'s. If you have any other questions let me know.


Aha.. I have unlocked it's secrets.. world domination is within my grasp...

Share this post


Link to post
Share on other sites

What has seemed to work quickly for me is save the excel file as a tab delimited txt file. Open the file with autoit and read the entire file as a text string. If you do a file readline it takes awhile but as a single text stream you can search for a key and then just parse it out by the chr(9)'s and chr(10)'s. If you have any other questions let me know.

Parse it out into an array I assume?

In either case, it sounds as though I should be converting the file to a text file or a csv. Any other suggestions, or is that the consensus?

Share this post


Link to post
Share on other sites

@Lord Maim

I've attached a macro and a parse.txt file to the post. Save the Parse.txt file to your C:\ directory. The code should be commented well enough, but if you have any questions, let me know.

Parse.txt

parsetutorial.au3


Aha.. I have unlocked it's secrets.. world domination is within my grasp...

Share this post


Link to post
Share on other sites

That sounds like the making of a distributed database...

How do you co-ordinate the timing of the updates? What happens if two people make changes to the same data between updates? Who gets dropped?

Have you looked at XML data interchange, and Excel macros? Database software with record locking?

Share this post


Link to post
Share on other sites

That sounds like the making of a distributed database...

How do you co-ordinate the timing of the updates? What happens if two people make changes to the same data between updates? Who gets dropped?

Have you looked at XML data interchange, and Excel macros? Database software with record locking?

Actually, what I'm intending is for the data to only flow in one direction. If necessary, the "site list" would be updated by me, and distributed to the other users. The list would be used in a strictly read only capacity by the user.

Here's a clearer idea of what I'm trying to do.

1. Script will read xls/csv/ini file and get site information.

2. Script will parse the data into arrays.

3. Script will create combo boxes based on the site.

Now this is the tricky part, I want three combo boxes, but the choice of the first will affect the other two, essentially filtering the data. Lets say the first is a combo that selects the Country, which limits the second box which selects by City, which limits the third which selects by location.

I'm still learning how to apply the data in the manner I wish, but any suggestions would be appreciated.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0