Importing data to the Intermediate Database (IDB)

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. 

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’

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).

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. 

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

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