Data Import: Send bespoke handwritten letters with PENSAKI

Pensaki is compatible with FIREFOX, CHROME & SAFARI, always in the latest version.

Pensaki is compatible with FIREFOX, CHROME & SAFARI, always in the latest version.

Data import: Addresses, salutations & text modules

At Pensaki different – SSL encrypted – data transfer methods are available, including our API. Please do NEVER send us your data by email.

You can download the required Pensaki import template for the format directly within the editor, or you can receive it from us by email.

Data preparation is the process of cleaning and transforming your raw data prior to importing it to Pensaki. It is an important step and involves reformatting, enriching and making corrections to data sets by using e.g. Google Sheets or Microsoft Excel.

The data import can take up to 10 minutes, depending on the volume. After successful completion, you will receive an email with instructions on how to proceed.

You can easily export all bad data so that you can import them again after correction. This automatically overwrites these errors.

With the preview function you can control the content of your mailing. The preview function is used exclusively for checking the content. WE WRITE WHAT YOU TRANSMIT.

1. Pensaki XLS import templates: requirements

  • Using the provided Pensaki XLS template is mandatory.
  • Don’t delete or move the 1st sheet in the XLS table.
  • All data must be inserted as text in the XLS table.
  • The file name of your XLS import file must not contain any special characters.

The data preparation requires basic knowledge in handling spreadsheet programs like e.g. Microsoft Excel©. You import addresses, personal salutations and other personalization segments (optional).

2. Data Preparation

2.1 Exemplary Import Data Structure

Example of the data structure for the standard import template. Every column has a maximum number of characters (including spaces). Excess lengths are automatically truncated and displayed as errors. In the table, both the data for the postal addresses and the salutations in the text or other segments are imported for personalization.

Table: Data structure for Pensaki standard formats. Templates vary by letter format type.

Columns: A to F: used for addressing
Columns: G: used for salutation line
Columns: H-K: used for personalization segments (optional)

Column AColumn BColumn CColumn DColumn EColumn FColumn GColumn HColumn IColumn J
address_1: max 40address_2: max 40address_3: Street (max 40)address_4: ZIP (max 10)address_4: City (max 30)address_5: Country (max 40)Salutation: Max 58segment1segment2segment3
DAB Ltd.FAO: Jane Doe16 Sumner PlaceSW7 3EGLondonUnited KingdomDear Jae,
Mlle Jacqueline Brunec/o L’Hotel13 Rue des Beaux Arts75006ParisFranceChère mademoiselle Brune,
S & A Adventures Inc.Sascha Doe6298-6260 Madeline St92115San Diego, CAUSADear Sascha,

Standard European Address Format

XLS import file: columns A - F

Column A:     Mlle Jacqueline Brune
Column B:     c/o L’Hotel
Column C:     13 Rue des Beaux Arts
Column D+E: 75006 Paris
Column F:     France
– – –
Column G: Chère mademoiselle Brune,

Standard UK Address Format

XLS import file: columns A - F

Column A: DAB Ltd.
Column B: FAO: Jane Doe
Column C: 16 Sumner Place
Column E: London
Column D: SW7 3EG
Column F:  United Kingdom
– – –
Column G: Dear Jae,

Standard US Address Format

XLS import file: columns A - F

Column A:      S & A Adventures Inc.
Column B:      Sascha Doe
Column C:      6298-6260 Madeline St
Column D+E: San Diego, CA 92115
Column F:      USA
– – –
Column G: Dear Sascha,

1. Maximal lengths per field vary.

  • Maximum line length is defined by the number of characters per line, including spaces.

2. A subset of alphanumeric characters is accepted.

  • Not all special characters are accepted. Invalid characters are classified as errors.
  • Non-printable characters are classified as errors.
  • Data needs to be without formatting/style data (“paste values”).

2.3 Tips & Best Practices to Prepare Data

You may not always be able to control the format and data type, especially if you copy & paste it from external data sources such as databases, a text file, or a web page.

Please make sure that you clean your data before importing it.

The Excel CLEAN function takes a text string and returns text that has been “cleaned” of line breaks and other non-printable characters.

Syntax: CLEAN(text)
Formula: Formula: =CLEAN(A28)

Result: Removes the nonprintable characters from the text string in cell A28.

・CLEAN removes line breaks
・CLEAN removes leading and trailing spaces from text

The TRIM function returns a text value with the leading and trailing spaces removed. You can also use the TRIM function to remove unnecessary spaces between words in a string.

Syntax: TRIM(text)
Formula: Formula: =TRIM(A28)

Result: Removes unnecessary spaces between words in a string in cell A28.

When you concatenate cells in Excel, you combine the contents of those cells. This method is often used to combine a few pieces of text that reside in different cells.

Column AColumn BColumn CColumn DFORMEL 
1DearJane,Dear Jane,=CONCATENATE(A1;" ";B1;C1)
2HiMark,Hi Mark,=CONCATENATE(A2;" ";B2;C2)
3HeyJohn,Hey John=CONCATENATE(A3;" ";B3;C3)

