Pulling data from CSV

User-to-User discussion about the PhraseExpress macro programming.
Forum rules
Please read the forum disclaimer. Thank you!
Post Reply
nsyrax
Posts: 30
Joined: 13 Mar 15, 19:48

Pulling data from CSV

Post by nsyrax » 03 May 17, 19:54

I have a spreadsheet with columns I want to randomly pull data from. For example:
Column 1 = First Name
Column 2 = Last Name
I want the script to randomly pull a value from Column 1 then Column 2 to create a full name. I know I can pull data from a CSV file, but can't figure out how to do it randomly.

Peter Stotz
PhraseExpress Expert
Posts: 533
Joined: 27 Feb 08, 15:07

Re: Pulling data from CSV

Post by Peter Stotz » 03 May 17, 22:09

Hi, nsyrax
let's assume
<ll> = lower limit of the spreadsheet rows (e.g. 2)
<tl> = top limit of the spreadsheet rows (e.g. 22)
Then you can calculate a ##random_row within this range with this formula:
{#calc <ll>+{#rnd {#calc 1+<tl>-<ll> -round 0}} -round 0}

Example for the rows 2 up to 22:
{#settemp -name ##random_row -content {#calc 2+{#rnd 21} -round 0}}
First Name: {#XLS -col 1 -row {#gettemp ##random_row} -file D:\data\file.xls}
Last Name : {#XLS -col 2 -row {#gettemp ##random_row} -file D:\data\file.xls}

Peter
Win 10-1607 64 bit - PhraseExpress 12.0.143i

nsyrax
Posts: 30
Joined: 13 Mar 15, 19:48

Re: Pulling data from CSV

Post by nsyrax » 04 May 17, 15:24

Hey Peter,

Thanks for the response! I inserted the following

Code: Select all

{#settemp -name ##random_row -content {#calc 2+{#rnd 999} -round 0}}
First Name: {#XLS -col 1 -row {#gettemp ##random_row} -file C:\Users\Nick\Documents\Work\MOCK_DATA.xls}
Last Name : {#XLS -col 2 -row {#gettemp ##random_row} -file C:\Users\Nick\Documents\Work\MOCK_DATA.xls}
Where the spreadsheet has 1000 rows And the output is
First Name:
Last Name :
Am I missing something?

Peter Stotz
PhraseExpress Expert
Posts: 533
Joined: 27 Feb 08, 15:07

Re: Pulling data from CSV

Post by Peter Stotz » 04 May 17, 19:10

@nsyrax
Hmm - this seems to be all right... (?) Do you use the current PhraseExpress version 12.0.143 ?
I tested this phrase...

Code: Select all

{#settemp -name ##random_row -content {#calc 2+{#rnd 999} -round 0}}##random_row: >{#gettemp ##random_row}<
First Name  : {#XLS -col 1 -row {#gettemp ##random_row} -file D:\Dokumente\PhraseExpress\FN-LN.xls}
Last Name   : {#XLS -col 2 -row {#gettemp ##random_row} -file D:\Dokumente\PhraseExpress\FN-LN.xls}
... with the spreadsheet FN-LN.xls and it works well. Output:
##random_row: >71<
First Name : Firstname70
Last Name : Lastname70

##random_row: >474<
First Name : Firstname473
Last Name : Lastname473
and so on...

For testing see the attached spreadsheet and the phrase '#rn Random Name'
Copy the FN-LN.xls into your directory C:\Users\Nick\Documents\Work\
and replace my filespec D:\Dokumente\PhraseExpress\ with C:\Users\Nick\Documents\Work\

Peter
Win 10-1607 64 bit - PhraseExpress 12.0.143i
You do not have the required permissions to view the files attached to this post.

nsyrax
Posts: 30
Joined: 13 Mar 15, 19:48

Re: Pulling data from CSV

Post by nsyrax » 04 May 17, 20:54

This is really odd. I used your script/xls file, changed the filepath, it generates the number for the random row, but no value for first or last name.
##random_row: >86<
First Name :
Last Name :

Peter Stotz
PhraseExpress Expert
Posts: 533
Joined: 27 Feb 08, 15:07

Re: Pulling data from CSV

Post by Peter Stotz » 04 May 17, 21:40

really odd
Yes.
Do you use a PhraseExpress Standard Edition or a Pro Edition?
Is the Excel installation all right?
Is the #GETXLSCOL macro and/or the #GETXLSROW macro working ?:
http://www.phraseexpress.com/docs12/09/ ... xcelcolumn
Peter
Win 10-1607 64 bit - PhraseExpress 12.0.143i

Klein
PhraseExpress Expert
Posts: 120
Joined: 24 Mar 14, 16:11

Re: Pulling data from CSV

Post by Klein » 05 May 17, 08:26

@Peter
I have checked your phrase. It works.

@nsyrax
Does the file have several sheets? Have you anew sorted the sheets?

nsyrax
Posts: 30
Joined: 13 Mar 15, 19:48

Re: Pulling data from CSV

Post by nsyrax » 05 May 17, 21:07

Peter Stotz wrote:
04 May 17, 21:40
really odd
Yes.
Do you use a PhraseExpress Standard Edition or a Pro Edition?
Is the Excel installation all right?
Is the #GETXLSCOL macro and/or the #GETXLSROW macro working ?:
http://www.phraseexpress.com/docs12/09/ ... xcelcolumn
Peter
Win 10-1607 64 bit - PhraseExpress 12.0.143i
I'm using the Pro edition of PhraseExpress and have an up-to-date version of Excel. Is the #GETXLSROW macro something I need to add to PhraseExpress?

Peter Stotz
PhraseExpress Expert
Posts: 533
Joined: 27 Feb 08, 15:07

Re: Pulling data from CSV

Post by Peter Stotz » 05 May 17, 21:33

Is the #GETXLSROW macro something I need to add to PhraseExpress?
No - it's only another PhraseExpress macro for Excel, see http://www.phraseexpress.com/docs12/09/ ... xcelcolumn
My idea is: If #GETXLSROW works, your PhraseExpress interacts with Excel...

Peter

Post Reply