How to set up a dataset from a SQL source
Generally, CSVs are a poor file format for survey data, because they don't contain any metadata (which includes subtle but important information about the data itself). Metadata generally includes information such as variable labels, value labels, whether the variables are sub-variables of a larger variable structure (such as a Categorical Array), and other related information. It's the main reason why an SPSS (.sav) or a SSS (.sss) is a far superior file format, or even data pulled directly from a collection platform that Crunch supports (e.g., Decipher or SurveyMonkey).
Unfortunately, it's quite common to work with an otherwise unhelpful CSV file (because there's no metadata) in order to extract survey data from a SQL source of some sort. The CSV file in these situations renders a list of numbers that are all but impossible to interpret. That's where Crunch comes in.
The following describes the three methods on how to import various datasets into Crunch when working with a CSV file, with particular emphasis on the recommended third option.
Option 1: Importing the raw CSV file using the web app, and then using the web app to organize it
- This works well in many basic scenarios, but may involve extra work entering information in the web app. It also might be unclear what variable pertains to what in your questionnaire.
- For a CSV that has no metadata at all, Crunch instead recommends using R so you can at least copy/tweak/apply to the new scenario.
Option 2: Importing the raw CSV file into Crunch, and then using R to clean it up
You must use R and an R framework (such as R Studio) to describe and organize your variables. See the full documentation on Crunch R, as well as the various articles within this this Help Center that address specific tasks using R.
Option 3: Importing the raw CSV file using R, along with a metadata document
- Since the metadata document fully describes everything about the raw data, it's prepared and ready when it comes into Crunch. Little to no further tweaking is required.
- The metadata document is written in JSON, and is imported into Crunch at the same time as the data.
- At the time this article was written, the web-based importer of data doesn't allow you to do this (it only allows to bring in a CSV file on its own). When that feature is enabled in the web-based importer, you'll no longer to write R code (and instead just need to have the JSON description document ready to go).
- You can find the full documentation here.
Uploading a CSV file along with a metadata description
In the following example, there are two files:
library(crunch) json <- jsonlite::fromJSON("https://raw.githubusercontent.com/Crunch-io/rcrunch/master/inst/example-datasets/pets.json", simplifyVector = FALSE) ds <- createWithMetadataAndFile(json, "https://raw.githubusercontent.com/Crunch-io/rcrunch/master/inst/example-datasets/pets.csv")