The 6 reason spreadsheets fail to import your data into any online platform – well this should be as easy as pie… but sometime you will get failures / errors when you initially try to import your data. These are usually caused by a few simple errors that prevent the spreadsheet from being initially loaded into the platform, not in the importing process. Its a bit like trying to post a large round parcel through a rectangular letterbox, no matter how many times you try it’s never going to fit!
In this article i’m going to go through the top 5 reasons that we have come across that prevent your data from being imported, along with some simple fixes that you can quickly do yourself.
1. Export your data as a .CSV file
‘Stuff’ that Excel hides behind your spreadsheet is the most common reason we come across when it comes to why spreadsheets fail to import – Microsoft Excel is an amazing piece of software and it can do loads of ‘stuff’, which is brilliant when you’re doing ‘stuff’. But when your a piece of software that Imports data, ll you want is a basic list, the clever ‘stuff’ that is hidden in the background of your spreadsheet (Uni-code, filters, tables, etc… ), can cause a whole heap of problems when importers try to read them. To make things worse, many of these ‘Stuff’s’ can not be seen to be fixed.
Luckily there is a quick and easy solution to fix this, simply export your data out as a .CSV file, which is the most basic format of spreadsheet, and it will remove all the ‘stuff’ in the background and make importing faster, more reliable and 90% of the time, work.
To Export your data from Ms Excel as a .CSV file, follow these steps:
Go to File > Save As. The Save As dialog box appears. In the Save as type box, choose the text file format CSV (Comma delimited). > Download
2. Column Titles
Again another really common error as to why spreadsheets fail to import we see and only takes a few seconds to fix, is not adding column titles / headers into the first row of your spreadsheet. This prevents the Importer tool from knowing which columns it needs to look at, and can even make it think the spreadsheet is blank, causing the initial import error.
Another, issue that does not prevent data from importing, but makes the whole process much more time consuming and complicated. When you exported your data from systems like HubSpot or MailChimp, they have a habit of exporting absolutely everything, including your inside leg measurement! Whist this can be nice to have, if you don’t need it imported into your new CRM, don’t. Start off by making a copy of the spreadsheet, and delete everything that is not needed. This will make the process much faster and simpler, when I coms to mapping your data.
3. Merged Cells
Another problem why spreadsheets fail to import is created when your are using Excel or similar programs, is when cells are merged together, an importer which needs the data in a simple format, does not know where to put the the data when this happens.
As you can see in the graphic Rows 2&3 have been merged in Column 2, when the Importer looks at this it does not know whether to put ‘Green’ into Row 2, Row 3, both or neither of the rows.
Fixing these can be a bit of a time consuming process, but start by exporting the spreadsheet into a .CSV format (remember this fixes most evils), as this will remove all the merged fields and leave you with some blanks that are easier to spot and fix with a quick scan.
4. Multiple sheets in your spreadsheet
It’s amazing how often we come across this problem that mean spreadsheets fail to import, even with users who have been working with data for years. The reason is that it is so easy to overlook. Like in the merged cell problem, when an importer looks at a spreadsheet with multiple ‘Sheets’, it does not know which one it should be looking at, and what to do with the others. So it often throughs up and error as soon as you try to load it.
There are 3 options for fixing this problem:
- combine all the data in to one sheet,
- save the individual sheets as differnt spreadsheets
- Or … you guessed it, export it as a .CSV, when you save it it will warn you that there are multiple sheets and give you the option to save them individually – Simples!!!
5. Wrongly formatted or missing emails addresses
When importing email addresses, the importer looks for a set format which is the basic structure of any email address – text@text.text. therefore, if the email does not match this format, it will be kicked out as an invalid email address. Luckily popcorn CRM’s importer will not block the data from importing, if they are incorrectly formatted. Instead it will import everything that it can produce a report for you (post import) with all the incorrectly formatted emails in it with a description so that you can fix them and re-import, other systems are not quite as forgiving, so its one to keep an eye on.
Some common examples of wrongly formats email addresses are:
- Mailto:text@text.text – this typically happens when you are copying an email from your mail client (Outlook etc…) as it adds the mailto: at the beginning.
- text@text.text – when 2 email addresses have been added into the same cell
text@text.text - text @text.text – random spaces in the address
- text@text. – missing domain names
- www.text@text.text – a www.added at the beginning of the email address
Popcorn CRM needs every single one of the contacts you are importing to have an email address associated with them, as we us this a the unique identifier, to avoid duplication of contacts, with out this, the contact will be added to the rejection report. If you do not have an email address, you can just add a holding email i.e. 1@your.domain.
5. Line returns in cells
Having extra line returns inside a cell is the of the most frustrating of all that spreadsheets fail to import as it can be hard to spot. The reason it throws up an error as like before, the importer is looking for a piece of information, that fits inside a box, it then wants to copy it into another box to complete it’s job. Unfortunately, when there are 2 or more lines inside to spreadsheet cell, this won’t fit into the box inside the software. So the importer does not know what to do with and it throws up an error.
In the example above the words Green and Grey have been pasted in a single cell. This is often seen when people are pasting addresses into the original spreadsheet.
But fear not there is a really simple fix for this … Simply follow these simple steps to ‘Find and Replace’ them with another character, it suggest something like ‘-‘, so that it is easy to spot when you are reviewing the copy later.
To do this in Excel:
- Select the cells that you want to search.
- On the keyboard, press Ctrl + F to open the Find and Replace dialog box, with the Find tab active.
- Click in the Find What box.
- On the keyboard, press Ctrl + J to enter the line break character.
- Type the character you want to replace it with into the Replace box.
- The click find and replace all
Unfortunately .CSV does not fix this one…
6. Data mixed up
And finally the hardest one to fix and usually one of the easies to spot, is when the data is mixed up, I.e. email addresses are in several columns, some of which are labeled as being for other data.
This usually happens when a company has been using a spreadsheet as there CRM and there have been a few members the team using it. Unfortunately, this is a manual and lets face it a tedious task to sort all the data into the correct positions.
A quick tip which can speed the process up, is to use the filters at the top of the column to sort them in to types / alphabetically, this allows you to group and copy blocks or data, rather than individual contacts.
Conclusion
So, hopefully this guide helped you to quickly and easily fix most things that prevent you from importing your data into popcorn CRM or any other other platform. When you know what you are looking for fixing a spreadsheet can become quite a fun challenge that is really rewarding to do (I know i’m a geek). But once you have your data in good shape, you will never have to do it again (until someone else breaks it 🙂 )
Import service
Hopefully this will have fixed your importing problem, but on the off chance that it’s hasn’t or you just don’t have the desire to to this. We have a service that popcorn CRM users can take advantage of, where we will clean up your spreadsheets and get them ready for import for you.
