Variant and patient data

Importing text files

For curators, managers and the database administrator, LOVD supports importing your variant and patient information from tab delimited text files, data preferably enclosed by double quotes. These can be taken from a LOVD download, or saved from a spreadsheet program such as Calc or MS Excel. You do not need to send the file to the server using FTP, as was done with LOVD 1.1.0. The exact list of columns needed is dependent on your LOVD configuration. If you're unsure, make a full download of your gene in the configuration area.
To import new data, click on the 'Configuration' tab in the menu and find the "Import new variant and patient data" link.

Warning Please NOTE that spreadsheets are well known for introducing errors in importing/exporting text files, due to automatic interpretation of the values. When using a spreadsheet program to edit a downloaded file, format all cells to "Text" before importing/pasting the downloaded data.

Also some cases have been reported where MS Excel removed contents of large text fields (> 255 characters).

If you're converting your own spreadsheet data to LOVD format, please read the following notes carefully. Always start out with a LOVD download file, so you have the correct headers!

  1. The first line should contain the version number and is very important in versions 2.0-04 and up, but should be left out in older versions of LOVD. If you do not include this line, LOVD will assume it's an old import format, and will alter the pathogenicity settings. The line should say something like:

    ### LOVD-version 2000-150 ### Variants/Patients ### Do not remove this line, unless importing in versions before 2.0-04 ###

  2. The second line contains the column names and is essential. LOVD looks at these column names to check if all required columns are present. The order of the columns does not matter.

  3. If there are mandatory columns missing from the import file, you get a warning and nothing is imported.

  4. If non-mandatory columns are missing, the column value will remain empty for the imported variants.

  5. Columns which are present in the file, but not in the database, are ignored. The names of these columns will be mentioned after a successful import.

  6. The ID* columns, such as ID_allele_ and ID_pathogenic_, contain numeric references representing values that will appear on the screen. The meaning of the (often numeric) values of these columns are listed below:
        ID_allele_ (optional, default value is Unknown)
        0 => Unknown
        1 => Parent #1
        2 => Parent #2
        10 => Paternal (inferred)
        11 => Paternal (confirmed)
        20 => Maternal (inferred)
        21 => Maternal (confirmed)
    Warning Please note that the below values for pathogenicity have changed as of LOVD version 2.0 build 04. If you're importing anything in an older version of LOVD, do not use these values, but scroll down for the older values.

        ID_pathogenic_ (optional, default value is Unknown)
        This should contain two numbers; the first for the reported
        pathogenicity and one for the concluded pathogenicity.
        1 => No known pathogenicity
        3 => Probably no pathogenicity
        5 => Unknown
        7 => Probably pathogenic
        9 => Pathogenic
    So 95 means a mutation is supposedly pathogenic, but this has not been proven.

    All options are:
        11 => -/-
        13 => -/-?
        15 => -/?
        17 => -/+?
        19 => -/+
        31 => -?/-
        33 => -?/-?
        35 => -?/?
        37 => -?/+?
        39 => -?/+
        51 => ?/-
        53 => ?/-?
        55 => ?/?
        57 => ?/+?
        59 => ?/+
        71 => +?/-
        73 => +?/-?
        75 => +?/?
        77 => +?/+?
        79 => +?/+
        91 => +/-
        93 => +/-?
        95 => +/?
        97 => +/+?
        99 => +/+
    Warning If you're importing in LOVD 2.0-03 or earlier, use this list of pathogenicity values and make sure there is no version code on top of the imported file.
        0 => No known pathogenicity
        1 => Pathogenic
        9 => Unknown
    All options are:
        00 => -/-
        01 => -/+
        09 => -/?
        10 => +/-
        11 => +/+
        19 => +/?
        90 => ?/-
        91 => ?/+
        99 => ?/?

        ID_status_ (optional, default value is Submitted)
        1 => Submitted
        4 => Non public
        7 => Marked
        9 => Public
    This contains the internal variant ID (numeric). If your database is empty, you can start counting from 1. If variants are equal, you can copy all variant fields including the variant ID in the file. If a variant is already in the database, use that variant ID. The import script will then check if the data in the file equals the data in the database.

    This contains the internal patient ID (numeric). If your database is empty, you can start counting from 1. If the patient has more than one variant, you can copy all patient fields including the patient ID in the file. If a patient is already in the database, use that patient ID. The import script will then check if the data in the file equals the data in the database.

        ID_submitterid_ (optional)
    The submitter ID of the submitter who's submitted this patient.

        ID_variant_created_by_ (optional, default value is you)
        ID_variant_edited_by_ (optional)

    The user ID of the user who's created and/or edited this variant data.

        ID_patient_created_by_ (optional, default value is you)
        ID_patient_edited_by_ (optional)

    The user ID of the user who's created and/or edited this patient data.

  7. Date fields (variant_created_date_, variant_edited_date_, patient_created_date_ and patient_edited_date_; all optional, default value is date of today) should contain a date in the format YYYY-MM-DD HH:MM:SS (2007-05-21 11:57:31), optionally leaving the seconds out.

  8. You can then import the file back into LOVD through the configuration area (Configuration > Import new variant and patient data). You don't need to send the file through FTP, like in LOVD 1.1.0.

  9. « Downloading data to text files Database statistics »