Solved: Add column to newsletter CSV

dave_lynch1 · ‎09-21-2018

Hi,

I want to add a column to the export csv for newsletter subscribers, the column will be the change_status_at. Could someone advise me on how to go about this?

Thanks in advance!

gelanivishal · ‎09-21-2018

Hello @dave_lynch1,

Please add below code on this file vendor/magento/module-newsletter/view/adminhtml/layout/newsletter_subscriber_block.xml

<?xml version="1.0"?>
<!--
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
    <body>
        <referenceBlock name="adminhtml.newsletter.subscriber.container">
            <block class="Magento\Newsletter\Block\Adminhtml\Subscriber\Grid" name="adminhtml.newslettrer.subscriber.grid" as="grid">
                <arguments>
                    <argument name="id" xsi:type="string">subscriberGrid</argument>
                    <argument name="dataSource" xsi:type="object">Magento\Newsletter\Model\ResourceModel\Subscriber\Grid\Collection</argument>
                    <argument name="default_sort" xsi:type="string">subscriber_id</argument>
                    <argument name="default_dir" xsi:type="string">desc</argument>
                    <argument name="use_ajax" xsi:type="string">1</argument>
                </arguments>
                <block class="Magento\Backend\Block\Widget\Grid\Massaction" name="adminhtml.newslettrer.subscriber.grid.massaction" as="grid.massaction">
                    <arguments>
                        <argument name="massaction_id_field" xsi:type="string">subscriber_id</argument>
                        <argument name="form_field_name" xsi:type="string">subscriber</argument>
                        <argument name="use_select_all" xsi:type="string">1</argument>
                        <argument name="options" xsi:type="array">
                            <item name="unsubscribe" xsi:type="array">
                                <item name="label" xsi:type="string" translate="true">Unsubscribe</item>
                                <item name="url" xsi:type="string">*/*/massUnsubscribe</item>
                            </item>
                            <item name="delete" xsi:type="array">
                                <item name="label" xsi:type="string" translate="true">Delete</item>
                                <item name="url" xsi:type="string">*/*/massDelete</item>
                            </item>
                        </argument>
                    </arguments>
                </block>
                <block class="Magento\Backend\Block\Widget\Grid\Export" name="adminhtml.newslettrer.subscriber.grid.export" as="grid.export">
                    <arguments>
                        <argument name="exportTypes" xsi:type="array">
                            <item name="csv" xsi:type="array">
                                <item name="urlPath" xsi:type="string">*/*/exportCsv</item>
                                <item name="label" xsi:type="string" translate="true">CSV</item>
                            </item>
                            <item name="excel" xsi:type="array">
                                <item name="urlPath" xsi:type="string">*/*/exportXml</item>
                                <item name="label" xsi:type="string" translate="true">Excel XML</item>
                            </item>
                        </argument>
                    </arguments>
                </block>
                <block class="Magento\Backend\Block\Widget\Grid\ColumnSet" name="adminhtml.newslettrer.subscriber.grid.columnSet" as="grid.columnSet">
                    <arguments>
                        <argument name="id" xsi:type="string">problemGrid</argument>
                    </arguments>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.subscriber_id" as="subscriber_id">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">ID</argument>
                            <argument name="index" xsi:type="string">subscriber_id</argument>
                            <argument name="header_css_class" xsi:type="string">col-id</argument>
                            <argument name="column_css_class" xsi:type="string">col-id</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.email" as="email">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Email</argument>
                            <argument name="index" xsi:type="string">subscriber_email</argument>
                            <argument name="header_css_class" xsi:type="string">col-email</argument>
                            <argument name="column_css_class" xsi:type="string">ccol-email</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.type" as="type">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Type</argument>
                            <argument name="index" xsi:type="string">type</argument>
                            <argument name="type" xsi:type="string">options</argument>
                            <argument name="options" xsi:type="array">
                                <item name="guest" xsi:type="array">
                                    <item name="value" xsi:type="string">1</item>
                                    <item name="label" xsi:type="string" translate="true">Guest</item>
                                </item>
                                <item name="customer" xsi:type="array">
                                    <item name="value" xsi:type="string">2</item>
                                    <item name="label" xsi:type="string" translate="true">Customer</item>
                                </item>
                            </argument>
                            <argument name="header_css_class" xsi:type="string">col-type</argument>
                            <argument name="column_css_class" xsi:type="string">col-type</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.firstname" as="firstname">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Customer First Name</argument>
                            <argument name="index" xsi:type="string">firstname</argument>
                            <argument name="default" xsi:type="string">----</argument>
                            <argument name="header_css_class" xsi:type="string">col-first-name</argument>
                            <argument name="column_css_class" xsi:type="string">col-first-name</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.lastname" as="lastname">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Customer Last Name</argument>
                            <argument name="index" xsi:type="string">lastname</argument>
                            <argument name="default" xsi:type="string">----</argument>
                            <argument name="header_css_class" xsi:type="string">col-last-name</argument>
                            <argument name="column_css_class" xsi:type="string">col-last-name</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.status" as="status">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Status</argument>
                            <argument name="index" xsi:type="string">subscriber_status</argument>
                            <argument name="type" xsi:type="string">options</argument>
                            <argument name="options" xsi:type="array">
                                <item name="status_not_active" xsi:type="array">
                                    <item name="value" xsi:type="string">2</item>
                                    <item name="label" xsi:type="string" translate="true">Not Activated</item>
                                </item>
                                <item name="status_subscribed" xsi:type="array">
                                    <item name="value" xsi:type="string">1</item>
                                    <item name="label" xsi:type="string" translate="true">Subscribed</item>
                                </item>
                                <item name="status_unsubscribed" xsi:type="array">
                                    <item name="value" xsi:type="string">3</item>
                                    <item name="label" xsi:type="string" translate="true">Unsubscribed</item>
                                </item>
                                <item name="status_unconfirmed" xsi:type="array">
                                    <item name="value" xsi:type="string">4</item>
                                    <item name="label" xsi:type="string" translate="true">Unconfirmed</item>
                                </item>
                            </argument>
                            <argument name="header_css_class" xsi:type="string">col-status</argument>
                            <argument name="column_css_class" xsi:type="string">col-status</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.change_status_at" as="change_status_at">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Status At</argument>
                            <argument name="type" xsi:type="string">date</argument>
                            <argument name="timezone" xsi:type="boolean">false</argument>
                            <argument name="index" xsi:type="string">change_status_at</argument>
                            <argument name="column_css_class" xsi:type="string">col-date</argument>
                            <argument name="header_css_class" xsi:type="string">col-date</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column\Multistore" name="adminhtml.newslettrer.subscriber.grid.columnSet.website" as="website">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Web Site</argument>
                            <argument name="index" xsi:type="string">website_id</argument>
                            <argument name="type" xsi:type="string">options</argument>
                            <argument name="options" xsi:type="options" model="Magento\Config\Model\Config\Source\Website\OptionHash"/>
                            <argument name="header_css_class" xsi:type="string">col-website</argument>
                            <argument name="column_css_class" xsi:type="string">col-website</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column\Multistore" name="adminhtml.newslettrer.subscriber.grid.columnSet.group" as="group">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Store</argument>
                            <argument name="index" xsi:type="string">group_id</argument>
                            <argument name="type" xsi:type="string">options</argument>
                            <argument name="options" xsi:type="options" model="Magento\Newsletter\Block\Subscribe\Grid\Options\GroupOptionHash"/>
                            <argument name="header_css_class" xsi:type="string">col-store</argument>
                            <argument name="column_css_class" xsi:type="string">col-store</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column\Multistore" name="adminhtml.newslettrer.subscriber.grid.columnSet.store" as="store">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Store View</argument>
                            <argument name="index" xsi:type="string">store_id</argument>
                            <argument name="type" xsi:type="string">options</argument>
                            <argument name="options" xsi:type="options" model="Magento\Newsletter\Block\Subscribe\Grid\Options\StoreOptionHash"/>
                            <argument name="header_css_class" xsi:type="string">col-store-view</argument>
                            <argument name="column_css_class" xsi:type="string">col-store-view</argument>
                        </arguments>
                    </block>
                </block>
            </block>
        </referenceBlock>
    </body>
