Sign in to follow this  
Followers 0
jeansaad

Import Excel XLS into GUI Program vs. Database Storage

7 posts in this topic

I need to develop a script that I need to get "perfect" information from an excel spreadsheet and be able to perform basic search functions on it within that script. This program is a GUI program and I would like to be able to populate a table of the data on prorgram load. I was thinking that I use an SQLite database program that I can use to import the data into the database. but for aroung 2600 names, I think this task would be daunting. Also, to populate 2600 names into a GUICtrlListView would take up some resources in order to get those names loaded. Are there any suggestions on how I can do this in a reasonably efficient way. Do I need to work with Excel directly using COM objects, with CSV files, or import that data into a local database. This data is pulled from a central location and I need it to be updated locally. I would however change this idea if there was a faster way to get the data from the central location.

Any suggestions are much appreciated.

Share this post


Link to post
Share on other sites



I need to develop a script that I need to get "perfect" information from an excel spreadsheet and be able to perform basic search functions on it within that script. This program is a GUI program and I would like to be able to populate a table of the data on prorgram load. I was thinking that I use an SQLite database program that I can use to import the data into the database. but for aroung 2600 names, I think this task would be daunting. Also, to populate 2600 names into a GUICtrlListView would take up some resources in order to get those names loaded. Are there any suggestions on how I can do this in a reasonably efficient way. Do I need to work with Excel directly using COM objects, with CSV files, or import that data into a local database. This data is pulled from a central location and I need it to be updated locally. I would however change this idea if there was a faster way to get the data from the central location.

Any suggestions are much appreciated.

OK, you have an Office spreadsheet .xls file. You want to read data from it and sort the data. Why would you put it in a database or ListView at all? You'll be reading it into an array, just keep it there for sorting and further processing.

:rolleyes:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

I want to be able to mange this file within the application. These files will be imported into the database around 4 times a year and I need this information to be displayed to the user in a graphical interface other than Excel.

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

I want to be able to mange this file within the application. These files will be imported into the database around 4 times a year and I need this information to be displayed to the user in a graphical interface other than Excel.

Well, let's break this into steps:

Step 1: Read data from .xls to an array using ExcelCOM_UDF.au3 by Locodarwin.

Can you already get that far with you .xls file and script?

Click Next when finished...

:rolleyes:

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

I want to be able to mange this file within the application. These files will be imported into the database around 4 times a year and I need this information to be displayed to the user in a graphical interface other than Excel.

Have you tried creating a GUI with a grid control?

Ralph

Share this post


Link to post
Share on other sites

Well, let's break this into steps:

Step 1: Read data from .xls to an array using ExcelCOM_UDF.au3 by Locodarwin.

Can you already get that far with you .xls file and script?

Click Next when finished...

:whistle:

I've looked at the _ExcelReadArray() function and it looks like it only reads a single row of data. My situation is a little more complicated as in I do not know the amount of rows I have to read in, only the amount of columns. Is there an efficient way to do this? If I were to write something that would read each line into an array this would waste a lot of memory. I'm talking about 2600 rows of data spanning over 7 columns. The exact number isnt precise and I need a way of finding out how many entries are in the xls file.

Have you tried creating a GUI with a grid control?

Ralph

Would this be the fastest way to read an XLS file? How would I implement this? Can I perform functions on this grid?

Share this post


Link to post
Share on other sites

I've looked at the _ExcelReadArray() function and it looks like it only reads a single row of data. My situation is a little more complicated as in I do not know the amount of rows I have to read in, only the amount of columns. Is there an efficient way to do this?

Look more closely...

; Syntax: _ExcelReadArray($oExcel, $iStartRow, $iStartColumn, $iNumCells, $iDirection = 0, $iIndexBase = 0)

Note the $iDirection parameter...

; $iDirection - The direction of the cells to read into array (0=right, 1=down)

You can, therefore, read columns into an array...

:whistle:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

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