In this guide, you will find step-by-step instructions to help you prepare your contact database efficiently. We discuss:
How to remove unnecessary columns so that your database only contains the necessary data that our system uses. For more information on the mandatory and optional fields to fill in a CSV file, click here.
How to split data into separate columns (e.g. separate first and last names).
How to search for and remove extra characters that may hinder further processing of the data (e.g. spaces, special characters).
How to save a file in CSV format to upload to edrone.
How to change the format in a column e.g. for a birthday.
How to simply highlight multiple lines/columns in a file.
By following the steps below, you will have a clear, structured data file ready to be imported into edrone.
So let's get down to business! 😊
The data contained in the test database, which is shown in the images below, is randomly generated data using the ChatGPT tool. They are not real data of physical individuals.
Note: The instructions in this article are based on Google Sheets, an easily accessible and intuitive online tool. If you are using another program, such as Microsoft Excel or LibreOffice Calc, some options may look different or work in a slightly different way. It's worth adjusting the steps to match the capabilities of your chosen software.
1. Deleting columns in a file
We will now show you how to delete a single column in a file. For more information about the mandatory and optional fields to fill in a CSV file, click here.
Open your file in Google sheet.
Select the column you want to delete:
Click the letter at the top of the column (e.g. ‘A’, ‘B’).
Delete a column:
Right-click on the selected column.
Select ‘Delete column’ in the context menu.
4. Done! The column has been deleted.
2. Splitting data (e.g. First Name and Last Name) into separate columns)
In this subsection, you will see how to simply split data into two separate columns, e.g. with the combined first and last name of your customers in the exported database file from your shop platform. Remember that the ‘raw’ file downloaded from your platform, needs to be properly formatted before uploading to the edrone system. Full instructions on how to format the file, can be found here.
Open the file in Google sheet and select the column you wish to split.
Go to the ‘Data’ tab (at the top of the screen).
In the ‘Data’ section, click the ‘Split text into columns’ button.
Click “Separator”:
Select a data separator (e.g. Space, Comma, or other character used to separate data) and click Enter to save your selection.
The data will be split into separate columns.
3. Searching for and removing extra characters in columns
We will now show you how to simply remove unnecessary spaces, extra dots or other characters.
Select a column of data.
Click “edit” or press Ctrl + H to open the ‘Find and Replace’ tool.
In the tab:
In the ‘Find and replace’ field, enter the character you wish to remove (e.g. ‘ “ for spaces or ”.’ for full stops).
Leave the ‘Replace with’ field blank.
Click ‘Replace all.’ All indicated characters will be removed.
4. Changing the format of a column (phone number or birth date)
If you want to upload information about your customers, such as their phone numbers or dates of birth, into the edrone system, then you need to remember the correct format in the column for this data. Full instructions for the file can be found here.
Changing the format of a telephone number
If you wish to format telephone numbers (e.g. remove separators or add spaces/ dashes), follow the steps below:
Select the column with the phone numbers:
Click a column letter (e.g. ‘D’) to select the entire column.
Go to the ‘Data’ tab:
If the data is in text form (e.g. with extra characters), go to the step with ‘Find and Replace’ (described below).
Use the ‘Find and Replace’ tool to delete or modify characters:
In the ‘Find’ field, enter the character you want to remove (e.g. ‘-’ or ‘(’).
In the ‘Replace with’ field, type a new character (or leave blank to delete characters).
Click ‘Replace all’
After deleting the special characters ‘-’:
Changing the format of birthdate.
If you want to change the date format (e.g. DD.MM.YYYY) to the one required by our system (YYYY-MM-DD), please follow the steps below:
Select the column with the dates you wish to change
Click on the column heading (e.g. ‘E’) to select the entire column
Go to Format > Number > Custom date and time
3. In the new window, select the format ‘YYYY-MM-DD’ from the list and click Apply.
4. The date format in the column has been changed.
5. Simple selection of multiple lines or columns in a file
In order to select multiple rows in Google sheet, you can use one of the following methods, depending on the situation 🙂
Method 1: Selecting adjacent rows (side by side)
Click the number of the first row you want to select (on the left side of the sheet, e.g. ‘2’ for the second row).
Hold down the Shift key on your keyboard.
Click the number of the last row you want to select.
Google sheet will automatically select all rows between the first and last row.
Method 2: Selecting non-adjacent rows (separate rows)
Click the number of the row you want to select first.
Hold down the Ctrl key on your keyboard.
Click the consecutive row numbers you want to select.
The rows can be spread over different parts of the sheet.
Method 3: Selecting all the rows in the worksheet
Click the rectangle in the top left corner of the sheet (it is above the number ‘1’ and next to the letter ‘A’).
This will select the entire worksheet, including all rows and columns.
Method 4: Selecting rows using the keyboard
Navigate to the first row you want to select using the arrows on your keyboard.
Hold down the Shift key and then use the down or up arrows to select subsequent rows.
These methods work for both small and large ranges of data.
6. Saving as a CSV file
Once you have organized your spreadsheet, you will need to save it as a file in .csv, .xls or .xlsx format. Only these file formats are supported by us.
If you save the file, as a CSV (our recommended format) make sure the file columns are separated by semicolons (possibly commas) and make sure the file is saved in the UTF-8 encoding system. Also, do not use special characters (e.g. dots) in the file name. Full instructions for preparing your database can be found here.
Open your file in Google sheet.
Click File → Download -> Comma Separated Values.
Done! Your file has been saved as a CSV file.
These are all our tips for basic operations in the Google sheet tool, so that you can easily and efficiently prepare a suitable file for importing your database file into edrone. Good luck!
Need more help?
If you have any further questions about the database import to edrone, please do not hesitate to contact us via chat or at hello@edrone.me