Man, just typing the title of this post makes me cringe with boredom.
However, I have used this technique many times over my career, and just recently the issue came up from a JibberJobber user who imported his Contacts.
His problem was that the first name and last name were in the same column, but we want that broken out when you do the import.
Fixing this is actually really simple. In my user webinars I talk about opening a csv file in Excel, so you can see the pretty columns and rows… but for this little exercise I recommend you open it up in Notepad.
I know, I know: opening it in Notepad will make it really, really ugly. Almost unreadable. Especially files that have a lot of “columns.” Just remember, csv stands for “comma separated values,” which means that each “field” is separated by a comma. The comma tells Excel to put the next thing in a new column. For example, this csv content:
First Name, Last Name, Email Address, Phone Number
Jason, Alba, Jason@Jason.com, 801.800.8123
Will look like this when opened in Excel:
What if your file is formatted like this, instead? Notice the name is ONE column (not broken out)?
Name, Email Address, Phone Number
Jason Alba, Jason@Jason.com, 801.800.8123
John Doe, John@Doe.com, 555.555.1234
Sally Jesse, Sally@Jesse.com, 800.123.4567
Right now we aren’t parsing this for you – the best and easiest thing to do is to fix it in the file. Again, go to Notepad, open the CSV file, and then make these very simple changes:
- In the first row, which is the header row, change Name to First Name, Last Name. This will make TWO columns instead of ONE (make sure to put the comma between the column names).
- In all the other rows, simply put a comma between the first and last name.
That’s it – it is very simple. I’m guessing if I had a file with 100 records (names) I could put the comma inbetween the first and last name in about 4 or 5 minutes, or less…
This might seem like a pain, and when we redo the import we might accommodate for one name field, but for now this is really quite easy. And YOU are empowered with the knowledge to manipulate your csv files!
Now when you import it will import the name values into the right fields (first, last).
Thank you for the useful tip. This will be harder if there is a huge enough list.
If the naming is consistent i.e “first name” space “second name” then there is an easier option using MS Excel.
Step 1 – Make sure to insert a column after the name column.
Step 2 – Select the list of names
Step 3 – Click the ‘Text to Columns’ option under Data in MS-Excel. Select ‘delimited’ click next and select ‘space’ as the separater. See the preview below to make sure it is correct, then finish the task.
Another tool for working with CSV files is CSVED, a free tool available at https://csved.sjfrancke.nl/
It can easily split and join columns as described, plus a whole boatload of other features!