Missing country in the invoices csv export

[ShapesGS]

Hello,

When exporting invoices csv, I have Billing Address and Shipping Address columns, but they do not display the country, only adress, city and zip code.

Any idea on how to fix this please ?

Best regards,

David

[Rakesh Jesadiya]

@ShapesGS

You have to override core file Magento_Sales/etc/di.xml file.

Just keep below code in virtualType of ShippingAddressAggregator and BillingAddressAggregator.

<item name="country_id" xsi:type="array">
      <item name="tableAlias" xsi:type="string">sales_billing_address</item>
      <item name="columnName" xsi:type="string">country_id</item>
 </item>

Final code,

 
 <virtualType name="ShippingAddressAggregator" type="Magento\Framework\DB\Sql\ConcatExpression">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="street" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">street</item>
                </item>
                <item name="city" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">city</item>
                </item>
                <item name="region" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">region</item>
                </item>
                <item name="postcode" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">postcode</item>
                </item>
                
                <item name="country_id" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">country_id</item>
                </item>
            </argument>

            <argument name="separator" xsi:type="string">, </argument>
        </arguments>
    </virtualType>
    <virtualType name="BillingAddressAggregator" type="Magento\Framework\DB\Sql\ConcatExpression">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="street" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">street</item>
                </item>
                <item name="city" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">city</item>
                </item>
                <item name="region" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">region</item>
                </item>
                <item name="postcode" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">postcode</item>
                </item>
                <item name="country_id" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">country_id</item>
                </item>
            </argument>
            <argument name="separator" xsi:type="string">, </argument>
        </arguments>
    </virtualType>

Please clean cache, Remove Var/generation folder.

if issue solved, Click Kudos/Accept as solutions.

[ShapesGS]

Hi @Rakesh Jesadiya,

Thank you for your answer.

I made some modules of my own, but never overriding di.xml, so I do not know if I'm right here.

I created app/code/Vendor/MagentoSalesOverride/etc/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <preference for="Magento\Sales" type="Vendor\MagentoSalesOverride"/>
    <virtualType name="ShippingAddressAggregator" type="Magento\Framework\DB\Sql\ConcatExpression">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="street" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">street</item>
                </item>
                <item name="city" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">city</item>
                </item>
                <item name="region" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">region</item>
                </item>
                <item name="postcode" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">postcode</item>
                </item>
                
                <item name="country_id" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">country_id</item>
                </item>
            </argument>

            <argument name="separator" xsi:type="string">, </argument>
        </arguments>
    </virtualType>
    <virtualType name="BillingAddressAggregator" type="Magento\Framework\DB\Sql\ConcatExpression">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="street" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">street</item>
                </item>
                <item name="city" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">city</item>
                </item>
                <item name="region" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">region</item>
                </item>
                <item name="postcode" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">postcode</item>
                </item>
                <item name="country_id" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">country_id</item>
                </item>
            </argument>
            <argument name="separator" xsi:type="string">, </argument>
        </arguments>
    </virtualType>
</config>

But it does not seem to work, maybe because I did not create I created app/code/Vendor/MagentoSalesOverride/etc/module.xml.. ?

[Rakesh Jesadiya · Accepted solution]

You have to do below process for your getting country in export csv file,

app/code/Vendor/MagentoSalesOverride/registration.php

<?php
\Magento\Framework\Component\ComponentRegistrar::register(
    \Magento\Framework\Component\ComponentRegistrar::MODULE,
    'Vendor_MagentoSalesOverride',
    __DIR__
);

app/code/Vendor/MagentoSalesOverride/etc/module.xml
In module you have to give dependancy on Magento_Sales module.

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Module/etc/module.xsd">
    <module name="Vendor_MagentoSalesOverride" setup_version="1.0.0">  
	<sequence>
            <module name="Magento_Sales"/>
	</sequence>
    </module>
</config>

Create file,

app/code/Vendor/MagentoSalesOverride/etc/di.xml

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <virtualType name="ShippingAddressAggregator" type="Magento\Framework\DB\Sql\ConcatExpression">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="street" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">street</item>
                </item>
                <item name="city" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">city</item>
                </item>
                <item name="region" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">region</item>
                </item>
                <item name="postcode" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">postcode</item>
                </item>
                <item name="country_id" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_shipping_address</item>
                    <item name="columnName" xsi:type="string">country_id</item>
                </item>
            </argument>
            <argument name="separator" xsi:type="string">, </argument>
        </arguments>
    </virtualType>
    <virtualType name="BillingAddressAggregator" type="Magento\Framework\DB\Sql\ConcatExpression">
        <arguments>
            <argument name="columns" xsi:type="array">
                <item name="street" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">street</item>
                </item>
                <item name="city" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">city</item>
                </item>
                <item name="region" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">region</item>
                </item>
                <item name="postcode" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">postcode</item>
                </item>
                <item name="country_id" xsi:type="array">
                    <item name="tableAlias" xsi:type="string">sales_billing_address</item>
                    <item name="columnName" xsi:type="string">country_id</item>
                </item>
            </argument>
            <argument name="separator" xsi:type="string">, </argument>
        </arguments>
    </virtualType>
</config>

Run,

php bin/magento setup:upgrade
php bin/magento setup:static-content:deploy

clear cache.
Remove var/generation folder.

if issue resolved, Click Kudos/Accept as solutions.

[ShapesGS]

Hi @Rakesh Jesadiya,

Thanks again for your effort, but it does not seem to work, country ID is not being added, I tried to export sales and invoices without luck.

[Rakesh Jesadiya]

@ShapesGS,

Yes You havent display in export country because Export action takes value directly from Database.

IN your database already invoice entry are generated and you can check inside sales_order_invoice table billing and shipping_address field with not added country id at suffix of address.

I have given code for new invoice generated those invoice entry are create Country id field in shipping and billing_address field now.

So This is valid for now new invoice of order. This code is not working for already generated invoice entry.

If you want to get billing and shipping address field with Country Then you have to doing something changes inside all those already generated field with update sql query code to add country id at suffix in each row.

Hope this will help you.

Please let me know if you have any issue.

If Issue resolved click kudos/accept as solutions.

[ShapesGS]

@Rakesh Jesadiya

OK, thanks for the info, I will check when I have a new order and tell you if it's OK.

[ShapesGS]

Hello,

Sorry for late feedback, your code is working great, thanks again !

Best regards,

David

[jinalshahs29fc]

@Rakesh Jesadiya 
How can we achieve if we want to display the country_id for the existing orders too?