ClearEvent allows you to export all kinds of data from your event to Comma Separated Values (.CSV) files. While CSV files are great when you need to get data from ClearEvent into some other system (accounting package, marketing automation tools, etc.), they don't store formatting information like a Excel (.xlsx) file do. Each time you export data to CSV file, you may find yourself re-applying formatting and saving as an Excel (.xslx) file to save the formatting and make the CSV file easier to read. You may also wish to apply some basic transformations or calculations.

If so, we have some Excel power user tips to help save you some time!

This article will walk you through setting up an Excel file that is formatted the way you like it, and this Excel file will get it's data from the exported CSV file from ClearEvent.

The approach below will work for any type of data you export from ClearEvent (budget, to-do's, participants, ticket orders, ticket holders, jobs, tickets, etc.)

Ok, let's get started!

Say we want to export our event budget. Normally, when you open a .CSV file that has been exported from ClearEvent in Excel, it looks like this:

Not terribly pretty is it? What would be better is if you could customize the formatting to exactly what you like, and then have it applied each time we open our CSV file. Ideally, lets say we want something like this (notice the table formatting and that some columns are center-aligned, and others are formatted as a currency):

So, how do we do this?

We can use Excel's From Text/CSV feature. We'll need to create a new Excel (.xslx) file and configure it to connect to the raw CSV file that we exported above from ClearEvent. This way, the formatting changes (and any transformations) we apply to the new Excel file will be kept. Nice right!?

There's a little bit of one-time setup involved, but once it's completed you won't need to worry about it again.

Example: Exporting Formatted Budget Data

In ClearEvent, export the budget to a .CSV file. When you export the CSV file, make sure to rename the .CSV file to "budget-raw-source.csv" and overwrite any existing file.
Open a new blank workbook in Microsoft Excel.
Select column A1.
From the Data ribbon bar, click the From Text/CSV button.

From the Import Data dialog window, find and select the budget-raw-source.csv file that you exported from ClearEvent above.

From the "budget-raw-source.csv" dialog window, if you need to transform the data (e.g. reorder the columns, remove columns, filter out records, apply field formatting, etc.) click the Transform Data button. Otherwise, if you just wish to import all data, click the Load button:

Next, you can also apply any additional formatting changes you like. For example, select each column containing a currency value and format it as Accounting Currency.

When you're done making changes, save the new Excel Workbook as an Excel (.xlsx) file named "budget-formatted.xlsx". This file will retain all formatting and can be used whenever you need to view this data or generate reports.

TIP: It's best to save the new Excel file in the same folder as the raw data source file budget-raw-source.csv. You'll need to keep these files together to avoid breaking the data connection we established between them using the From Text/CSV excel feature.

And that's it! You won't need to do that again the next time you export data from ClearEvent.

Refreshing Budget Data In budget-formatted.xlsx

At any point, you can refresh the data in the Excel file with fresh data from your event. All of the formatting and transformations you have applied will be kept. To refresh the data follow these steps.

Open the ClearEvent Event Manager App, go to the Budget section and export the latest budget. You must rename the export file to budget-raw-source.csv and overwrite the existing file.
Open the Excel (.xlsx) file budget-formatted.xlsx
Go to the Data tab.
Click Refresh All. Data from the connected source file budget-raw-source.csv will then into budget-formatted.xlsx. All formatting changes and transformations that you applied will remain.