Many data migrations are handled in Excel. When migrating data you often find yourself “massaging” the data a bit. To do so, I often use Excel. For example, let’s look at taking a first and last name and then creating a short name that has a firstname.lastname convention and a full name that has a firstname (space) lastname convention.
This can be done with formulas pretty quickly. First, let’s place our firstname into column A and our lastname data into column B, making sure that the first and last names for the rows match up (usually validating the first row and last row will be good enough). To start a formula in Excel, click in a column and then type the equals sign (=). Cells can be called by simply typing the column then the row (e.g. A1 is the top left field in Excel. If you type an ampersand then it will merge the cell with something, such as a delimiter or another cell (or in our use, both). So using =A1&B1 would simply make firstname.lastname (assuming that is our header).
You can also quote characters with double quotes and insert static text into part of a cell. So next, let’s create our firstname.lastname column. Click into cell C1 and type:
=A1&”.”&B1
You should then see the merged output in column C1. If you have hundreds or thousands of rows you’ll want to insert the formula into each, with the row updated to reflect each. You can copy/paste the formula and Excel is smart enough to change the rows, or you can click on the lower right corner of the C1 cell (you will see cross hairs if you are hovering over the correct location to click) and then drag down until you have highlighted the last row of column C, which should highlight all of column C that contains data, releasing the mouse to merge your formula per row.
Now that we have a shortname, let’s build our full name. Click into cell D1 and type:
=A1&" "&B1
You should then see the merged output in column D1. Click on the lower right corner of the D1 cell (you will see cross hairs if you are hovering over the correct location to click) and drag down until you have highlighted the last row of column D, which should highlight all of column D that contains data. Then release the mouse and you should see the merged content displayed correctly for all of the cells. Save the final file as a csv file again and you’re done!