Use column letters instead of numbers for .csv functions

General Macro Scheduler discussion

Moderators: Dorian (MJT support), JRL

Post Reply
Warren
Pro Scripter
Posts: 83
Joined: Sun Oct 08, 2017 11:57 pm

Use column letters instead of numbers for .csv functions

Post by Warren » Sat Nov 04, 2017 7:07 pm

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)

User avatar
Marcus Tettmar
Site Admin
Posts: 7378
Joined: Thu Sep 19, 2002 3:00 pm
Location: Dorset, UK
Contact:

Re: Use column letters instead of numbers for .csv functions

Post by Marcus Tettmar » Sun Nov 05, 2017 9:33 am

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.
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar

Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts