Noted. Thank you!
On Wed, 9 Aug 2023, 18:17 Evan Hisey, <[email protected]> wrote: > 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 > <https://groups.google.com/d/msgid/ansible-project/CAEcFzYy_xAboowq-N6Voq_w0no9zoSMWhbByanytbhM3DGOkKA%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/CANGEjuUfk4pbm%3Du9aBAP_A1K9FKb%3DgGN2uvynq%3DQ67AyLsoSZw%40mail.gmail.com.
