Try using a python to merge csvs into xlsx and shell module before mail
module.
----------------------------------------------
localhost@root tmp # cat csv_xlsx_combine.py
import pandas as pd
csv1_data = pd.read_csv('file1.csv')
csv2_data = pd.read_csv('file2.csv')
xlsx_writer = pd.ExcelWriter('output.xlsx', engine='openpyxl')
csv1_data.to_excel(xlsx_writer, sheet_name='Sheet1', index=False)
csv2_data.to_excel(xlsx_writer, sheet_name='Sheet2', index=False)
xlsx_writer.save()
-------------------------------------------------------------
for above python code to work, you ll need pandas and openpyxl modules
install alongside python3
pip install pandas
pip install openpyxl
------------------------------------------------------------------------------------------
tasks:
- name: Preparing attachment
shell: |
python3 /tmp/csv_xlsx_combine.py
ls /tmp/output.xlsx && echo "Good to go"
- name: Sendmail
community.general.mail:
subject: "{{ email_subject }}"
body: "{{ email_body }}"
attach: "/tmp/output.xlsx"
sender: "{{ email_sender }}"
to: "{{ email_recipient }}"
host: "{{ smtp_host }}"
port: "{{ smtp_port }}"
username: "{{ smtp_username }}"
password: "{{ smtp_password }}"
delegate_to: localhost
- debug:
msg: "Mail sent successfully"
this should work
On Wed, 9 Aug 2023 at 18:23, Aharonu <[email protected]> wrote:
> 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
> <https://groups.google.com/d/msgid/ansible-project/CANGEjuUfk4pbm%3Du9aBAP_A1K9FKb%3DgGN2uvynq%3DQ67AyLsoSZw%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/CAMZqFbv0-FWEudwO-bBv7tdQXXH7nFCgQ6s0SqOpUgZMWXWkxg%40mail.gmail.com.
