Creating Random Unique ID’s

In this example we show you how to mange creating Unique ID’s for your sample and show you how you can use them. This example assumes that you already have a text delimited file that you can use for your sample. For the purpose of this example we will be using a sample file that has the following structure.

ID;Queue;Active;Serial;Test;Email;Fname;Surname
;;;;;John@info.com;John;Smith
;;;;;Jane@info.com;Jane;Jones
;;;;;Jim@info.com;Jim;Bowlen

You will notice that the first row of the file holds the field names and then the next 3 hold the actual data. The first 5 columns hold the dimensions required sample fields which at this stage are empty the rest hold the information about each respondent that we want to send an email to. The next step we need to take is to open up this text file in Excel. We will use Excel to create the ID’s for each record and fill in the blank cells. So where do we start. Open up Excel and click file open, change the file type to

Open Text File

The next thing you should see is the following screen.

Import Wizard

Click Next and specify the delimiter to be a ;

Import Wizard - Set the delimiter

Click Finish and you should end up with your sample in Excel. I have made some format changes to the sheet, just so that you can see the headers better.

Import restult

Next we need to enter a formula in the ID column. Place the cursor in this column and type

=LEFT(H2,2) & LEFT(G2,2) & ROW()

So what have we just done, well basically we have taken the first 2 characters from the surname and concatenated them onto the first two characters if the first name and then added the row number. This will then give us a unique identifier for each record. Next we need to populate the remaining blank fields. Enter the following into the remaining blank columns on row two.

Enter ID's

Once you have entered the rest of the values highlight the cells shown in the image and drag the box down to fill in the remaining cells.

Enter queue information

Click the little square box, keep the mouse button down, and drag it down to E4.

Populate the rest of the rows

You should then end up with the following. Every record should now have an unique ID that mrInterview can use.

Final Sample

The next thing you would normally do is to load this sample file up into dimensions. Now as you know you cannot upload Excel sample files into dimensions. What you have do is to make them a semicolon delimited file. I know that you can change the delimiter to something else, but I have found that it is best to keep it to semicolon. To create the file we will need to think about the data it self first. Does it have any semicolons or commas in it already ? If it does replace them with something else. I normally do a search and replace in Excel to do this. Once we are sure the Excel file has no semicolons & commas in it we can create the file to upload. Click File and Save As in Excel and save the files a CSV

Save as csv file

You will see the following message pop up before Excel saves the file. The reason it does this is because a CSV file is not capable of storing all the functionality that excel can provide so click the “Yes” button.

Don't save formating

Now you can open up the CSV file into something like notepad and replace all the commas with semicolons. Please note if you use something like Notepad on a large file this step will take a long time. Once the file is edited save the file as a text file and load it up into dimension in the normal way. The last thing that you may want to do is to create the links that are to me used to send to each respondent. DimensionNet offers an HTML / Plain text email facility and is good so you can use that but sometimes you may want to use something like Word or another mail merge program. To create the survey links for each record you could do the following. Open up the excel version of the sample and add an extra column into the excel sheet called URL. In this example i have put it infront of the ID column, but you can do this anywhere you like.

Add the url field

Enter the following into row two of cell A.

="http://mySite/mrIWeb/mrIWeb.dll?I.Project=A&ID=I" & B2

Don’t forget to change the actual URL ( in bold ) to the URL of your survey. Once done you will end up with the following

Make the URL

You are now ready to fill in the other cells as you did before with the copy function we used previously for the ID etc

Leave a Comment

%d bloggers like this: