Import Excel XLS into GUI Program vs. Database Storage

[jeansaad]

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.

[PsaltyDS]

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.

[jeansaad]

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.

[PsaltyDS]

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...

Edited June 29, 2007 by PsaltyDS

[RDSchaefer]

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

[jeansaad]

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...

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?

[PsaltyDS]

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...