DFA Reporting API download file in invalid format of Excel

[Hive9 Dev]

Hi,

I am using DFA Reporting API v3.3 in .Net technology and follow the below steps as per my requirement.

1. Create Report through API in Campaign Manager as per the requirement

2. Run the above created report every night

3. Download the report result in EXCEL format

4. I wants to do further process on the above downloaded excel file

I am successfully able to Create and Run the report through API v3.3 as mentioned in the class library.

For the direct download report result in Excel format, i followed the same steps mentioned under the Run method in the class library > v3.3 > Reports > DownloadFile.cs.

However, the file doesn't download in the valid excel format and getting an error while trying to open the downloaded file for the 4th step.

Here's the .net code of 3rd Step:

           

do
            {
                File file = _clientService.Files.Get(reportId, fileId).Execute();


                if ("REPORT_AVAILABLE".Equals(file.Status))
                {
                    Console.WriteLine("File status is {0}, ready to download.", file.Status);


                    FilesResource.GetRequest getRequest = _clientService.Files.Get(reportId, fileId);


                    // Optional: adjust the chunk size used when downloading the file.
                    getRequest.MediaDownloader.ChunkSize = MediaDownloader.MaximumChunkSize;


                    // Execute the get request and download the file.
                    using (System.IO.FileStream outFile = new System.IO.FileStream(GenerateFileName(file),
                        System.IO.FileMode.Create, System.IO.FileAccess.Write))
                    {
                        var status = getRequest.DownloadWithStatus(outFile);


                        // Console.WriteLine("File {0} downloaded to {1}", file.Id, outFile.Name);
                        return outFile.Name;
                    }
                    throw new Exception($"File status is {file.Status}, system didn't download the file");
                }
                else if (!"PROCESSING".Equals(file.Status))
                {
                    throw new Exception($"File status is {file.Status}, processing failed.");
                }
                else if (GetCurrentTimeInSeconds() - startTime > MAX_RETRY_ELAPSED_TIME)
                {
                    throw new Exception($"File processing deadline exceeded.");
                }


                sleep = GetNextSleepInterval(sleep);
                Console.WriteLine("File status is {0}, sleeping for {1} seconds.", file.Status, sleep);
                Thread.Sleep(sleep * 1000);
            } while (true);

private string GenerateFileName(File file)

        {

            // If no filename is specified, use the file ID instead.

            string fileName = file.FileName;

            if (String.IsNullOrEmpty(fileName))

            {

                fileName = file.Id.ToString();

            }

            String extension = "CSV".Equals(file.Format) ? ".csv" : ".xlsx";

            return System.IO.Directory.GetParent(System.AppDomain.CurrentDomain.BaseDirectory).Parent.FullName + fileName + extension;

        }

Here's the .Net code to read the above downloaded excel file:

using (System.IO.FileStream fs = new System.IO.FileStream(fullfilePath, System.IO.FileMode.Open, System.IO.FileAccess.Read))

            {

                IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(fs);

                // excelReader2007.IsFirstRowAsColumnNames = true;

                DataSet result = reader.AsDataSet();
            }

The exported file format is '.xlsx' and getting an error while calling "CreateOpenXmlReader". The above code works fine while we use the valid excel fie. I have tested the above code by putting manually created excel file and it open and able to read. However, it couldn't open/read the Google Campaign Manager exported file. 

Getting an error message: "Detected ZIP file, but not a valid OpenXml file."

Please let me know if i miss anything or need to provide some configuration while download the file in Excel format from Google in API request in the 3rd step.

[DCM API (DoubleClick Campaign Mgr)]

Hi, 
Are you having issue with downloading the report or while post processing the downloaded report at your end? Please check the following from API reference document for report format attribute:
The output format of the report. If not specified, default format is "CSV". Note that the actual format in the completed report file might differ if for instance the report's size exceeds the format's capabilities. "CSV" will then be the fallback format. Acceptable values are:

• "CSV"
• "EXCEL"

Reference links:

• https://developers.google.com/doubleclick-advertisers/v3.3/reports#resource-representations
• https://developers.google.com/doubleclick-advertisers/guides/download_reports

Thanks,
Lakshmi, DCM API Team

ref:_00D1U1174p._5001UHHujw:ref