I would send it as two files and let the user integrate to one file if they prefer since it is not a technical requirement of usage. Csv as you note does not support tabs. With out a hard technical requirement the effort to supply tabs may exceed the value.
On Wed, Aug 9, 2023, 7:42 AM Thanh Nguyen Duc <[email protected]> wrote: > Technically csv doesn’t support multiple tabs so it is not able to achieve. > > Thanks and Best Regards, > > Thanh. > > On 9 Aug 2023, at 19:39, Aharonu <[email protected]> wrote: > > > Hi Evan Hisey, > > Thanks for your response. > > It is end user requested preferences. We don't need to avoid multiple > files but segerate required data in one csv file with multiple tabs. > > Example, > If I want to pull filesystem which are online and offline staus. We need > both in one file cvs ox excel but two different tabs. > > File1.cvs > Tab1: Off_fs > Tab2: On_fs > > > On Wed, 9 Aug 2023, 17:57 Evan Hisey, <[email protected]> wrote: > >> Try starting with reviewing the problem the solution is trying to solve. >> Why does it have too be two tabs? Is it a technical requirement or just a >> preference? What will be consuming the data? Can it be used as two cvs? >> >> On Wednesday, August 9, 2023 at 6:56:50 AM UTC-5 Aharonu wrote: >> >>> Thank you. Even I am not much aware of Python coming in advance level. >>> >>> It looks like big task. I will wait anyone suggest us more better way >>> >>> On Wed, 9 Aug 2023, 17:15 Thanh Nguyen Duc, <[email protected]> >>> wrote: >>> >>>> Hope can help. I am not python programmer so code may not clean. >>>> from ansible.module_utils.basic import * >>>> import sys >>>> import os >>>> import csv >>>> import xlsxwriter >>>> import glob >>>> import codecs >>>> import pwd >>>> import grp >>>> def main(): >>>> fields = { >>>> "csv_dir": {"required": True, "type": "str"}, >>>> "output_xlsx_file": {"required": True, "type": "str"}, >>>> "format_header": {"required": True, "type": "bool"}, >>>> "format_error": {"required": False, "type": "list"}, >>>> "format_correct": {"required": False, "type": "list"}, >>>> "owner": {"required": False, "type": "str"}, >>>> "group": {"required": False, "type": "str"}, >>>> "split_data": {"required": False, "type": "bool"}, >>>> "summary_csv_list": {"required": False, "type": "list", "default": []}, >>>> } >>>> module = AnsibleModule(argument_spec=fields) >>>> wb = xlsxwriter.Workbook(module.params['output_xlsx_file']) >>>> format_header = wb.add_format() >>>> format_header.set_bold() >>>> format_header.set_bg_color('blue') >>>> format_header.set_font_color('white') >>>> f1 = wb.add_format({'bg_color': 'red', 'font_color': 'black', 'bold': >>>> True }) >>>> f2 = wb.add_format({'bg_color': 'green', 'font_color': 'black', 'bold': >>>> True }) >>>> f3 = wb.add_format({'border':1, 'border_color':'black', 'text_wrap': >>>> True}) >>>> csv_dir = module.params['csv_dir'] >>>> csv_file_list = sorted(glob.glob(csv_dir + '/*.csv')) >>>> summary_worksheets = [] >>>> for summary_filename_csv in module.params['summary_csv_list']: >>>> summary_csv_file_path = os.path.join(csv_dir, summary_filename_csv) >>>> summary_sheet_title = >>>> os.path.splitext(os.path.basename(summary_csv_file_path))[0][0:31] >>>> summary_ws = wb.add_worksheet(summary_sheet_title) >>>> with codecs.open(summary_csv_file_path, 'r') as summary_csvfile: >>>> summary_table = csv.reader((l.replace('\0', '') for l in >>>> summary_csvfile)) >>>> summary_num_row = 0 >>>> summary_num_cols = 0 >>>> summary_columns_width = [] >>>> for summary_row in summary_table: >>>> if module.params['format_header'] and summary_num_row == 0: >>>> summary_ws.write_row(summary_num_row, 0, summary_row, format_header) >>>> else: >>>> modified_summary_row = [] >>>> for item in summary_row: >>>> modified_summary_row.append(item) >>>> summary_ws.write_row(summary_num_row, 0, modified_summary_row, f3) >>>> summary_num_row += 1 >>>> summary_num_cols = max(summary_num_cols, len(summary_row)) >>>> summary_columns_width = [max(len(j), summary_columns_width[i] if >>>> len(summary_columns_width) > i else 1) for i, j in enumerate(summary_row)] >>>> # Simulate autofit column >>>> for i, j in enumerate(summary_columns_width): >>>> column_name = "%s:%s" % (chr(ord('A') + i), chr(ord('A') + i)) >>>> summary_ws.set_column(column_name, j) >>>> summary_worksheets.append(summary_ws) >>>> summary_ws.autofit() >>>> summary_ws.conditional_format( >>>> 'C2:C10000', >>>> {'type': 'no_blanks', 'format': f2} >>>> ) >>>> summary_ws.conditional_format( >>>> 'D2:D10000', >>>> {'type': 'no_blanks', 'format': f1} >>>> ) >>>> # Move the summary sheets to the first position >>>> for summary_ws in summary_worksheets: >>>> summary_ws.set_first_sheet() >>>> for csv_file_path in csv_file_list: >>>> if os.path.basename(csv_file_path) in module.params['summary_csv_list']: >>>> continue >>>> sheet_title = os.path.splitext(os.path.basename(csv_file_path))[0][0:31] >>>> ws = wb.add_worksheet(sheet_title) >>>> with codecs.open(csv_file_path, 'r') as csvfile: >>>> table = csv.reader((l.replace('\0', '') for l in csvfile)) >>>> num_row = 0 >>>> num_cols = 0 >>>> columns_width = [] >>>> for row in table: >>>> if module.params['format_header'] and num_row == 0: >>>> ws.write_row(num_row, 0, row, format_header) >>>> else: >>>> modified_row = [] >>>> for item in row: >>>> if ',' in item and module.params['split_data']: >>>> split_data = item.split(',') >>>> trimmed_data = [value.strip() for value in split_data] >>>> modified_row.append('\n'.join(trimmed_data)) >>>> else: >>>> modified_row.append(item) >>>> ws.write_row(num_row, 0, modified_row, f3) >>>> num_row += 1 >>>> num_cols = max(num_cols, len(row)) >>>> columns_width = [max(len(j), columns_width[i] if len(columns_width) > i >>>> else 1) for i, j in enumerate(row)] >>>> if module.params['format_error']: >>>> for i in module.params['format_error']: >>>> ws.conditional_format('A2:S10000', >>>> { >>>> 'type': 'text', >>>> 'criteria': 'containing', >>>> 'value': "%s" %i, >>>> 'format': f1 >>>> } >>>> ) >>>> if module.params['format_correct']: >>>> for i in module.params['format_correct']: >>>> ws.conditional_format('A2:S10000', >>>> { >>>> 'type': 'text', >>>> 'criteria': 'containing', >>>> 'value': "%s" %i, >>>> 'format': f2 >>>> } >>>> ) >>>> if module.params['format_header']: >>>> ws.autofilter(0, 0, num_row-1, num_cols-1) >>>> ws.autofit() >>>> wb.close() >>>> # change ownership >>>> if module.params['owner'] and module.params['group']: >>>> uid = pwd.getpwnam(module.params['owner']).pw_uid >>>> gid = grp.getgrnam(module.params['group']).gr_gid >>>> os.chown(module.params['output_xlsx_file'], uid, gid) >>>> elif module.params['owner']: >>>> uid = pwd.getpwnam(module.params['owner']).pw_uid >>>> gid = grp.getgrnam(module.params['owner']).gr_gid >>>> os.chown(module.params['output_xlsx_file'], uid, gid) >>>> elif module.params['group']: >>>> uid = pwd.getpwnam(module.params['group']).pw_uid >>>> gid = grp.getgrnam(module.params['group']).gr_gid >>>> os.chown(module.params['output_xlsx_file'], uid, gid) >>>> response = {"result": "file %s created" % >>>> (module.params['output_xlsx_file'])} >>>> module.exit_json(changed=False, meta=response) >>>> if __name__ == '__main__': >>>> main() >>>> >>>> ansible localhost -m ncs_csvtoexcel \ >>>> -a >>>> "csv_dir=/ancenter/opt-tasks/TESTPROD/Monthly/Windows/Compliance/2023-06-05 >>>> \ >>>> output_xlsx_file=/ancenter/opt-tasks/TESTPROD/Monthly/Windows/Compliance/2023-06-05/test.xlsx >>>> \ >>>> format_header=true \ >>>> format_error=Non-Compliance \ >>>> format_correct=Compliance" \ >>>> split_data: True \ >>>> owner=ancenter \ >>>> group=ancenter \ >>>> summary_csv_list=Summary_{{todaytime}}.csv" >>>> >>>> csv_dir: >>>> description: The directory containing the csv file with csv >>>> extension.The xlsx file will contain a sheet for each csv >>>> type: string >>>> required: true >>>> output_xlsx_file: >>>> description: The path of the output xlsx file >>>> type: string >>>> required: true >>>> format_header: >>>> description: If true the header(the first line of each csv) will be >>>> formatted >>>> type: boolean >>>> required: true >>>> summary_csv_list: >>>> description: List of csv files inserted in the first sheet(s) of the >>>> workbook >>>> type: list >>>> required: false >>>> format_error >>>> description: high light the keyword in red >>>> type: list >>>> required: false >>>> format_correct >>>> description: high light the keyword in green >>>> type: list >>>> required: false >>>> owner: >>>> description: change owner of file >>>> type: string >>>> required: false >>>> group: >>>> description: change group of file >>>> type: string >>>> required: false >>>> split_data: >>>> description: If true the data in all the data row will be split with >>>> comma delimiter >>>> type: boolean >>>> required: false >>>> Thanks and Best Regards, >>>> >>>> Thanh. >>>> >>>> On 9 Aug 2023, at 18:04, Aharonu <[email protected]> wrote: >>>> >>>> >>>> Hi Thanh Nguyen Duc, >>>> >>>> Thanks for quick response. May I get that reference details so I will >>>> give try for my requirement. >>>> >>>> >>>> On Wed, 9 Aug 2023, 16:29 Thanh Nguyen Duc, <[email protected]> >>>> wrote: >>>> >>>>> Csv i don’t think have multiple tabs. I have done with excel instead. >>>>> You can use j2 template to create a csv then python to consolidate them to >>>>> 1 file. >>>>> On 9 Aug 2023, at 17:55, Aharonu <[email protected]> wrote: >>>>> >>>>> >>>>> Hi Todd and Team, >>>>> >>>>> Could you please help one below query? Thank you. >>>>> >>>>> Example, I have *file1.csv *creates throught mail module as >>>>> mentionedbelow. I need to create 2 tabs (data-set1, data_set2) in >>>>> *file1.csv* and update required data. How can I deal with this? >>>>> >>>>> Thank you >>>>> >>>>> >>>>> - name: Send csv file to the user community.general.mail: host: >>>>> port: subject: Ansible-report body: Hello, this is an e-mail >>>>> from: [email protected] (Jane Jolie) to: John Doe <[email protected]> >>>>> attach: ./*file1.csv* delegate_to: localhost >>>>> >>>>> -- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "Ansible Project" group. >>>>> To unsubscribe from this group and stop receiving emails from it, send >>>>> an email to [email protected]. >>>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/ansible-project/CANGEjuUuBxieKOifmcEqN9OY6Y%2BuqEdXyG2B%2BUv_eUk1FmiOVA%40mail.gmail.com >>>>> <https://groups.google.com/d/msgid/ansible-project/CANGEjuUuBxieKOifmcEqN9OY6Y%2BuqEdXyG2B%2BUv_eUk1FmiOVA%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>>> -- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "Ansible Project" group. >>>>> To unsubscribe from this group and stop receiving emails from it, send >>>>> an email to [email protected]. >>>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/ansible-project/655EE3A2-3194-4219-B7D2-C7236C831F11%40gmail.com >>>>> <https://groups.google.com/d/msgid/ansible-project/655EE3A2-3194-4219-B7D2-C7236C831F11%40gmail.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "Ansible Project" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/ansible-project/CANGEjuWWTHkMSuTRJgKju70S9GOBSNok1oxndCkBr_aMa0vqGw%40mail.gmail.com >>>> <https://groups.google.com/d/msgid/ansible-project/CANGEjuWWTHkMSuTRJgKju70S9GOBSNok1oxndCkBr_aMa0vqGw%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "Ansible Project" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> >>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/ansible-project/C95EF81F-7406-48DD-A8ED-A4C59F5B7850%40gmail.com >>>> <https://groups.google.com/d/msgid/ansible-project/C95EF81F-7406-48DD-A8ED-A4C59F5B7850%40gmail.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>> -- >> You received this message because you are subscribed to the Google Groups >> "Ansible Project" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/ansible-project/9c9a828b-cf63-422a-b717-2fc6597a3361n%40googlegroups.com >> <https://groups.google.com/d/msgid/ansible-project/9c9a828b-cf63-422a-b717-2fc6597a3361n%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- > You received this message because you are subscribed to the Google Groups > "Ansible Project" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/ansible-project/CANGEjuXSx3gfKG9hCPpku9pDUx1%2BT88Um8g9oB%3DgX6eyJaE6yw%40mail.gmail.com > <https://groups.google.com/d/msgid/ansible-project/CANGEjuXSx3gfKG9hCPpku9pDUx1%2BT88Um8g9oB%3DgX6eyJaE6yw%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > -- > You received this message because you are subscribed to a topic in the > Google Groups "Ansible Project" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/ansible-project/2PO6VyDeAxg/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/ansible-project/397BB4BA-7C21-4E58-B9D3-EA7D8FC3F3EF%40gmail.com > <https://groups.google.com/d/msgid/ansible-project/397BB4BA-7C21-4E58-B9D3-EA7D8FC3F3EF%40gmail.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "Ansible Project" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAEcFzYy_xAboowq-N6Voq_w0no9zoSMWhbByanytbhM3DGOkKA%40mail.gmail.com.