</page>

After please run below command in magento root directory

php bin/magento setup:static-content:deploy -f
php bin/magento cache:flush
php bin/magento cache:clean

Then please export data, You will get change_status_at data in CSV.

Let me know if you have any issue.

--
If my answer is useful, please Accept as Solution & give Kudos

View solution in original post

gelanivishal · ‎09-21-2018

Hello @dave_lynch1

Please replace below code for change_status_at column at line number 134

<block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.change_status_at" as="change_status_at">
    <arguments>
        <argument name="header" xsi:type="string" translate="true">Status At</argument>
        <argument name="type" xsi:type="string">date</argument>
        <argument name="timezone" xsi:type="boolean">false</argument>
        <argument name="index" xsi:type="string">change_status_at</argument>
        <argument name="column_css_class" xsi:type="string">col-date</argument>
        <argument name="header_css_class" xsi:type="string">col-date</argument>
        <argument name="format" xsi:type="string">1</argument>
    </arguments>
</block>

In this code, you can see format node which value 1 - That is a standard date format, You can replace that by below value and you will get an appropriate result for it.

0 - Sep 21, 2018
1 - September 21, 2018
2 - Sep 21, 2018
3 - 9/21/18

After change value, don't forget to run below command in magento root directory

php bin/magento setup:static-content:deploy -f
php bin/magento cache:flush
php bin/magento cache:clean

