Using a CSV file : tips and tricks

Last updated: 2021-11-11 17:06

This document goes through a list of issues which users could encounter when uploading a CSV file in HD4DP v1. The article provides by tips and tricks to overcome these issues.

How should I create a CSV file?

Several methods can be used for creating a .csv file :

  • Download the .csv file for your data collection on
  • Consult this article first
  • Use a spreadsheet  (e.g. Microsoft Excel, OpenCalc)
  • Save the document using one of the 2 following methods :
    • Use the spreadsheet and make sure the file saved use semi-column (;) as separator to fitting the standard of Depending of the spreadsheet used, the creation of the .csv file containing semi-columns as file separators, could be carried out in 2 steps :
      • save the file as .csv file containing commas as column separators
      • edit the .csv file with a text editor and replace the commas by semi-column
    • Copy the data in a note editor and save it as .csv file
      • on Microsoft : notepad, notepad++
      • on Macintosh : textedit;app

How to find the technical requirements for a field in the registration?

  • Consult the registration requirements for each field in the data collection definition, as shown below :
    • Field Type e.g. text, boolean, choice, date, patient
    • CSV column_name 
    • Required : 'true' means that is a mandatory field and it has be present within the .csv extract
  • Postal code missing : use the value 999
  • Postal code for people living abroad : 9999

How to perform a massive deletion of records with HD4DP v1?

  • If your HD4DP version is not yet version 1.10.3 (consult this website for getting informed with HD4DP version), contact
  • For future releases, the functionality is available

How should I treat NISS code with leading zeros when creating my csv?

 For treating leading 0s of a national registry number (NISS), you can fill in the field 'patient_id' by adding a simple quote (')  before the leading zeros as follows:

  • as is : 0007788..
  • to be: '0007788...

We advice the use of Excel TEXT function for formatting your NISS field for adding leading zeros. You can consult the following article for more information.

Can I use both Windows and Macintosh file for creating a csv file?

  • From the HD4DP version 1.10.3, you create your CSV file can either on Windows or Macintosh
  • For older versions, you can create a CSV on Macintosh buy you have to save it on a Windows environment before uploading it in HD4DP

How should I format the date fields within my csv extract?

The date format has to be dd/mm/yyyy, therefore  make sure the date format is correct by consulting your .csv file before uploading. Open the file with a text editor and not a spreadsheet and check the format date.

How should I proceed with deceased patient?

  • Put a value in the field 'patient | deceased' 
    • TRUE if patient deceased
    • FALSE if patient alive
  • Put the date of death in the field 'patient_id | date_of_death'  if the patient is deceased 

How can I fix errors with the automatic generation of Patient_ID?

  • If you do not dispose of a national number of the patient, a patient_id will be generated automatically. This is called a 'codepat' and you can find more information in the article "What is a codepat and how is it calculated?
  • If you use a codepat, you should use the substrings function for extracting the name and first name from the codepat field to fill in the strings from the 'CODEPAT': 
    • patient_id | name
    • patient_id | first_name
  • Below, an example of use of substring functions directly applied on the 'CODEPAT' for carrying this information extraction
    • patient_id | name  = MID(cell,9,2)   
    • patient_id | first_name =MID(cell,11,2)
    • patient_id | generated has to set to 'TRUE'
    • patient_id | sex has to be set to 'M' (Male) or 'F'(Female)
  • Test this procedure using the following csv extract, you should get the 'patient_id' filled in automatically


  • Pay attention cases when the substring function returned values as DE, DU IN, LA, LE, OP in the field 'patient_id | name', you should add one characteristic 

e.g. LE could become LEE or DE could become DEE 

Which norm of country code should I use in my csv extract?

Fill in the field 'NATION' using the the 2-digits ISO standard 3166alpha2 format.

How can I make sure that there are no spaces in cells left empty?

Make sure there is no spaces in cells left empty by searching the 'SPACE' characters (e.g. CTRL-F and push on the SPACE touch).
Remove all the spaces in cells left empty by replacing all the 'SPACE' characters by an empty string '' (e.g. CTRL-H and replace the 'SPACE' characters by an empty field '').

How can I treat the numbers with decimals?

The decimal separator must be the comma (e.g. 1,78 ; 1,89)

What are the not supported postal codes?

Postal CodeLocality
1007Assemblée de la Commission Communautaire Française
1005Assemblée Réunie de la Commission Communautaire
1099Bruxelles X
1008Chambre des Représentants
1046European External Action Service
1041International Press Center
1035Ministère de la Région de Bruxelles Capitale
1031Organisations Sociales Chrétiennes
1012Parlement de la Communauté française
1047Parlement Européen
1006Raad van de Vlaamse Gemeenschapscommissie 
1009Senat de Belgique
1048Union Européenne - Conseil
1011Vlaams parlement

What values to use when postal code is missing or for patient living abroad?

Missing postal code : 999
Patient living abroad : 9999