Error When Exporting Products to Excel

I am using NC 2.70 and when I try to export all products to excel I receive a Problem loading page - The connection was reset. Does anyone know how I can resolve this? I currently have 3,444 products.

I think I've seen that once before; took so long it timed out.  Try it again.

I have tried several times, but every time it times out. Any other ideas?

Does anyone know the SQL query that is run against the database to create the Excel file? I could run it directly instead of through the website if I could find out what it was.

You could try an export to XML and then import it into Excel. You will get more lines per variant to include all info included in other tables.

If you are exporting a List<T> this is a little extension method that I use to export to .csv which can be opened in excel.

It is so much faster than excel.

The usage is drop dead simple:

public MyControllerAction()
{
   var myProducts = _productService.GetAllProducts();
   return myProducts.ExportCSV("my-filename");
}

Here are the extension methods

        public static string GetCSV<T>(this IList<T> list)
        {
            var sb = new StringBuilder();

            //Get the properties for T for the headers
            var propertyInfos = typeof(T).GetProperties();
            for (var i = 0; i <= propertyInfos.Length - 1; i++)
            {
                sb.Append(propertyInfos[i].Name);
                if (i < propertyInfos.Length - 1)
                    sb.Append(",");
            }
            sb.AppendLine();

            //Loop through collection then the properties and add values
            for (var i = 0; i <= list.Count - 1; i++)
            {
                var item = list[i];
                for (var j = 0; j <= propertyInfos.Length - 1; j++)
                {
                    var o = item.GetType().GetProperty(propertyInfos[j].Name).GetValue(item, null);
                    if (o != null)
                    {
                        var val = o.ToString();

                        //check if val contains a comma and escape
                        if (val.Contains(","))
                            val = string.Concat("\"", val, "\"");

                        //replace any \r or \n special characters from a new line with a space
                        if (val.Contains("\r"))
                            val = val.Replace("\r", " ");
                        if (val.Contains("\n"))
                            val = val.Replace("\n", " ");

                        sb.Append(val);
                    }

                    if (j < propertyInfos.Length - 1)
                        sb.Append(",");
                }
                sb.AppendLine();
            }
            return sb.ToString();
        }

        public static void ExportCSV(this string csv, string filename)
        {

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("content-disposition",
                string.Format("attachment; filename={0}-{1:MM-dd-yyyy_hh-mm}.csv", filename, DateTime.UtcNow));
            HttpContext.Current.Response.ContentType = "text/csv";
            HttpContext.Current.Response.AppendCookie(new HttpCookie("fileDownloadToken", "vz29xo4"));
            HttpContext.Current.Response.AddHeader("Pragma", "public");
            HttpContext.Current.Response.Write(csv);
            HttpContext.Current.Response.End();
        }

        public static void ExportCSV<T>(this IList<T> list, string filename)
        {
            var csv = GetCSV(list);
            csv.ExportCSV(filename);
        }

If this works for you tell a friend.  I don't know why anyone would want to map fields to excel.

Good luck.

t

UPDATE:  I should probably mention the AppendCookie line.  I use this for modal confirmation boxes.  e.g.  If a user is about to download a large file from a grid or something like that I use a modal popup for them to confirm (in case they clicked in error).  That cookie get's passed in the response stream which can then be used to close the modal window.  If you don't use a modal confirmation window just comment out that line.

eadameg wrote:
I tried the Export to XML, but I get the same result.

SilhouetteBS wrote:
It's not a SQL query. That's why it's so slow. It loops through a list of products, and then queries for their variants.

                foreach (var p in products)
                {
                    var productVariants = _productService.GetProductVariantsByProductId(p.Id, true);
                    foreach (var pv in productVariants)

Worse, it then has to query for list of mapped Categories, Manufacturers, and Pictures.  (in the inner pv loop no less, even though those entities are on the product).  And in 2.70 and later, worse, it needs to get localized SEO Name

So, if you don't need the latter, then you can run the following in SSMS, and then paste into Excel (set cell format of entire sheet to Text).  

Caveat:  "NULL" will appear where the real export shows blank, and 1 or 0 will show instead of TRUE or FALSE.  You can use ISNULL() or DECODE() to adjust as needed.


select
    p.Name,
    ShortDescription,
    FullDescription,
    ProductTemplateId,
    ShowOnHomePage,
    MetaKeywords,
    MetaDescription,
    MetaTitle,
    --SeName,
    AllowCustomerReviews,
    p.Published,
    pv.Name as ProductVariantName,
    SKU,
    ManufacturerPartNumber,
    Gtin,
    IsGiftCard,
    GiftCardTypeId,
    RequireOtherProducts,
    RequiredProductVariantIds,
    AutomaticallyAddRequiredProductVariants,
    IsDownload,
    DownloadId,
    UnlimitedDownloads,
    MaxNumberOfDownloads,
    DownloadActivationTypeId,
    HasSampleDownload,
    SampleDownloadId,
    HasUserAgreement,
    UserAgreementText,
    IsRecurring,
    RecurringCycleLength,
    RecurringCyclePeriodId,
    RecurringTotalCycles,
    IsShipEnabled,
    IsFreeShipping,
    AdditionalShippingCharge,
    IsTaxExempt,
    TaxCategoryId,
    ManageInventoryMethodId,
    StockQuantity,
    DisplayStockAvailability,
    DisplayStockQuantity,
    MinStockQuantity,
    LowStockActivityId,
    NotifyAdminForQuantityBelow,
    BackorderModeId,
    AllowBackInStockSubscriptions,
    OrderMinimumQuantity,
    OrderMaximumQuantity,
    AllowedQuantities,
    DisableBuyButton,
    DisableWishlistButton,
    CallForPrice,
    Price,
    OldPrice,
    ProductCost,
    SpecialPrice,
    SpecialPriceStartDateTimeUtc,
    SpecialPriceEndDateTimeUtc,
    CustomerEntersPrice,
    MinimumCustomerEnteredPrice,
    MaximumCustomerEnteredPrice,
    Weight,
    Length,
    Width,
    Height,
    pv.CreatedOnUtc
    --CategoryIds,
    --ManufacturerIds,
    --Picture1,
    --Picture2,
    --Picture3,
from ProductVariant pv
join Product p
  on p.Id = pv.ProductId
where pv.Deleted = 0
  and p.Deleted = 0                


joebloe wrote:
Nice!  But why change CR/LF's to space.  CSV would not do that.  Just quotes around text - same as for comma.