--
If my answer is useful, please Accept as Solution & give Kudos

View solution in original post

gelanivishal · ‎09-21-2018

Hello @dave_lynch1,

Please add below code on this file vendor/magento/module-newsletter/view/adminhtml/layout/newsletter_subscriber_block.xml

<?xml version="1.0"?>
<!--
/**
 * Copyright © Magento, Inc. All rights reserved.
 * See COPYING.txt for license details.
 */
-->
<page xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:View/Layout/etc/page_configuration.xsd">
    <body>
        <referenceBlock name="adminhtml.newsletter.subscriber.container">
            <block class="Magento\Newsletter\Block\Adminhtml\Subscriber\Grid" name="adminhtml.newslettrer.subscriber.grid" as="grid">
                <arguments>
                    <argument name="id" xsi:type="string">subscriberGrid</argument>
                    <argument name="dataSource" xsi:type="object">Magento\Newsletter\Model\ResourceModel\Subscriber\Grid\Collection</argument>
                    <argument name="default_sort" xsi:type="string">subscriber_id</argument>
                    <argument name="default_dir" xsi:type="string">desc</argument>
                    <argument name="use_ajax" xsi:type="string">1</argument>
                </arguments>
                <block class="Magento\Backend\Block\Widget\Grid\Massaction" name="adminhtml.newslettrer.subscriber.grid.massaction" as="grid.massaction">
                    <arguments>
                        <argument name="massaction_id_field" xsi:type="string">subscriber_id</argument>
                        <argument name="form_field_name" xsi:type="string">subscriber</argument>
                        <argument name="use_select_all" xsi:type="string">1</argument>
                        <argument name="options" xsi:type="array">
                            <item name="unsubscribe" xsi:type="array">
                                <item name="label" xsi:type="string" translate="true">Unsubscribe</item>
                                <item name="url" xsi:type="string">*/*/massUnsubscribe</item>
                            </item>
                            <item name="delete" xsi:type="array">
                                <item name="label" xsi:type="string" translate="true">Delete</item>
                                <item name="url" xsi:type="string">*/*/massDelete</item>
                            </item>
                        </argument>
                    </arguments>
                </block>
                <block class="Magento\Backend\Block\Widget\Grid\Export" name="adminhtml.newslettrer.subscriber.grid.export" as="grid.export">
                    <arguments>
                        <argument name="exportTypes" xsi:type="array">
                            <item name="csv" xsi:type="array">
                                <item name="urlPath" xsi:type="string">*/*/exportCsv</item>
                                <item name="label" xsi:type="string" translate="true">CSV</item>
                            </item>
                            <item name="excel" xsi:type="array">
                                <item name="urlPath" xsi:type="string">*/*/exportXml</item>
                                <item name="label" xsi:type="string" translate="true">Excel XML</item>
                            </item>
                        </argument>
                    </arguments>
                </block>
                <block class="Magento\Backend\Block\Widget\Grid\ColumnSet" name="adminhtml.newslettrer.subscriber.grid.columnSet" as="grid.columnSet">
                    <arguments>
                        <argument name="id" xsi:type="string">problemGrid</argument>
                    </arguments>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.subscriber_id" as="subscriber_id">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">ID</argument>
                            <argument name="index" xsi:type="string">subscriber_id</argument>
                            <argument name="header_css_class" xsi:type="string">col-id</argument>
                            <argument name="column_css_class" xsi:type="string">col-id</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.email" as="email">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Email</argument>
                            <argument name="index" xsi:type="string">subscriber_email</argument>
                            <argument name="header_css_class" xsi:type="string">col-email</argument>
                            <argument name="column_css_class" xsi:type="string">ccol-email</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.type" as="type">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Type</argument>
                            <argument name="index" xsi:type="string">type</argument>
                            <argument name="type" xsi:type="string">options</argument>
                            <argument name="options" xsi:type="array">
                                <item name="guest" xsi:type="array">
                                    <item name="value" xsi:type="string">1</item>
                                    <item name="label" xsi:type="string" translate="true">Guest</item>
                                </item>
                                <item name="customer" xsi:type="array">
                                    <item name="value" xsi:type="string">2</item>
                                    <item name="label" xsi:type="string" translate="true">Customer</item>
                                </item>
                            </argument>
                            <argument name="header_css_class" xsi:type="string">col-type</argument>
                            <argument name="column_css_class" xsi:type="string">col-type</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.firstname" as="firstname">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Customer First Name</argument>
                            <argument name="index" xsi:type="string">firstname</argument>
                            <argument name="default" xsi:type="string">----</argument>
                            <argument name="header_css_class" xsi:type="string">col-first-name</argument>
                            <argument name="column_css_class" xsi:type="string">col-first-name</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.lastname" as="lastname">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Customer Last Name</argument>
                            <argument name="index" xsi:type="string">lastname</argument>
                            <argument name="default" xsi:type="string">----</argument>
                            <argument name="header_css_class" xsi:type="string">col-last-name</argument>
                            <argument name="column_css_class" xsi:type="string">col-last-name</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.status" as="status">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Status</argument>
                            <argument name="index" xsi:type="string">subscriber_status</argument>
                            <argument name="type" xsi:type="string">options</argument>
                            <argument name="options" xsi:type="array">
                                <item name="status_not_active" xsi:type="array">
                                    <item name="value" xsi:type="string">2</item>
                                    <item name="label" xsi:type="string" translate="true">Not Activated</item>
                                </item>
                                <item name="status_subscribed" xsi:type="array">
                                    <item name="value" xsi:type="string">1</item>
                                    <item name="label" xsi:type="string" translate="true">Subscribed</item>
                                </item>
                                <item name="status_unsubscribed" xsi:type="array">
                                    <item name="value" xsi:type="string">3</item>
                                    <item name="label" xsi:type="string" translate="true">Unsubscribed</item>
                                </item>
                                <item name="status_unconfirmed" xsi:type="array">
                                    <item name="value" xsi:type="string">4</item>
                                    <item name="label" xsi:type="string" translate="true">Unconfirmed</item>
                                </item>
                            </argument>
                            <argument name="header_css_class" xsi:type="string">col-status</argument>
                            <argument name="column_css_class" xsi:type="string">col-status</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.change_status_at" as="change_status_at">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Status At</argument>
                            <argument name="type" xsi:type="string">date</argument>
                            <argument name="timezone" xsi:type="boolean">false</argument>
                            <argument name="index" xsi:type="string">change_status_at</argument>
                            <argument name="column_css_class" xsi:type="string">col-date</argument>
                            <argument name="header_css_class" xsi:type="string">col-date</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column\Multistore" name="adminhtml.newslettrer.subscriber.grid.columnSet.website" as="website">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Web Site</argument>
                            <argument name="index" xsi:type="string">website_id</argument>
                            <argument name="type" xsi:type="string">options</argument>
                            <argument name="options" xsi:type="options" model="Magento\Config\Model\Config\Source\Website\OptionHash"/>
                            <argument name="header_css_class" xsi:type="string">col-website</argument>
                            <argument name="column_css_class" xsi:type="string">col-website</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column\Multistore" name="adminhtml.newslettrer.subscriber.grid.columnSet.group" as="group">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Store</argument>
                            <argument name="index" xsi:type="string">group_id</argument>
                            <argument name="type" xsi:type="string">options</argument>
                            <argument name="options" xsi:type="options" model="Magento\Newsletter\Block\Subscribe\Grid\Options\GroupOptionHash"/>
                            <argument name="header_css_class" xsi:type="string">col-store</argument>
                            <argument name="column_css_class" xsi:type="string">col-store</argument>
                        </arguments>
                    </block>
                    <block class="Magento\Backend\Block\Widget\Grid\Column\Multistore" name="adminhtml.newslettrer.subscriber.grid.columnSet.store" as="store">
                        <arguments>
                            <argument name="header" xsi:type="string" translate="true">Store View</argument>
                            <argument name="index" xsi:type="string">store_id</argument>
                            <argument name="type" xsi:type="string">options</argument>
                            <argument name="options" xsi:type="options" model="Magento\Newsletter\Block\Subscribe\Grid\Options\StoreOptionHash"/>
                            <argument name="header_css_class" xsi:type="string">col-store-view</argument>
                            <argument name="column_css_class" xsi:type="string">col-store-view</argument>
                        </arguments>
                    </block>
                </block>
            </block>
        </referenceBlock>
    </body>
