LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: epetrou

Hello ,

i was wondering if i could do a process in lavastorm instead of excel macros,since the data are big and excel cant handle it..

I have a big original excel list, with 11 columns, one of them being the unique key ID (see attached file,column header is "DEALER")

What i want to do is split the first list into others based on the "DEALER" key.For example :

Adam VF400 adam@gmail.com
Adam VF400 adam@gmail.com
Eva VF401 eva@gmail.com
Eva VF401 eva@gmail.com
Eva VF401 eva@gmail.com
Sonya VF500 sonya@gmail.com


The outcome i want is to have 3 separate outputs with :
Output 1:
Adam VF400 adam@gmail.com
Adam VF400 adam@gmail.com

Output 2:
Eva VF401 eva@gmail.com
Eva VF401 eva@gmail.com
Eva VF401 eva@gmail.com

Output 3:
Sonya VF500 sonya@gmail.com

I am doing it already with a macro in excel although like i said earlier, it cant handle bigger lists than 3000 rows..

Is there a way to do it in Lavastorm ?
As a side note,the end result is to save each output in excel file and send each one through email in the address inside the file Do not know if it can be done or not just stating my needs

I use lavastorm desktop 5.0 with no additional addons
Attachments:
sample.zip

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ryeh

Splitting into lists is straight-forward, but outputting each group to a separate output will be tricky. Did you want the outputs to be back into Excel? If so, separate files (one for each ID)?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: epetrou

Yes i would like to be in excel each output, and yes separate file.

in the above example my ultimate goal is to create a new excel with the same name as the ID
Output 1: => VF400.xlsx
Adam VF400 adam@gmail.com
Adam VF400 adam@gmail.com

Output 2:=>VF401.xlsx
Eva VF401 eva@gmail.com
Eva VF401 eva@gmail.com
Eva VF401 eva@gmail.com

Output 3: => VF500.xlsx
Sonya VF500 sonya@gmail.com

and so on. Dont know if Lavastorm has any restriction, max i have 90 ID's, so 90 output (!). Its big data

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

I had a conversation with ryeh and he suggested one answer which is here. I also had a conversation with ssmith who suggested a different answer that I'll post later.

The attached graph uses the looping logic. It goes through each dealer, extracts the data for the dealer, writes the dealer data to an Excel file, then renames the file to contain the dealer iin the name.

To use this example you will need to specify the input and output directories with Run parameters. Note that in Windows you will need to change back slashes "\" in the directory names to forward slashes "/" because of how LAE interprets the back slashes in BRAINscript code.
Attachments:
SplitExcelFileData.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

I've updated the previous attachment with the example that ssmith suggested to me.

This uses a Python library named xlwt that still ships with LAE although I do not believe it is used anywhere in the latest LAE. The output is in the old .xls format instead of the more current .xlsx format. The library and node gives you control over column widths and cell formats. It runs much faster than the first example, but the speed usually isn't a big issue when working with the amounts of data where using Excel makes sense. Also, you would have to have a license for the developer pack which gives you access to run customized Python nodes.
Attachments:
SplitExcelFileData.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: epetrou

sorry for the late reply i was too busy to try either solutions..

now that i have the time i was trying to do the first example,but my lae doesn't recognize the "do for each record" node.Do i need to setup a different library ? maybe you can point me to an easy guide on how to do that ?

also browsing through the forums i came upon a "send email" node.i could use that also if i can read a how to guide

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: lnason

The "Do for Each Record" nodes and the rest of the iteration nodes were part of the 5.1 release and I see you are still on 5.0 from your message above, so you will need to upgrade.

When it comes to node help/user guides..... for most, if not all officially supported nodes you should be able to click the "Node Help" button from the top of the node editor to get information about how to use & configure a node.