Export and import data with Excel

This tutorial will show you how to export a PERSON record into excel or a CSV file that you can use as a template for importing PERSON records with Excel.

Firstly, this process is pretty much the same as a previous tutorial I did on exporting and importing with XML files and also a screencast tutorial except we will be using Excel and CSV files instead.  So I will assume that you have already setup your JMS queues, external system and also your MIF global directory already.  If not, you can have a look at those previous post on how to set those up.  You will need to setup the MIF global directory as that directory is the folder where the exported CSV file will be located once its created.

So the first thing we need to do is export a person record into a CSV file that we can then use as a template to import more person records.  Go to your external system that you created, and click on the ‘Publish Channels’ tab.  If you don’t have anything in there, you want to add a new row and select the ‘MXPERSONInterface’ publish channel.  This is an out of the box channel so you don’t have to create your own.  Now in the ‘End Point’ textbox, you will want to put in ‘MXFLATFILE’.  This will tell MIF that we want to export it to a CSV file when we export it.  Enable this publish channel and save it.

Now make sure you highlight the right publish channel, so click on the entire row we just created to make sure we are selecting the right one.  Click on the ‘Data Export’ button towards the bottom right corner of the table.  Make sure the ‘Publish Channel’ value says ‘MXPERSONInterface’.  Now all we need is an export condition.  This is basically a SQL where clause that we need to MIF to query and export.  You can just enter ‘1=1’ to get all records, but remember that MIF as a maximum number of records that it will allow you to export.  I believe it is around the 200 mark.  You could also type in ( personid = ‘MAXADMIN’ ).  This will query for the MAXADMIN user and export his information.  For the ‘Export Count’ field, you can enter 1 which will tell MIF that we only want one record to be exported.  This is useful if you use ‘1=1’ as your export condition.

Click OK and wait until MIF processes your request.  Go to your MIF global directory, you should see a bunch of sub-directories and one of them is called ‘flatfiles’.  This is where your CSV file is once it’s been created.  You should see a file named something like this ‘EXTIMPORT_MXPERSONInterface_1286983954258731235.dat’.  MIF defaults the flat file exports to .DAT files, but if you open it notepad, you will see that it is basically a CSV file.  You can rename this file and change the extension to .CSV.  Once you do that you can then open it with Excel.

Now you have a template that you can use to import person records with Excel.  So go ahead and make any changes you want to this template in Excel and save it to CSV.

We are now ready to import this CSV file… almost!  First, after you have saved your CSV from excel, open it with notepad and make sure everything is correct.  The first line of the CSV needs to be changed to be in accordance with MIF processing.  The first line of the excel file needs to be something along the lines of this:

EXTIMPORT,MXPERSONInterface,AddChange,EN

The first value ‘EXTIMPORT’ is the name of your external system.  The second value is the PERSON interface object.  The third value is the action property we want applied to this import process.  The last is the language.  There should NOT be any commas after the last value.  The first line has to follow this format exactly or else you will get errors.

Once you have everything in place, you are now ready to import.  Go to your external system and click on ‘Enterprise Services’ tab.  Click on the ‘MXPERSONInterface’ row and click on ‘Data Import’ button.  From the options, choose ‘Flat File’ and leave the ‘Delimiter’ and ‘Text Qualifier’ as it is.  You can check the ‘Import Preview?’ option to have MIF verify the file before it gets processed.  I would recommend this as it gives you a pretty good detailed error messages if your flat file isn’t well formatted, but sometimes it doesn’t actually import anything.  Lastly, upload your CSV file and wait for it to get processed.  If you checked the ‘Import Preview?’ box and nothing happens, then try it again and uncheck the ‘Import Preview?’ box.

Did You Know...

As Maximo Experts, we have developed several add-on products for Maximo that mobilize the work force, simplifies assignments, provides ad-hoc reporting capabilities and facilitates the seamless integration of Service Requests into Maximo.

Check out our products by clicking on the following links: EZMaxMobile, EZMaxPlanner and EZMaxRequest.

Find Out More

One thought on “Export and import data with Excel

Leave a Reply