Articles in this section

How to Update CSV and Excel Files

  • Updated
Follow

Introduction

Sometimes when updating projects with new or revised data, additional information in the revised data file contradicts conclusions about the variable type when importing the original file. There are two relatively common scenarios where this can occur: 

  • Numeric variables that become text variables
  • Text variables that become numeric

Method

Numeric variables that become text variables

If a variable contains only numeric data when it is originally imported, it will be assumed to be a numeric variable from that point onward, and any non-numeric data will automatically be converted to missing data.

If it is important to change the type of a variable, the process is:

  • Open the original CSV or Excel file in Excel.
  • Create a new column, ideally immediately to the right of the original variable.
  • Make the corrections in the new variable.
  • Import the updated data file by selecting File > Data Sets > Update.
  • Hide the original variable by ticket the "H" in the Tags column of the Variables and Questions tab, and use the new variable in its place.

Text variables that become numeric

Where a variable contains information that causes Q to interpret the variable as being text, such as commas, dollars signs or any other non-numeric data, Q will continue to interpret the data as being text, even if the underlying data is changed to text and re-imported.

You can change the variable to numeric by changing its Variable Type to Numeric in the Variables and Questions tab.

If Updating Excel file in Q doesn't add new records

If there is a blank row followed by a non-blank row or the equivalent for columns, Q will fix the data set dimensions to this configuration. The reasoning behind this is that in this circumstance we will conclude that the file contains multiple tables of data, and that we only want the first of those tables, or some sort of footer which we want to avoid. 

This means any additional rows or columns will be ignored on update.

To resolve this problem:

  1. Change the .QPack extension to .zip

  2. Open the .Q file in a good text editor

  3. Change the nRows parameter for ImportReader so it’s a higher number:

  4. Save

  5. Zip-up the .Q file and data file
  6. Change .zip to .QPack

Next

How to Read an Excel File into Q

How to Work with Excel Files in Q

How to Use Excel and CSV Files in Q

 

Related articles