Below are a few tips to help with successful loading of data in CSV format through MEA (now known as Integration Framework)

1. Ensure that your Object Structure supports flat files

There is a text box on the object structure record that needs to be checked to allow this object to be used with CSV files.

2. Use the correct format

Export a test record to get a CSV template in the correct format to import back in.

3. Include Enterprise service and External system in file

Ensure that the first line includes the name of your enterprise service and external system. (again exporting a test record will show you the format)

4. Remove any extra commas from the first line of the CSV file

If there are 15 columns in the spreadsheet, when you edit the file in Excel and save it, it will add 15 commas to the end of the first line and cause the import to fail. After editing the file in Excel, re-open it in notepad and remove all commas after the last character on the first line.

5. Commas are deleted by Excel

When using Office 2003 (could also be other versions), if the last column in the spreadsheet is blank, any commas at the end of the line, after about row 16, get deleted by Excel. This is noticed when you re-open the document in notepad. As a workaround when in Excel, I usually fill the last column with ‘XXXXX’ then use notepad++ to search and replace the value with null afterwards.

6. If using the sequential queue to load. Ensure that the queue is empty.

If previous files were loaded and failed, the queue will be ‘blocked’ until these messages have been deleted. To see if the queue has error messages in it go to ‘Integration – ‘Message Reprocessing’.

If there are messages in the queue they will be displayed one at a time under the messages box. To clear these, check the box next to the message and click ‘select action – ‘delete message’.

If there is more than one message then this can be very time consuming as there will a 5 seconds delay before the next message appears. To delete these in bulk login to the WebSphere console and navigate to the sequential queue;

Service Integration (in left hand column ‘Buses’ click on ‘intjmsbus’ ‘messaging engines’ click on the ‘ctgNode01.MXServer-intjmsbus’ ‘queue points’ click on the sequential queue ‘SQINBD@ctgNode01.MXServer-itjmsbus’ ‘Runtime’ tab  Messages

Then select ‘Delete All’

7. Check for any non standard characters within the file.

Use notepad++ to open your file and check for any non standard characters within the file that will cause issues while loading.