LAE

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

Originally posted by: tmjaisonmathew

Here is my situation, I need to split the records into multiple excel sheets in a single excel file. Thanks to one of the sample brg in forum able to split. But there is issue in the format of big value, it is auto formatting to exponential value. I think it is the default behaviour of excel sheet.

Below is the records going to pipe to a excel sheet, my question is i can make the text bold using the style below "font: bold on" so is there any way can format the value as $ XXXXXXXX. XX something like using style, do you know what should I put for the style.


Thanks so much if you can help me this.


FileName SheetName Row Column DataField Unicode Style
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 1 1 Unicode MODE font: bold on
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 1 2 Unicode VALUE_DATE font: bold on
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 1 3 Unicode ACCOUNT_NO font: bold on
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 1 4 Unicode BATCH_NO font: bold on
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 1 5 Unicode CR_ITEM font: bold on
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 1 6 Unicode CR_BATCH_AMT font: bold on
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 1 7 Unicode DR_ITEM font: bold on
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 1 8 Unicode DR_BATCH_AMT font: bold on
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 1 9 Unicode ONLINE_REJECT font: bold on
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 1 10 Unicode AUTOPAY_EXPRESS font: bold on
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 2 1 Unicode B NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 2 2 Unicode 14/09/2016 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 2 3 Unicode 001-001262-2 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 2 4 Unicode 00041 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 2 5 Unicode 2 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 2 6 Unicode 3843.02 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 2 7 Unicode 0 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 2 8 Unicode 0 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 2 9 Unicode 0 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 2 10 Unicode E NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 3 1 Unicode B NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 3 2 Unicode 14/09/2016 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 3 3 Unicode 001-001464-1 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 3 4 Unicode 02238 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 3 5 Unicode 223 NULL
/data/groups/XX/DOWNLOAD/MTHLY YYYY Template Nov 2016.xls B 3 6 Unicode 5.26008e+07 NULL

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

Originally posted by: mlam

Hi tmjaisonmathew,

I don't understand your question...do you mean that you're trying to output data to an Excel file and be able to dictate the cell formatting in Excel? If so, using the option to output to a template Excel file sounds like what you need. With this functionality, you can output data from Lavastorm to Excel using the Output Excel node and use the "TemplateInputFile" property on the Optional tab in the node editor to reference an Excel file that is already set up with cell formatting.

There is an example here that might be helpful: http://community.lavastorm.com/threa...template+excel

Of course, please reply if I have misunderstood.

Michelle

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

Originally posted by: tmjaisonmathew

Hi Michele,

Thanks so much for your reply. My original requirement is split the records based on one column value to different excel sheets in one excel file, for that I able to find solution in the community.

But unfortunately this solution is using Agg node and don't allow template during excel file generation and here is the code for generating the cells from the source data using Agg node. Even though source data is double but when open the excel sheet it showed exponential value as string (aligned to left) . Please see the attached picture, Cells is the aggr node which is generating the excel data

I only have two options.

1) Display double value=> for this only I am requesting for help.Any workaround to show the data as double instead of string. Attached below is the snapshot of brg. Cells is the node I am looking for solution.
2) Use any other way to split the records to multiple excel sheets in single excel file.But need to maintain the datatype when open the excel sheet. I am searching for the solution past 3 days without any hope.


if firstExec then {
fieldNames = inputFields(1)
numberOfFields = fieldNames.len()
}

sheetRecordNumber = groupCount()

output 1 {
emit 'FileName'
emit 'SheetName'
emit int(null) as "Row"
emit int(null) as "Column"
emit str(null) as "DataField"
emit unicode(null) as "Unicode"
emit str(null) as "Style"
# emit numberOfFields
where false
}

# write header row
if firstInGroup then {
fld = 0
col = 1
while fld < numberOfFields {
fieldName = fieldNames[fld]
if fieldName == "FileName"
or fieldName == "SheetName"
then {
fld = fld + 1
} else {
do output 1 {
emit 1 as "Row"
emit col as "Column"
emit "Unicode" as "DataField"
emit unicode(fieldName)
as "Unicode"
emit "font: bold on" as "Style"
}
fld = fld + 1
col = col + 1
}
}
}

# write data rows
fld = 0
col = 1
row = sheetRecordNumber + 1
while fld < numberOfFields {
fieldName = fieldNames[fld]
if fieldName == "FileName"
or fieldName == "SheetName"
then {
fld = fld + 1
} else {
do output 1 {
emit row as "Row"
emit col as "Column"
emit "Unicode" as "DataField"
emit field(fieldName)
as "Unicode"
emit str(null) as "Style"
}
fld = fld + 1
col = col + 1
}
}

GET_HELP.gif

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

Originally posted by: mlam

Hi tmjaisonmathew,

Are you able to provide your BRG file, sample input file and sample of what you want the output to look like?

Thanks,
Michelle

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

Originally posted by: tmjaisonmathew

Sure thanks so much, I am new to this forum, this only allows image and video only. How to upload brg files.

Just another question, do you know how to merge multiple excel sheets into one excel file.

I have multiple brd files which have processed multiple excel sheets from a single file .I need to merge it back to to single file. Do you know how to do it.

Sorry to ask this question, I need to finish it by tmr, if no solution i need to go this approach.

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

Originally posted by: mlam

When you click "reply", there is a button in the bottom right labelled "Go Advanced". That takes you to a reply window with more options, including to manage attachments, which accepts BRG, txt and zip files among others.

If you have multiple BRD files, you can read them all in using a combination of a Directory List node (to get the full file paths for the BRDs) and connect to a Multiple BRD Files node, which will read all the files in together, after which you can output the results to a single Excel file.
If the data is already read in Lavastorm, you can just use the Cat node to append the data sets together.

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

Originally posted by: awilliams1024

Re. "I have multiple brd files which have processed multiple excel sheets from a single file .I need to merge it back to to single file. Do you know how to do it."

There are probably more elegant ways of doing this, but you could possibly use the Append Excel node to merge the data sets. See the attached example data flow.

Append_Excel_Multiple_Sheets_Example.brg

In the example your processed data is represented by the two static data nodes. Each of the data sets were connected to the Append Excel node.
The Append Excel node was then configured so that the names of the input pins reflected the required named for the worksheets [use the IO tab to change the names].
The 'SheetMatch' method property was set to use the Name of the input pin to locate the worksheet. The 'DataAppendMethod' was set to clear the contents of an existing matching sheet.
Since the default action of the node is to create a worksheet if it does not exist (see the 'NoSheetForInputAction' property) it will create the required sheet with the new data from each input pin.

Note that while the file does not have to exist, the destination directory must exist.

Regards,
Adrian