Highlighted
Acclaimed Contributor.
Acclaimed Contributor.
876 views

Import new records to device table (new CI)

Jump to solution

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.

0 Likes
1 Solution

Accepted Solutions
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

Re: Import new records to device table (new CI)

Jump to solution

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.

 

View solution in original post

5 Replies
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

Re: Import new records to device table (new CI)

Jump to solution

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'

Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

Re: Import new records to device table (new CI)

Jump to solution

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.

 

View solution in original post

Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Import new records to device table (new CI)

Jump to solution

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

 

 

0 Likes
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

Re: Import new records to device table (new CI)

Jump to solution

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

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Import new records to device table (new CI)

Jump to solution

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)

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.