Reset Search
 
Article

Submit records using a CSV file : tips and tricks

« Go Back

Support article

 
Last Modified Date5/30/2018 3:46 PM
SummaryThis document goes through a list of issues which users could encounter when uploading a CSV file in HD4DP. The article provides by tips and tricks to overcome these issues.
Content

Introduction

The article answers the following questions : Consult this article for getting started with HD4DP.
Consult first the site https://support.healthdata.be  for any question about csv even if through the following, some useful articles will be sometimes referred.
 

How should I create a CSV File?

Several methods can be used for creating a .csv file :
  • Download the .csv file for your datacollection on https://www.healthdata.be/dcd/#/collections
  • 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 healthdata.be. 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 ?

  • Postal code missing : use the value 999
  • Postal code for people living abroad : 9999
  • 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
User-added image
 

How can I upload a CSV file in HD4DP?

Consult this article.
 

How to perform a massive deletion of records with HD4DP?

  • If your HD4DP version is not yet version 1.10.3 (consult this website for getting informed with HD4DP version ), contact support.healthdata.be@sciensano.be
  • 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.
 

How should I update a big CSV extract  (>100 records) ?

Use the FASTTRACK extraction way as explained in this article.
 

Can I use both Windows or 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 speadsheet 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 shoud 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
​Example:
 CODEPAT;patient_id;patient_id|generated;patient_id|internal_patient_id;patient_id|
name;patient_id|first_name;patient_id|date_of_birth;patient_id|sex;patient_id|deceased;
patient_id|place_of_residence;HIVType;RNA_DATE;RNA_VALUE;RNA_SPECIMEN;
RNA_ASSAY;DNA_PCR_DATE;DNA_PCR_VALUE19711125JADAM;71112535971;;;;;25/11/1971;M;;2000;
HIV1;12/12/2016;25;B;99;;19711125JADAM;19711125JADAM;TRUE;;JA;DA;25/11/1971;M;;2000;
HIV1;05/10/2016;82;B;90;;
 
  • 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 is 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 postal codes not supported ?

 
Postal Code
Locality
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
1110OTAN
1012Parlement de la Communauté française
1047Parlement Européen
1100Postcheque
1006Raad van de Vlaamse Gemeenschapscommissie 
1044RTBF
1033RTL-TVI
612Saint-Nicolas
1101Scanning
1009Senat de Belgique
1105SOC
1048Union Européenne - Conseil
1011Vlaams parlement
1043VRT

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

  • Missing postal code : 999
  • Patient living abroad : 9999

Feedback

 

Was this article helpful?


   

Feedback

Please tell us how we can make this article more useful.

Characters Remaining: 255