In this article
The Excel/csv data files are often used for reporting external data such as media investments, social media data, sales figures. These data files can be imported to Forsta Visualizations. Regular survey data can also be imported by using the new Excel format but it requires more manual work compared to the .sav for handling the meta data labels. A project can have a mix of data sources so survey data can be imported in SPSS. SAV format and external data in several different Excel files. Excel/CSV is a very wide format and data can be stored in many ways. The Excel/csv file you want to import to Forsta Visualizations need to follow the following format guidelines:
Only one sheet in the data file will be considered during the import
Variables should be placed in columns and cases (respondents) in rows.
The variables (each column) should have a code/ identification name.
The standard variables (Respondent id, Response date and Weight) are, either be added to the data in our Meta Data Transformation, or already exist in the file as columns
If each case has a specific date (response date) it should be included in the file as a date, it cannot be extracted from a week number etc. Forsta Visualizations accepts specific date type or can convert a string into a date.
The Meta data transformation service can be used during the data import which means that these variables can be missing in the data file and added manually during the import.
Note: older excel versions using xls are not supported. You have to use a saved version containing the xlsx extension.
All variable types can be used when using Excel/CSV data but all variables will either be Numeric or String automatically. If the file contains categorical questions the Meta data transformation service must be used to recode Numeric/String variables into Categorical variables in the first data import.
How will Forsta Visualizations know what to use for Variable Code, Variable Text and Case Data?
By default, Forsta Visualizations will take whatever is in your first row and use that for both Variable Code and Variable Text, and your “case data” will start on your second row. This is something you can change. You can also select what sheet to import data from. (You can only import data from 1 sheet at the time. Each sheet is treated as its own data file. If you want to modify what row to use as code, text or Case data, make sure that:
- all variable codes must be placed in the same row for all columns.
- all variable text must also be in the same row for all columns.
- all case data needs to start in the same row.
Note: You must have something as your Variable Code cell for all columns that has something in it. This way Forsta Visualizations recognizes what code to either import or match the data to.
How does Forsta Visualizations create variables and answers?
When Forsta Visualizations reads an Excel file, the system will go read each cell of the file, which can take some time if you have a large file. It will look at all the different answers in each column and create answer blocks based on them. It’s important to understand the logic behind Answer Blocks whilst importing Excel files. If you for example only have answers in each column represented by numbers starting from 1-n, all variables will be sharing answer blocks within Forsta Visualizations. Due to this, it’s better to import the actual answer text instead of ID’s. If you import your data files with answer text, Forsta Visualizations will match answer to answer. This means that you must be consistence with the spelling. Any spelling mistakes or other changes will create a new answer alternative. Of course, if you imported the first file with answer text, you could continue your imports with ID’s. If there are only ID’s (numbers) in your file, Forsta Visualizations will match your answers with the answer ID’s within the system.
Step by step:
Go to Import data icon under Data section and select “Import” tab.
In the dropdown menu select the file type Excel/CSV and Browse to search and select the file.
Under BATCH TYPE, select to Import Case data or Import Meta data&Case data. You will select this option if you are loading data for the first time, or if you are loading altered metadata. If you are loading a second batch of data and there have been no changes to your metadata, you can load case data only.
Under Excel/CSV file settings choose Sheet name, Variable code row No, Variable text, and Data start row No, where variables and data can be found.
-
If you select to Import Meta data & Case data or create a new metadata transformation, the options are identical as when you are using .sav file.
Note: in case you want to import CSV files, Forsta Visualizations will show you some different specific settings. Instead of choosing the sheet and row numbers, it will request which decimal separator and csv delimiter you have in your file
When uploading a multiple choice question, use a column per answer and load these including the labels in the cells, as in the picture below:
Make sure that you have "Code open ended question to categorical" activated during the upload and in the Meta Data transformation you can set the type to Single choice and combine these columns into a multiple (read this article for more information). However, new answers belonging to this multiple which might appear in new uploads need to be manually added when loading these.
How to add Mandatory variables?
For all data files that should be imported to Forsta Visualizations, each row/respondent needs answers on three variables. Respondent ID, Respondent Date and Weight. These can be variables in your date file, or variables that you let Forsta Visualizations create for you.
Respondent ID
This variable MUST be an open numeric, including only digits. If you add it to your data through the MDT, Forsta Visualizations will start at the highest imported RespID+1 to add these ID’s for each row. If you already have an Open Numeric you want to use, right click on that variable, and select “Set variable as Respondent ID”.
Response Date
If you let the system create Response date for your file, each row in it will have the same date. (Either a static date selected from a calendar, or “Todays date” for the import.) This can be done by right click in your MDT and select “Create Response Date”. On the tab “Create new” you select a static date or “Todays date” by checking the box “Use import date”.
You can also use a variable for this.
Note: You can ONLY use an Open Ended or a “True excel date” variable to import that variable as a Respondent Date in Forsta Visualizations. This means that you will NOT be able to import a variable that ONLY has digits in it, even if you have marked that column in Excel as a Text. The inputs in there are still just digits and will be treated as an Open Numeric in the MDT.
Response date as string: The input in this variable MUST include some characters that isn’t a digit. For example -, /, : or other characters. It could look like this: 2020-08-07 18:37. Once you see that this is an Open Ended in Forsta Visualizations (you can’t change an open numeric to an Open Ended within the MDT, because the original data is still an open numeric), you right click anywhere in the MDT, select to create a Respondent Date. Go to “Select existing”, select your Open Ended variable, check the “Custom format” box and use the transformation characters to transform your Open Ended into a Date. In the example above, you would use yyyy-MM-dd HH:mm. The exact combinations of these characters are decided by the format of your Open Ended variable.
Response date as true Date in Excel: “True excel dates” are also treated as open numeric in our MDT, but since it’s a function used in Excel (calculating number of days from Dec 30th 1899), you don’t have to do any transformation on it. You just have to change your Open Numeric (True excel date) into a Date by changing the type of the variable in your MDT. Once you have done that, you can just right click on that variable and “Set variable as Response Date”.
Weight
This variable MUST be open numeric, including only digits. If you add it to your data thru the MDT, Forsta Visualizations will add the weight 1 to all rows. This means that all your respondents will weigh the same and will show the exact same results as Unweighted in all our data objects. If you already have an Open Numeric you want to use, right click on that variable, and select “Set variable as Weight”.