Importing a spreadsheet
Excel, CSV and PDF into any list screen — the template that maps itself, how the columns are matched, what is checked, what happens when the same file comes back, and why it posts through the API.
Every list screen in the platform accepts an import. It is not a separate migration tool for a handful of tables; it is a drawer on the screen you are already looking at, and it loads into that screen’s records.
- Open the screen the records belong on and choose Import.
- Drop in an .xlsx, .xls, .csv or .pdf file. Legacy .xls files are read directly — there is no need to re-save them as anything.
- Check the column mapping. Columns are matched to fields by name first, and anything unmatched is offered as a dropdown so you can point it at the right field yourself.
- Read the preview. It shows what will be created, row by row, with the rows that will be skipped listed first and the reason beside each. If rows match records already here, choose what happens to them.
- Import. Rows are posted through the same API the screens use, and the last step says how many were created, updated or merged, how many were left alone, and why any were not imported.
- Every row lands in the company selected in the top bar when the import was started (and in the unit picked beside it), never in your home company. If you cannot work in that company, the rows are refused rather than made somewhere else.
A template that maps itself
Starting from nothing? Download a template, either from the import drawer or from the list’s own toolbar. It is a CSV file with one row: this screen’s own column headings, nothing to delete. Fill it in and bring it back, and every column maps itself; the import goes straight on to the preview. Under the button the drawer lists what every row needs.
- “Every row needs” counts the fields the screen’s form marks as required, and nothing the platform fills in itself, such as the business unit taken from where you work.
- The additional fields your company has given a name go in the template too. The unnamed ones are not in the file, but can still be chosen when you map the columns, under Additional fields.
- A screen whose records only a process can make (a posting, a scan) does not offer an import at all, rather than refusing every row of every file.
Numbers the system gives
Many screens number their own records from a number range: a candidate gets its candidate number, a requisition its requisition number. On such a screen a column in the file that holds those numbers is set aside, not imported, and the mapping says why: the screen gives each record its own number from that range. It is never put on the nearest field that will take it. To keep the numbers in your file, an administrator can set that number range to allow a number to be given.
What a reference is looked up in
A field that points at another record says in the mapping what it is looked up in — "Employee id → employees", for example — so you can see that a column of candidate numbers does not belong there. A reference with no code of its own is found through what owns it (a bill of material by its item), and one kept by name is looked up by name.
Importing the same file again
A file uploaded a second time used to make every record a second time. Now each row is matched to a record that is already here, and you choose what happens to a match.
- If this very file was imported into this screen before, the drawer says so as soon as it is read: when, how many records it created and who imported it.
- Rows are matched on the record’s own number when the file carries it; otherwise on columns the screen keeps unique; otherwise on an email, a code or a name; and for a person with no number, on first and last name. Match on, in the preview, shows which and lets you choose another column — or nothing, which makes every row new.
| Choice | What happens to a row that matches |
|---|---|
| Add new only (the default) | It is left alone. Only rows that match nothing are created, so the same file twice makes nothing twice. |
| Update matches | The fields this file maps overwrite the record’s. Fields the file does not map are left as they are. |
| Merge | Only the fields that are empty on the record are filled in. Nothing already there is changed. |
| Add every row as a new record | Every row is created, whether it matches or not — what the import did before. |
The preview counts them before anything happens: so many new, updated, merged, and already here, left alone. Each row is marked with what it will become.
An update or a merge is a change to the record made as you, through the screen’s own save. A posted, closed or locked record, or one your role may not change, is refused with the reason and reported at the last step. It is never changed behind the rules’ back.
A file of documents with lines — rows grouped into documents — is always created as new; the choice is offered for single-row records. A scheduled import from a connected drive carries the choice made when it was scheduled.
What the preview checks
Every value is judged the way the screen will judge it: a number has to be a number, a yes/no field has to say yes or no, a field with a fixed list has to hold one of its values, a reference has to name a record that exists, and a code the company already has — or that the file carries twice — is flagged. Then every row that passes is tried against the screen itself and rolled back, so a rule the company added or a check on two fields together is caught as well. A preview with nothing flagged is an import with nothing refused.
What can be put right without guessing is put right, and the preview shows the corrected value: “Wrap” becomes wrap and “Corner Board” becomes corner_board where the list holds them, “Yes” becomes yes, “$1,250.00” and “30%” become numbers, and a unit or an item written by its code (EA, ITM-0042) becomes that record. A heading such as UOM, Item, Active, Recyclable or Recycled Content % is matched to its field without being mapped by hand.
A value that cannot be put right is named with what the field accepts — for example “material type "Carton" is not one a packaging material accepts (dunnage, wrap, strapping, …)”. Correct the file, or leave the row out: Import creates the rows that are ready and skips the rest, and the import’s run keeps each skipped row with its line number and reason. Asked why an import failed, Ask reads that run and quotes the rows.
Why it goes through the API
The import does not write to the tables. It calls the same endpoints the console calls, which means every rule that protects a record typed by hand also protects a record that arrived in a spreadsheet: a journal must balance, a duplicate supplier invoice is refused, a lot-controlled item demands its lot number, a closed period rejects the posting, and the audit log records it.
This is slower than a bulk insert, and deliberately so. An import that bypassed the rules would be a way of getting data into the platform that the platform cannot stand behind.
PDFs
A PDF is read for tables — a supplier statement, a bank listing, a printed report. What is found is shown for confirmation before anything is created, because table extraction from a PDF is inference and inference should be looked at.
Grouped documents
Where a document has a header and lines, the spreadsheet carries one row per line and a column that says which document the row belongs to. Rows sharing that value become one document with several lines, not several documents.
Leads from Ceipal or another applicant-tracking system
An applicant-tracking system such as Ceipal exports its leads as two files: the leads themselves, each one a company (website, industry, revenue, SIC and NAICS codes, number of employees and locations, fiscal year end, address, owner), and the contacts at those companies (first and last name, email, mobile, office number and extension, title, address). Both import as they are, with every column matched: "Lead Name" is the company on CRM → Leads, and on CRM → Lead Contacts it names the lead each person belongs to.
- Import the leads file on CRM → Leads first.
- Then import the contacts file on CRM → Lead Contacts. Each contact is attached to the lead of the same name in your company.
- A contact whose lead is not there yet is refused with the lead’s name ("No lead is called …"), so importing the files the wrong way round tells you so instead of leaving people attached to nothing.
A lead keeps its contact’s first and last name apart, as an applicant-tracking system does, and the full name is made from them. A file with a single "Contact Name" column is split at the last space. A lead need not name anybody: a company with no contact yet is a lead too. When a lead is converted to an account, its contact and everybody under Lead Contacts become the account’s contacts.
From a connected drive, and on a schedule
The file does not have to come off your machine. Any list screen can import from a connected drive, and the same import can be set to run in the background or on a schedule — which is how a file that a bank or a supplier drops into a folder every morning becomes records without anybody opening it. See Connected drives.
One column holding the whole name
Applicants, candidates, contacts and other people are kept with a first name and a last name, and both are needed. A file with a single name column — Applicant_Name, Full name, Student name — can still be imported: the column is matched to Full name (split into first and last name), the last word becomes the last name and the words before it the first name. "Tanvi Patil" becomes Tanvi and Patil. The preview shows the split for every row before anything is saved, and a file that has its own first-name and last-name columns keeps them.
Columns that are your company’s own fields — NEET Score, Preferred Course — are matched by their name or label once the fields exist under Platform → Customization → Custom Fields for that screen. Add the fields first, then import.
Since 20 October a delimited text file (.txt, .tsv — comma, tab, semicolon or pipe; UTF-8, ANSI or Unicode) imports like a CSV, a Word document is refused as not a data file, and a file the inspection finds dangerous is refused. See Uploading Word and text documents, and importing delimited text files.