Use column letters instead of numbers for .csv functions

[Warren]

After auditioning a half dozen csv editors, and seeing how difficult it is on many of them to refer to columns as numbers, it occurs to me that it would be much easier if I could just match what they are expecting on the MS side by having MS use column letters.

Using letters is also probably better for my dyslexic brain to make sure I'm always clear which is the column indicator, and which is the row indicator, and it's quite confusing and prone to human error that each of the programs refers to column 1 as "B"

I can do something like not show the column names, and fill in a blank column with my own, but this is compounding the human readability issue of the first row now being row 2.

I suppose I could come up with a kludgey way to assign a bunch of variables along the lines of Let>A=0, etc. but that might cause variable conflicts elsewhere if letters are used, and I wanted to check to see if there is a built in, standard, or otherwise simpler approach to be able to read and write .csv's using column letters rather than numbers in MS so that it matches the xl conventions (looking at openoffice Calc specifically as the one I'll likely use)

[Marcus Tettmar]

Two methods that spring to mind:

1. Create variables called colA, colB .... colZZ. Use a subroutine to do this and run it at start:

Code: Select all

GoSub>InitAlphas

..
XLGetCell>xlH,Sheet1,5,colD,result

SRT>InitAlphas
//add more to end if needed ...
Let>alphas=A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z
Separate>alphas,COMMA,alpha
Let>k=0
Repeat>k
  Let>k=k+1
  Let>this=alpha_%k%
  Let>coi%this%=%k%
Until>k=alpha_count
End>InitAlphas

2. Instead of creating variables we can use the position of the character in a list:

Code: Select all

//Define list at top:
Let>alphas=ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ

//Now use in XLGetCell:  
XLGetCell>xlH,Sheet1,5,{Pos("D",%alphas%)},result

That may seem more "messy" but you could make your own GetCell subroutine which makes it look neater:

Code: Select all

XLOpen>%SCRIPT_DIR%\samples\example.xls,1,xlH

//Use our subroutine instead of XLGetCell:

GoSub>myGetCell,xlH,Sheet1,2,B,result

MessageModal>result

SRT>myGetCell
  Let>alphas=ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZ
  XLGetCEll>myGetCell_Var_1,myGetCell_Var_2,myGetCell_Var_3,{Pos(%myGetCell_Var_4%,%alphas%)},tmp
  Let>%myGetCell_Var_5%=%tmp%
END>myGetCell

So with the last method we use GoSub>myGetCell instead of XLGetCell whenever we want.