This article provides some tips on creating and formatting the files you upload to Analytics. These files must be formatted correctly or upload errors may occur.In this article:
- The data files must be uploaded in CSV (comma separated values) format.
- The CSV file's header must exactly match the target Data Set's schema. The order of the columns must be the same as that specified in the schema, and all column names must be use the internal
ga.dimension or metric name, not the display name. (E.g.,
- The uploaded data must be correctly formatted. See below for details.
Create the upload file
While you are free to use any method that works for you, there are two simple ways to create an upload file: using a text editor or using a spreadsheet application. No matter how you create the file, you'll need the Data Set schema in order to create the upload file header.
Get the schema
You can copy or download a Data Set's schema by editing the Data Set and clicking the Get schema button.How to get the Data Set schema:
- Sign in to Google Analytics.
- Click Admin, and navigate to the property to which you want to upload data.
- In the PROPERTY column, select the property that contains the Data Set that will hold your data.
- Click Data Import.
- Click the target Data Set's Name link.
- Click the Get schema button (near the bottom of the page). This will display the dialog shown below.
Use a text editorTo create your upload file using a text editor, simply copy and paste the schema into the first line of a new file. You can then add your data, one record per row, under this header.
Be sure your editor creates plain text files. Rich text editors, such as Microsoft Word, should not be used unless you explicitly save your files as plain text (.txt). Rich text format contains additional formatting and control characters that will likely cause import errors.
Use a spreadsheet
To use a spreadsheet for your upload data, follow the steps above to get the Data Set schema. Then click the Download schema template button. This will place the schema headers in your Downloads directory. You can then open the schema in your favorite spreadsheet. Use the spreadsheet's Import function to bring in data you want to upload. When your file is ready for upload, use the Save As or Export function of your spreadsheet program and choose CSV as the output format.
Format the upload data
Data Import expects each line in your upload file to contain information about a single hit. The individual dimensions and metrics that make up that hit are separated from each other by commas. Each line in the file ends with a line break. If there are commas within the actual data you want to upload, that data must be surrounded by quotes. Probably the most common cause of content errors is improper use of commas, quote marks and line break characters in the uploaded data, so understanding how Data Import handles these can save you trouble down the road.
By definition, a CSV file uses commas to begin a new cell. In other words, when Data Import sees an unescaped comma, it assumes you are starting a new column entry. The example below shows properly formatted data:
abc123,29.99,Blue,XXL def345,100,Red,M ghi678,1035.20,Green,S
In this case, Data Import sees four columns of data:
But what happens if we add an extra comma to the data, for example, by formatting the numeric column as currency (by adding a comma to mark the thousands position, like this?
Now, due to the extra comma, when you try to upload this file Data Import sees five columns, producing an upload table that looks like this:
This will produce an error, such as the following:
“Error: column count does not match"
Double quote marks can be used to "escape" a cell's value. Typically, you would surround a cell's value with quotes if the value contains a comma. Otherwise, Data Import will interpret the comma as the start of a new cell (see above).
The value "1,035.20" will be treated as a single cell (however, Data Import will see it as a string, not a currency value).
When escaping a cell with quotes, the number of quotes must be balanced. If there is an odd number of quote marks in a given row, the import will fail with a message such as:
“Error: Invalid CSV format at row x.”
Line breaks within a CSV file are used to indicate the start of a new row. Data Import does not support line breaks within cells even if these are escaped by quotes. Extra line breaks within a row will cause an error in the number of columns in either that row or one of the following rows, producing an error message such as:
"Error: invalid CSV row format"