Expand my Community achievements bar.

Announcement: Calling all learners and mentors! Applications are now open for the Adobe Analytics 2024 Mentorship Program! Come learn from the best to prepare for an official certification in Adobe Analytics.

Make exports to other systems, especially Excel, painless

Avatar

Level 2

5/15/12

Data Extracts, Data Warehouse and plain report exports as CSV almost always use a format that requires some wrangling to get working in Excel, and are really difficult to import into other formats.

 

For example, a simple Data Extract of page views by hour gives some output like this:

 

Hour,Page Views 12 AM May 9,25921 1 AM,14771 2 AM,8485

Straight out of the box, Excel formats that first line as 1/05/2009 00:00, which is clearly wrong. The second line only contains the hour not the date, which makes it useless to use in a Pivot. The right format would be to add a date column and use a date format that Excel supports straight out (ISO-8601 is good!).

 

Then a plain old trended pages report gives you output like this:

,Date, my:homepage,, mytherpage,, my:anotherpage,, my:yetanotherpage,, my:andanotherone, 1.,"May 1, 2012",12345,8.6%,3247,7.1%,1545,3.5%,800,2.5%,700,2.6%

 

Opened in Excel, that Date column isn't recognised as such at all and you have to go through all sorts of contortions to make it work.

 

So how about a checkbox or system-wide setting that turns on "Excel-friendly CSV"? Excel exports have their own limitations, and aren't useful for non-Windows systems when you need to integrate.

1 Comment