Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Make exports to other systems, especially Excel, painless

Avatar

Avatar
Boost 5
Level 2
simon-rumble
Level 2

Likes

23 likes

Total Posts

5 posts

Correct reply

0 solutions
Top badges earned
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Boost 5
Level 2
simon-rumble
Level 2

Likes

23 likes

Total Posts

5 posts

Correct reply

0 solutions
Top badges earned
Boost 5
Boost 3
Boost 10
Boost 1
View profile
simon-rumble
Level 2

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

Avatar

Avatar
Applaud 5
Level 1
jeampz
Level 1

Likes

0 likes

Total Posts

15 posts

Correct reply

0 solutions
Top badges earned
Applaud 5
View profile

Avatar
Applaud 5
Level 1
jeampz
Level 1

Likes

0 likes

Total Posts

15 posts

Correct reply

0 solutions
Top badges earned
Applaud 5
View profile
jeampz
Level 1

30-07-2014

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