Import new records to device table (new CI)

What is the easeist way to make Import from file (txt, csv,...) to SM ?

There is a list of new CI, while import must be auto increase id for CI.

  • I've found the Text Import Wizard to be pretty useful.  

    In the Text Import Wizard, you call out the fields your .txt or .csv is going to contain (I usually use .csv).  You can create a formatctrl record that the import uses (so you can call the getnumb,fc RAD to populate the CI ID, just like you would if you called the device formatctrl record on a normal add) and can include any special processing you'd like to apply just for the Text Imports.  See the Help documentation on 'Import a character-delimited text file'

  • Verified Answer

    So, running a text import is pretty straightforward, if you read the guide and have tried it in your environment.

    You start with a .csv that includes whatever fields you want to populate in your device records.  A simple create might have the columns for logical.name, type, subtype, location, environment and assignment.  Let's say you have a .csv named 'serverCreate.csv' like the following:

    logical.name, type, subtype, location, environment
    server1, computer, server, LOC01, production
    server2, computer, server, LOC02, test
    server3, computer, server, LOC01, production

    Then, you create a Text Import Descriptor file for your import.  The descriptor is basically the explaination of the way the data is looks in the csv, the name of the formatctrl record to use when running the import, the mapping of which columns in your csv map to which fields in the dbdict, any special expressions that you want to trigger when you run the import, etc.

    So, start at Database Manager.  From the menu, select 'Text Import Wizard'.  On the first screen, we care about the 'File to Import' field and the 'Destination table' field.  You can ignore the 'Member' and 'File Type'.  Click the folder icon next to the 'File to Import' field and browse to the location of your .csv file.  In the 'Destination table' field, enter 'device'.

    If there are any Import Descriptor files that already exist for the selected destination table, the system will display a list.  Otherwise, select 'New'.

    When you select new, the system will display the first few lines of your .csv file and prompt you to select whether this is a character delimited csv or a fixed width csv.  Using our sample, you'd want to selecte 'Character-delimited'.  Then click 'Proceed'.

    Even if you selected 'New', the system will then display the contents of the first Import Descriptor record.  Don't know why.  But this is the one we're going to modify.  

    The 'Import Name' field is what you want to call this import record so you can save it and use it later.  The 'Import Table' field is read only, and uses the 'Destination table' value you selected earlier (in this case, 'device').  The Import Mode allows you to determine how the system should handle the records in the system when running this import.  For example, if you select 'Add/Replace', the system will add any data in your import record that doesn't have a match in the system already, and update any records where the primary keys match.  If you want to make this record ONLY add new records and not update any existing records, select an import mode of 'Add'.  If you want it to update existing records and not add any new records, then select 'Replace Only'.  In this case, we want to only add, so select 'Add'.

    The 'Format Control' field allows you to specify which formatctrl record should be applied to this import.  I find it's easiest to create a formatctrl for each general type of import.  Like, I have a formatctrl for the device table, for the probsummary table, contacts table, etc... anything that I created a text import for, I usually create a specific formatctrl for.  The fc works like any other fc, so you can set your Add queries, expressions, javascript, subroutines - anything you want to generate that should apply to this import.  For example, you mentioned the population of the CI ID field.  So you could take a look at the device formatctrl record, at the code that is generating that ID, and duplicate that code in a new FC record that you apply to this import.

    You can also set your Delimiter value. Using the sample from above, that would be 'Comma', but if you've got a different .csv file, use whatever value makes sense.

    The 'Fields' array of values is where you map out the fields in your import that map to fields in the device table. The fields need to be listed in the same order as the fields in your .csv. So in this case, you'd have 'logical.name', 'type', 'subtype', 'location' and 'environment'. You don't have to worry about the Field Type, the Date Format the Array Type or Occurrences in this this case. But, let's say that one of your fields was a Date field. You could list the format of the date value in the import, so the system can figure out how to turn the value of the date in the .csv into a date field in HPSM. Array fields are more complex, so I'm going to skip those for now.

    In the 'Advanced' tab, you can do a bunch of coding. But, for a simple add, using our example, the only part you need to do is the 'Skip Lines' in the bottom right. Enter 1, because our .csv has a header row, and we want to tell the tool to skip it.

    Click the 'Test' button in the menu, and the 'Test Import' tab will grab the first few lines of your .csv file and display it for you, so you can check to see if the data looks right. If it does, Add your import and then click Proceed.

    If you want to have this import run in the background on a schedule, select the 'Schedule' option. Otherwise, click the 'Run' and run this import. The system will then use your .csv file, put it through your Import Descriptor record, and create (or update, based on whatever you selected) the records in the tool.

    Try this in your lower environment; and read the help documentation.

     

  • Thank you so much for such a detailed explanation.

    Just wondered should contain a file the field name row or not.

     

    When i try to make import get errors:

    Added record contains the keys  NULL. (file.import.new,add)
    file:(device) key:(logical.name=) (file.import.new,add)
    Key #1 - empty. (file.import.new,add)

    import file contains next columns:

    logical.name; type; subtype;  sub.subtype;  istatus;  mos.rec.num;  model;  location;  assignment;  assignee.name

     

     

  • Thank you so much for such a detailed explanation.

    Just wondered should contain a file the field name row or not.

     

    When i try to make import get errors:

    Added record contains the keys  NULL. (file.import.new,add)
    file:(device) key:(logical.name=) (file.import.new,add)
    Key #1 - empty. (file.import.new,add)

    import file contains next columns:

    logical.name; type; subtype;  sub.subtype;  istatus;  mos.rec.num;  model;  location;  assignment;  assignee.name

     

     

  • Thank you so much for such a detailed explanation.

    Just wondered should contain a file the field name row or not.

     

    When i try to make import get errors:

    Added record contains the keys  NULL. (file.import.new,add)
    file:(device) key:(logical.name=) (file.import.new,add)
    Key #1 - empty. (file.import.new,add)

    import file contains next columns:

    logical.name; type; subtype;  sub.subtype;  istatus;  mos.rec.num;  model;  location;  assignment;  assignee.name

     

     

  • Not sure I understand your question, but if your Import Descriptor record was set up correctly, you shouldn't be getting that error.  So that means something is wrong.  Can you show a screenshot of your Import Descriptor record 'Fields' tab and 'Advanced' tab?  (If you run the Text Import Wizard off Database Manager, it's the screen labled 'Import Wizard 3/4')

  • I Applied a workaround solution for this error.

    Just did not use the FC and everything went without errors. So I had to manually specify the value of the fields: logical.name and id. (and then set new value for number table for device)

    It seems there is a limit to the number of columns to import. So it was much easier and faster to Import the basic fields, and everything else - add(merge) to the already created records using "SQL managment studio" (directly to the database)