Skip to main content

Part of Intermediate Database (IDB) guidance preparing the IDB for making submissions to the SDCS Cloud

Importing data to the Intermediate Database (IDB)

Though data can be manually entered into the IDB file, it is assumed that an import/export routine is in place to transfer data from an electronic patient records / administration system (EPR/PAS) or local data warehouse to the IDB.  Where this is the case, the data items required to populate the IDB should be extracted from the appropriate local information systems and then converted (where necessary) to ensure data integrity.

For any queries about how to obtain export files or for issues with the format or contents of export files you should check with your system supplier. Always check that you’re using the latest available version of the supplier system and follow any guidance provided to set up your system.

Current Chapter

Current chapter – Importing data to the Intermediate Database (IDB)


Summary

Though data can be manually entered into the IDB file, it is assumed that an import/export routine is in place to transfer data from an electronic patient records / administration system (EPR/PAS) or local data warehouse to the IDB.  Where this is the case, the data items required to populate the IDB should be extracted from the appropriate local information systems and then converted (where necessary) to ensure data integrity.

For any queries about how to obtain export files or for issues with the format or contents of export files you should check with your system supplier. Always check that you’re using the latest available version of the supplier system and follow any guidance provided to set up your system.


Tips for importing CSV files

Following any local conversion process, it is strongly recommended that the CSV export files are not opened or saved in MS Excel prior to their import into the IDB.  The files should be saved locally as CSV files (not converted to spreadsheets) and, if opened, opened using a plain text reader such as MS NotePad or TextEdit (on Mac). This is due to a number of known issues where Excel will automatically reformat your data such as by:

  • dropping the leading zero from data stored in plain text (for example. ‘01’ becomes ‘1’ in Excel, where 2-digit numbers are expected for some fields in the IDB);
  • changing long numbers (e.g. SNOMED CT codes) to scientific notation , for example 123456789123456789) becomes 1.23457E+17’and the stored number being amended to 123456789123456000, resulting in rejected codes in the IDB

Viewing dates in Excel can also be misleading as Excel uses a display date format of DD/MM/YY, to show all dates by default, whereas the actual format of dates stored in plain text is commonly YYYY-MM-DD or YYYY/MM/DD – which is important for the import settings used to import data into the IDB. 


Using the access import wizard

To start the import process it is recommended that a ‘clean’, previously unused, copy of the IDB in the relevant version from TRUD (see above) is used along with fresh downloads of export files from your PAS (to avoid errors from files having being amended). To use the Access import wizard to import CSV files into the IDB you should use the following steps.

1. Opening the IDB, you should see an Access file presented as in the below screenshot with tables listed down the left-hand side corresponding with the data set tables. To begin the imports, right click on the first table you wish to populate and select ‘import’ and then ‘text file’

screen shot of the IDB page with all access objects highlighted on left hand side.

2. The next screen will ask you to select the CSV file to be imported (browse to select the individual file matching the table it’s to be imported to) and then select ‘append a copy of the records to the table’ ensuring the correct matching table is selected from the drop-down (appending the records ensures that records are added without amending or altering the fields and settings of tables in the IDB).

screen shot of access data base screen with get external data window open

3. At the next screen, select ‘delimited’ (as your CSV file will be delimited with commas separating each field) and then next. 

4. At the next screen (see below screenshot), ensure the comma and first row contains field names are selected. 

screen shot of import text wizard screen open

5. Before progressing, click on the  advanced… button in the bottom eft which will bring up a new window.

screen shot of import text wizard with test import specification screen open

6. Under the dates, times and numbers section of this window, select the date and time formats to match the format of these as shown in your CSV file in plain text (for example as viewed in NotePad, rather than Excel) – this commonly involves changing the date order (such as to YMD), delimiter (such as to ‘-‘) and including leading zeros in dates (but depends on the plain text format of your data).

This will only need to be done once, as you can select the option to ‘Save As’ and your preferences will be preserved for future submissions.

7. Once you’ve checked the settings selected match the format in your CSV file, select ‘ok’ to return to the previous screen.

8. Then finally, select next and then finish to complete your import

screen shot of import text wizard with import to header screen highlighted

 

 

Last edited: 28 April 2021 9:47 am