SiteSpect has a useful Data Export feature that allows you to export any of the data that SiteSpect collects. You can use the exported data in a variety of third-party applications to further analyze site visits. Some additional information may make it easier for you to use this data in Microsoft Excel.
What Happens in Excel?
Make an export file, following the instructions in Data Export. Then open the resulting file using Excel.
In some cases, for example, if one of the cells in the file contains data for more than one entry, the entries may look like one entry since they are enclosed in quotes.
In the image below, you can see two entries that are surrounded by quotes that contain only numbers, commas, and decimal points. For entries such as those within the red rectangles in the following image, you want to signify that you have multiple entries for this column, but by default, Excel transforms the multiple entries in the column into a single number.
When Excel imports such data it uses automatic formatting, which does not know that there is more than one number within the quotes. It may turn such items into a very large number, such as the one shown in the following image. In cell R8390, Excel has transformed what should be more than one number, i.e., the data within the quotes in the red rectangle on the right above, into one very large number.
How to Solve This
There is a way to properly import data exported by SiteSpect while maintaining the integrity of the data. Excel has a built-in utility that allows you to tell it how to treat column data in order to preserve data integrity.
To import SiteSpect's data into Excel:
- Export data from SiteSpect. When you perform the export, use the following options. Refer to the topic, Exporting Data for details on how to do this.
- Text File: Comma Separated Values (CSV)
- Double-quote (") Field Values
- LF (Line Feed)
- Once you are done with the export, do not open the CSV file.
- Launch Excel and create a new empty spreadsheet.
- Select the Data ribbon and in the Get External Data section of that ribbon, click From Text.
- Navigate to the CSV file and click Import. Excel launches its Text Import Wizard, which allows you to tell Excel what type of data you are exporting. It's this part of the process that will preserve the integrity of your data.
-
In the Original Data Type section of Step 1 of the wizard, select Delimited and click Next.
- Next, specify how the data is delimited. Select Comma and make sure all the other boxes in the Delimiters section of the page are blank. In the Text Qualifier drop-down, select double-quotes ("). Click Next.
- Scroll through the columns of data. For each column that Excel may possibly format incorrectly, select the column and then click the Text button, as in the following image.
- Click Finish when you are done.