loader

Prepare Your Data for Import

Excel Tips and Tricks - Excel Functions and Formulas

 

General information

We’re constantly improving the import of data. The import of data is possible with the provided Pensaki XLS Import Templates only. You’ll be able to download these templates during the order process. Please make sure to always use the latest version for each product. The import templates vary depending on the product.

The current release of the Pensaki® app processes subset characters of Latin1/ISO 8859-1. With this, the app covers the major European languages (German, English, French, and Italian) including selected special characters (e.g. #;€,:@&,%“!?=()/-). Unauthorized characters will be marked in RED after the import.

Data import checklist:

Basic knowledge in working with spreadsheet software like Microsoft© Excel© is mandatory for the preparation of your data. Actually it's fairly simple to do it, if you have a look at the information we provide you on this page. The responsability for the correct preparation of the data exclusively resides with the customer, at no point in time Pensaki will be able to correct the data you have provided.

 

Please keep in mind that the robots will write EXACTLY what you provide, i.e. we will not review, proof read and correct your content.

 

About the required Pensaki import templates (.xls):

  • ONLY the import template provided by Pensaki will be accepted
  • Please read the information in the 1. tab of the XLS file and
  • Please DO NOT remove the 1. work sheet.
  • →The XLS import file’s name cannot contain any fancy special characters

About the data within the import templates (.xls):

  • →The data records in the file can not contain unauthorized special characters like º ‡ ¶ ¿® ©ªº∆∑¥å.
  • →The numer of allowed characters per cell can not be exceeded. The Pensaki app automatically cuts-off cells with excess length.
  • →Data has to be NO FORMAT (values only) and does not contain any non-printable characters.
  • →Please try to refrain from words with consecutive capital letters, this never looks it’s best when written manually.

 

 

 

Import file structure(s)

This is how the standard import file is structured. The maximum number of characters per cell can be found in brackets.

 

Column A (40) Column B (40) Column C (40) Column D (10) Column F (30) Column G (40) Column H (58)
Müller & Meier GmbH Peter Meier (persönlich) Musterstrasse 123 68124 Musterhausen Deutschland Dear Peter,
Stefano Brambilla Brambilla & C. Via Bramante 8 20154 Milano Italia Dear Stefano,
Tim Cook (personal) Apple Inc. 1 Infinite Loop 95014 Cupertino, CA USA Dear Tim,
Paul Stewart c/o Number Sixteen 16 Sumner Pl, Kensington SW7 3EG London United Kingdom Dear Paul,
Alphabet Inc. Sergey Brin (private) 1600 Amphitheatre Pkwy 94043 Mountain View, CA USA Dear Sergey,
The White House Barack Obama 1600 Pennsylvania Avenue NW 20500 Washington, DC USA Dear Barack,
             
envelope
_Line1
envelope
_Line2
envelope
_Address_Street
envelope
_Address_Zip
envelope
_Address_City
envelope
_Address_Country
Personal_Salutation-Line
             

 

If the field length exceeds the specified limits the PENSAKI App will automatically cut-off all excess characters.

Excel Functions and Formulas 101

 

How to verify field lengths → the Excel LEN function =LEN()

Column A Column B EXCEL© function in Column B
Carsten Kostermann  19 =LEN(B2)
Kathleen Thielnon 17 =LEN(B3)
Marion Bauerfeind 17 =LEN(B4)
Monika Wilko2323 16 =LEN(B5)

 


Identify excess field lengths  the Excel IF function

Column A Column B Column C Column D EXCEL© function in Column D
Carsten Kostermann  19 40    
Kathleen Thielnon 17 40    
Lorem ipsum dolor sit amet, consetetur sadipsci 47 40 ERROR =IF(LEN(A4)>C4;"ERROR";" ")
Peter Müller 12 40    
Frank Meistermann 17 40    

 


 

Merging fields  the Excel TEXTJOIN() function

  Column A Column B Column C Column C EXCEL© function in Column C
1 Dear Mr. Kostermann , Dear Mr. Kostermann,  
2 Dear Cara , Dear Cara,  
3 Hi Paul , Hi Paul, =TEXTJOIN(A3;" ";B3;C3)
4 Liebe Frau Dr. Schneider , Liebe Frau Dr. Schneider,  
5 Hallo Herr Merzing , Hallo Herr Merzing,  
6          
7          

 


 

How to convert consecutive capital letters  the Excel PROPER() function

Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

  Column A Column B EXCEL© formula inColumn B
1 ABC GMBH Abc Gmbh  
2 SONNENSTUHL GmbH Sonnenstuhl Gmbh  
3 ABCDEFG AG Abcdefg Ag =PROPER(B3)
4 FRITZ + Söhne GmbH Fritz + Söhne Gmbh  
5 MUSTER AKTIENGESELLSCHAFT Muster Aktiengesellschaft  
6      
7