Getting Specific Data From Excel

[TheSkubb]

Hello all,

I am looking to try and get a lot of data copied from an excel spreadsheet into another program, but I can only run 100 cells at a time. Is there any way to set up getting 100 new cells automatically instead of telling it each time to grab the values from cells a to z and if so how would I go about scripting a stop button. Since my data is variable I will never know what cell is going to be the last every time so I have no idea how to get it to stop or if this idea is even possible.

Basically the program is suppose to run as such:
1:copy cells 1-100 from excel
2:place them into another program where I collect the results
3:copy the next 100 cells and run step 2 with that set of data
continue this till the end where there is a line telling it this is the last one

I am no programmer so any advice and help would be greatly appreciated.

Thanks

[djs]

Why 100 cells? Is that a row? Or a set of rows?

You can read the entire excel sheet into memory at once using XLSheetToArray and then process it as you need to.

Outside of that, just set up a double loop, an outer loop to process through rows and an inner loop to process through the columns. Use XLGetSheetDims to know how many columns and rows there are.

Dan

[TheSkubb]

Thanks Dan,

I have to use 100 because I am imputing these numbers into a database. Any more than that and I risk a timeout which cannot happen. I am also just using one column with a large list of numbers going down. I was thinking of using the loop the only problem is I do not know how to write out a stop command when the data ends since I will never know the exact amount of data that is entered

[djs]

If I understand that you basically have a single column of data, you want to move that column to the other file, its a fairly easy task.

I assume the original file is static (not being added to) while you are working with it. If so, simply read the whole file using XLSheetToArray (or CSVFileToArray). You can then use the Array_COUNT to know how many are in the file.

Then setup a loop from 1 to Array_COUNT and write Array_%k%_1 to the second file. (%k% being the loop counter).

Alternatively, if you need to read the original file item by item, then I assume you are using ReadLn. Its easy to detect the end of the file, but in the way of experimenting, try it out:
ReadLn>c:\temp\test.txt,1000000,line

Assuming 1000000 is beyond the end of the file, use the debugger and see what is returned in line.

It should be ##EOF##
Write a loop exit if line=##EOF##

Other things you might see (other than the line you expected) are ##NOFILE## or ##ERR##. I get the ERR message a lot when I have the file open in Excel and try to read it. Excel tends to lock files for exclusive use.

Dan

[TheSkubb]

Thanks again Dan, but right now I cant seem to copy out the block of data that I want. Is there a way to pull just specific blocks from the excel sheet itself and not the array? Unless you can copy chunks of the array I think it might be easier to set it up from the sheet.

[djs]

How do you determine what 'block' of data you want?

You can read any line in the file. I'm not sure if you have an excel spreadsheet (.xlsx) or a text file (.csv). If its a text file, just use:

ReadLn>File Path,105,mydataline
In this case, 105 is the line of data you want to read, it can be any line number that is valid in the file.

If its excel, then you have to do a bit more, but same concept:
XLOpen>excel file,1,xlbook
(the 1 in this case makes it visible, set to 0 if you don't need to see excel open)
XLGetCell>xlbook,Sheet1,105,1,mydata
XLQuit>xlbook

Don't forget the xlquit command.
On the XLGetCell line, the 105 again is the line you want to read. You can have this be a variable inside of a loop.

The 1 is the column you want to read, so in this case, it would be the first column, but change this to whatever column you want.

The data is stored in the variable mydata.

A few catches on the Excel side is I haven't found a way to read the sheet names, so I always use Sheet1. You need to make sure the sheet name matches the actual sheet name in the file.

The XLOpen and XLClose commands are a bit slow (like opening excel). If you are looping looking for something, do the open, then loop to find what ever it is, then close. Don't open and close on every read.

Dan

[nodochau]

Hello Dan,
I came across your topic and start to learn the XLSheettoArray command. I am confusing the counting though.
I create a sample Excel sheet which has 3 cols and 5 rows and use:
XLSheettoArray>filename,Sheet1,xldata
I get xldata_1_1 is a data in row1 col 1 that is easy.
But xldata_1_count and xldata_2_count and they are both return 4. What is the value of 4 since the sheet has 5 rows and 3 cols.
And xldata_count returns 6 ?? what is 6
The bottom line is that what is the return value of :
arrayname_row_count
arrayname_count

Please help.
Thanks

[Dorian (MJT support)]

You can find that explained here.

arrayname_row_count = how many columns of data are in that row.
arrayname_count = how many rows there are (including the header)

[nodochau]

Thank you for the quick response.
I am working on it now.

[nodochau]

Hi Dorian,
In my sample as I mentioned above, there are 3 cols and but why arrayname_row_count returns 4. It should return 3, right?

[Grovkillen]

Hi Dorian,
In my sample as I mentioned above, there are 3 cols and but why arrayname_row_count returns 4. It should return 3, right?

What you see and how the XLS looks like are two different things. There's probably some syntax in the fourth row. I could be simply that you clicked on a cell....

[nodochau]

Hi Dorian,
In my sample as I mentioned above, there are 3 cols and but why arrayname_row_count returns 4. It should return 3, right?

What you see and how the XLS looks like are two different things. There's probably some syntax in the fourth row. I could be simply that you clicked on a cell....

The arrayname_row_count returns how many cols in the row. The spreadsheet is create from new blank.

[Dorian (MJT support)]

It certainly seems to add an extra blank row and column, even if that column has never been clicked in or contained data. I have asked Marcus if he has any ideas why this happens.

Brand new Sheet. Column D never clicked in.
Row 5 cell will have been active after entering cat/ringo/plane, and the cell below becomes auto-selected - so that may explain the extra row. But the column is a mystery.

dog john car
horse paul bus
fish george train
cat ringo plane

XLSheetToArray>d:\Book1.xls,Sheet1,myarr

Returns one blank column, and one blank row.

0: MYARR_1_1=dog
0: MYARR_1_2=john
0: MYARR_1_3=car
0: MYARR_1_4=
0: MYARR_1_COUNT=4
0: MYARR_2_1=horse
0: MYARR_2_2=paul
0: MYARR_2_3=bus
0: MYARR_2_4=
0: MYARR_2_COUNT=4
0: MYARR_3_1=fish
0: MYARR_3_2=george
0: MYARR_3_3=train
0: MYARR_3_4=
0: MYARR_3_COUNT=4
0: MYARR_4_1=cat
0: MYARR_4_2=ringo
0: MYARR_4_3=plane
0: MYARR_4_4=
0: MYARR_4_COUNT=4
0: MYARR_5_1=
0: MYARR_5_2=
0: MYARR_5_3=
0: MYARR_5_4=
0: MYARR_5_COUNT=4
0: MYARR_COUNT=5

[Dorian (MJT support)]

I've reported this to dev as it might be a bug. You're not doing anything wrong - we both experienced the same results.

In the mean time it's easy enough to work around - just set your loop limit one less than length, or add some checks and balances in.

[nodochau]

I've reported this to dev as it might be a bug. You're not doing anything wrong - we both experienced the same results.

In the mean time it's easy enough to work around - just set your loop limit one less than length, or add some checks and balances in.

It sounds good now. Thanks again Dorian.