</page>

After please run below command in magento root directory

php bin/magento setup:static-content:deploy -f
php bin/magento cache:flush
php bin/magento cache:clean

Then please export data, You will get change_status_at data in CSV.

Let me know if you have any issue.

--
If my answer is useful, please Accept as Solution & give Kudos

dave_lynch1 · ‎09-21-2018

Hi @gelanivishal That's great! Do you know how I can changed the date format as when we import this data to our 3rd party management system we need it to pass their data format validation.

Thanks!!

gelanivishal · ‎09-21-2018

Hello @dave_lynch1

Please replace below code for change_status_at column at line number 134

<block class="Magento\Backend\Block\Widget\Grid\Column" name="adminhtml.newslettrer.subscriber.grid.columnSet.change_status_at" as="change_status_at">
    <arguments>
        <argument name="header" xsi:type="string" translate="true">Status At</argument>
        <argument name="type" xsi:type="string">date</argument>
        <argument name="timezone" xsi:type="boolean">false</argument>
        <argument name="index" xsi:type="string">change_status_at</argument>
        <argument name="column_css_class" xsi:type="string">col-date</argument>
        <argument name="header_css_class" xsi:type="string">col-date</argument>
        <argument name="format" xsi:type="string">1</argument>
    </arguments>
</block>

In this code, you can see format node which value 1 - That is a standard date format, You can replace that by below value and you will get an appropriate result for it.

0 - Sep 21, 2018
1 - September 21, 2018
2 - Sep 21, 2018
3 - 9/21/18

After change value, don't forget to run below command in magento root directory

php bin/magento setup:static-content:deploy -f
php bin/magento cache:flush
php bin/magento cache:clean

--
If my answer is useful, please Accept as Solution & give Kudos

Add column to newsletter CSV

Add column to newsletter CSV

Re: Add column to newsletter CSV

Re: Add column to newsletter CSV

Re: Add column to newsletter CSV

Re: Add column to newsletter CSV

Re: Add column to newsletter CSV