How To Manipulate a CSV File

How To Manipulate a CSV File 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 arent 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. Thats it it is very simple. Im 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). How To Manipulate a CSV File 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 arent 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. Thats it it is very simple. Im 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).