Formula: = CONCATENATE(A1,” “,B1,C1) – with ” ” used to insert a space between the content of Column A1 and Column B1.

Here you learn how to convert a text string to proper case in Excel. This means that the first letter in each word in uppercase, and all other letters in lowercase.

Column AColumn BColumn CColumn DFORMULA of Column D
JamesSMITHJames SMITHJames Smith=PROPER(C1)
johnJOHNSONjohn JOHNSONJohn Johnson=PROPER(C2)
RobertWILLIAMSRobert WILLIAMSRobert Williams=PROPER(C3)

With the TEXT function of Microsoft Excel© this is easily possible. The TEXT function converts numbers to text. Here is the formula: =TEXT(C1; “TT.MM”) – at the end simply copy the results and with “Paste as” … copy “Values” into the import template. Alternative formulas are accordingly: =TEXT(C1; “TT.M”) or =TEXT(C1; “TT.MMMMMMMMMM”)

 

RAW Data=TEXT(A2;"mm/dd/yy")=TEXT(A2;"dd/mm")’ =TEXT(A2;"mmdd")
12.08.201908/12/1912/080812
14 August 201908/14/1914/080814
14.08.201908/14/1914/080814
15.08.201908/15/1915/080815

Excel automatically removes leading zeros. Learn here how to keep your data in its original format, which Excel treats as text.

Phone Numbers=TEXT(A2;"0000-000-000-000")Postal Codes=TEXT(D2;"00000")
00443455567890044-345-556-7890495104951
00493455567890049-345-556-7890012300123
00493455567890049-345-556-7890012400124
RAW dataNumber CharsFormula of Column BSpecial Length Check Validation*
Mr. Adolph Blaine Charles David Earl Frederick Gerald Hubert Irvin John Kenneth Lloyd85= LEN(A1)<<<<ERROR>>>>
Mr. Adolph Blaine Charles David Earl Frederick Gerald53= LEN(A2)<<<<ERROR>>>>
Mr. Adolph Blaine Charles David Earl Frederick46= LEN(A3)<<<<ERROR>>>>
Mr. Adolph Blaine Charles David Earl Frederic45= LEN(A4)OK

* = e.g. =IF(LEN(A4)<=45;”OK”;”<<<>>>”)

The traditional approach:

1. On your worksheet, select the cells that contain the resulting value of a formula that you want to copy to the import template.

2. On the Home tab, in the Clipboard group, click “Copy” or press CTRL+C or cmd+C on your keyboard.

To copy this selection to the Pensaki XLS import template, switch to that file, and then select the upper-left cell of the paste area.

3. On the Home tab, in the Clipboard group, click Paste Button image , and then click Paste Values. Or, press Ctrl+Alt+V to display the Paste Special dialog box.

4. Select Values and click OK.

The shortcut:

1. Select the range you wan to copy.
2. Move the mouse cursor to the border of the selection until the cursor turns to the cross hairs, like you are going to move the range.
3. Right-click and hold.
4. Move the range to a new location.
5. Release the right mouse button.
6. A new menu appears. Select the third option: Copy Here as Values Only.
The range is copied and pasted as values only.

3. Start Data Import

Click import data to start the import

Once your data import is complete, you will receive an email with the result of the analysis. This can take up to 10 minutes, depending on the size of your data. Please follow the instructions to correct your errors comfortably. Please check your registered email regularly for status updates!

Option 1: Online error correction

1. Click „EDITOR“

2. Click on highlighted fields (red)

3. Make Corrections

4. Click „SAVE“ to conclude.

Option 2: Offline Error Correction

1. Click „Download Report“

2. Correct all errors directly within the XLS file.

3. Click “Start Import”

4. Bad data will automatically be replaced.

Option 1: Direct Online Correction

Fehler korrigieren
Pensaki Importfehler - Online Korrektur 01
Pensaki Importfehler - Online Korrektur 01

Option 2: Download File, fix and re-import the corrected file

XLS mit Datenfehler hier herunterladen

Click on “See Import Results”

Pensaki Data import Analysis

e.g.

Result of data import analysis 7037
Records:  51
Incorrect data: 3

XLS mit Datenfehler hier herunterladen

1. Click “Download import results as excel file”.

2. Open the XLS file “import_results.xlsx”.

3. Fix all errors with the XLS file

4. Import xls file record again

5. After Import the errors will be overwritten

With the Pensaki preview function, you can check all your letter for content errors. We write EXACTLY THE CONTENT YOU PROVIDE, i.e. spelling and grammar will NOT BE CHECKED.

5. Content Correctness Verification

Please always check carefully the correctness of all content, because Pensaki writes your letters in handwriting without spell checking. This means that the friendly robots will write your content including all errors.

Pensaki-how-to-Serienbrief-08-Vorschau-2
Pensaki-how-to-Serienbrief-08-Vorschau
To stay in the know subscribe to our free Pensaki newsletter! 
Subscribe Now!
close-image