Importing and Exporting Data to database-run applications such as Otto and CRM-systems
Here is how you prepare your data for upload to preserve special encodings such as É, Ü, Å, Ä, Ö best.
The reason this is not so straightforward is that character-encodings has evolved from using only 8 bits of data (allowing for only 256 different characters) to using more so more of the characters used in different languages can be rendered correctly.
The normal character-encoding that SHOULD be used in just about all cases is called UTF-8 ("without BOM" - BOM stands for Byte Order Mark).
Since earlier versions of Excel doesn’t handle this, this is a way of making sure the data is correct.
Import: Importing files to your system
For both PC and Mac:
1) In excel, you can put the columns in the order you want, and check your data nicely.
2) Select all your data, hit Ctrl+F to search for semi-colons (;) and replace them with e.g. commas (,) to make sure you don’t already have extra semi-colons.
Now you have to do things a little differently for PC and Mac:
3) Save the excel-list in text-format, preferably in semi-colon (;) separated format (csv)
(in e.g. Excel 2010 and later you should be able to choose UTF-8 without BOM (or just UTF-8) as character-encoding directly)
4) Open the file in an advanced text editor such as notepad++ (http://notepad-plus-plus.org/ )
5) If you needed to save in .txt, with tabs between entries or with commas (,) instead of semi-colons (;), you must now search and replace this with semi-colons (;). (Hit Ctrl+F to do the search and replace).
6) In the menu-bar, click “Encoding”, and if it’s not “Encoded in UTF-8 without BOM”, then hit “Convert to UTF-8 (without BOM).
7) Now, save the file (and rename it from .txt to .csv if that’s not already the file-ending), and it’s ready to upload.
3) Save the excel-list in text-format, preferably in semi-colon (;) separated format (csv) but in e.g. Excel 2011 for Mac you need to use UTF-16
4) Open the file in an advanced text editor such as text wrangler (http://www.barebones.com/products/textwrangler/download.html )
5) Now you need to hit Command-F to search and replace the TABs to semi-colons (;). You can select just the space in one TAB, hit Command-C to copy it, and Command-V to paste it into the search-bar.
6) When you save, you need to:
- Make sure the encoding is UTF-8 without BOM
- That linefeeds is in UNIX-style is used only (LF, not CRLF)
- Rename the extension from .txt to .csv if that’s not already in the file-ending.
7) Now, save the file and it’s ready to upload.
To import into Otto specifically:
8) It’s important that you do not have header-endings, and that you check the template format and make sure your data are put in the exact right columns before you upload.
9) Select which sequence the contacts should be added to.
To import into any other CRM:
8) Go to import, select the file and you may need to hit:
“Import file properties” and change:
- “Fields Qualified By” from double quotation marks (“) to “none”.
- “Separator” should be changed to semi-colon (;)
9) Map the fields and save the mapping if you want to upload like this again.
Export: Analyzing and Modifying exported data from database run-systems like Otto:
1) Your exported data is generally csv-formatted, with fields separated with separators like semi-colons (;)
2) In order to view these files in e.g. Excel, you may need to do the following after opening them:
- Hit the “A” (to select the whole first column)
- Go to the Menu-item “Data”
- Hit “Text to Columns”
- Choose that the file-type is “Delimited”
- Then choose what delimits your data, most often: semi-colons (;), TABs or commas(,), and click finished.
3) Voilà, you now have your data presented as a “normal” spread-sheet.