Have you ever imported a file into Participants and gotten “Attempting to insert data that does not have a corresponding column.” or “String or binary data would be truncated.”? This post will explain how to avoid these errors by checking and fixing your Participants file manually before importing. We will first describe the manual method using Excel and then provide a Dimensions script that can also be used to fix these issues.
MANUAL ANALYSIS AND FIX
Error “Attempting to insert data that does not have a corresponding column.”
This typically occurs when one of the text fields contains the delimiter.
If you’ve exported your data from Excel, you might have an export where the fields that contain the delimiter have been surrounded by double quotes. Excel ignores delimiters inside fields when the fields are surrounded by double quotes but Dimensions Participants doesn’t give special treatment to the double quotes. They are treated like any other character.
If you’ve got this type of file, open the file in Excel and use the Find feature to look for the delimiter. If you find the delimiter, then you have a problem and the file needs to be saved with a different delimiter. The easiest way to save a file with a different delimiter is to customize your regional options. Close Excel. Open the “Regional and Language Options” application from the Control Panel. On the “Formats” tab, choose the “Customize this format…” button. Change the “List separator” to a character that is not present in your file. Often the pipe character, |, can be used. Now that you’ve changed the delimiter, opening by double-clicking or choosing the .csv will use the new delimiter so before opening the existing file, change the extension to .txt so that the “Text Import Wizard” will be displayed. Import the text using the original delimiter. By default, Excel uses a double quote as the text qualifier so any delimiters inside fields surrounded by double quotes will be ignored. Now save the file as a .csv and the file will actually be saved with the new delimiter. This file can then be uploaded to Participants after you have changed the Participants delimiter to the new delimiter (don’t forget to click the “Apply” button).
If you’ve got a file that does not have double quotes around the fields, then the easiest way to check for delimiters in columns is to open the file in Excel using the delimiter and then check for text in columns that don’t have a header. Use the sort feature to sort on the first column that does not have a header. Then find all the rows with text in that column, either at the top or bottom. In this case, you will have to manually fix these rows to fit into the correct columns before exporting with a new delimiter.
Error “String or binary data would be truncated.”
This occurs when some of the data in a particular column is longer than the length that has been defined in Participants. Unfortunately the error does not tell you which column is too short or how long it needs to be. You can use the array formulas feature of Excel to find the maximum length of any column. Insert a new row at the top of the Excel sheet. In the function area for the first column of the first row, type the following. Replace N with the last row number. (A short cut for finding the last row and column is Ctrl+End.)
Once you’ve typed in the function, type Ctrl+Shift+Enter. This turns the formula into an array formula. Excel will surround the formula with braces and place an instance of the formula in each cell of the range. You will see the length of the longest cell as the value. You can then re-select the cell and then drag the corner to fill the entire top row with the formula. You will then have the longest value for each column.
Now you can upload the file but before importing, use the “Additional Fields” tab to increase the length of the field to at least the number reported by the formula.
SCRIPTED ANALYSIS AND FIX
You can also use this Dimensions script to analyze and fix a Participants file. The script uses the strategies discussed above to analyze and fix the file automatically if possible. The script is provided as a Dimensions script so that you can easily modify it. However, this means it is slower than it would be if it were a written in a compiled language. If you use the script regularly on large files you may want to port the script to a compiled language like C# or VB .Net or ask us here at smarter Dimensions to do that for you.
Download the script. Run it using mrStudio or mrScriptCL. The script will prompt for the full path to the participants file and the current delimiter and will then analyze the file. It will fix the file if possible and provide a report specifying the minimum length of each field. If it is not possible to fix the file manually, it will suggest a delimiter that can be used to fix the file manually.