Make exports to other systems, especially Excel, painless

Avatar

Avatar

simon-rumble

Avatar

simon-rumble

simon-rumble

15-05-2012

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 (1 New)
1 Comment

Avatar

Avatar

jeampz

Avatar

jeampz

jeampz

30-07-2014

Even having the option to send a data warehouse in a .txt file would be a great help!