Service Desk Practitioners Forum
cancel

Data Exchange - Not extracting all fields!

SOLVED
Go to solution
Highlighted
Yousef Jamous
Respected Contributor.

Data Exchange - Not extracting all fields!

Hi
I've an excel sheet that I use to get data from. When I do so, I find out that some of the columns has been exported to XML, while other fields are not! I can't figure out why!

I'm attaching a sample of my excel, ini file and resulting XML file.
Could it be related to type of custom fields?
11 REPLIES
Yousef Jamous
Respected Contributor.

Re: Data Exchange - Not extracting all fields!

Forgot to mention important information:

SD 4.5, SP24 on Windows 2000 Server
Ben Snell
Super Contributor.

Re: Data Exchange - Not extracting all fields!

Hi

Any chance of posting the log file as well?

Ben
Yousef Jamous
Respected Contributor.

Re: Data Exchange - Not extracting all fields!

This is the contents of the log file.


===============================================================
EXTRACTOR Started: 2008-05-12 15:31:25
===============================================================


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ BEGIN : YJ_LICENSES_CLASS ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Started : 2008-05-12 15:31:25
SqlQuery : SELECT "SEARCHCODE","LICENCENO","EQUIPCODE","EQUIPSTAT","STATION" FROM RAD
Receiving MetaData : [0]=SEARCHCODE, [1]=LICENCENO, [2]=EQUIPCODE, [3]=EQUIPSTAT, [4]=STATION
Export Columns : [0]=true, [1]=false, [2]=false, [3]=false, [4]=true
Queries To Database : 1
Rows Selected : 12
Finished : 2008-05-12 15:31:25
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ END : YJ_LICENSES_CLASS ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

====================================================================
EXTRACTOR Finished: 2008-05-12 15:31:25
Time elapsed : 0 hrs 0 min 0 sec (0)
====================================================================

Ben Snell
Super Contributor.
Solution

Re: Data Exchange - Not extracting all fields!

Hi

Yo ucan see columns 2 to 4 in the log file are set to "false" - this means that they are not put into the XML file.

Try altering the ini file so that attributes and columns match you spreadsheet and I believe that this should work.

Regards
Ben
Yousef Jamous
Respected Contributor.

Re: Data Exchange - Not extracting all fields!

Thanks Ben for your reply. I'm sure that the mapping is right. I mean the columns are mapped to the right attribute!!

Could Spaces in the attribute names cause some issues?
Gerry Allardice
Outstanding Contributor.

Re: Data Exchange - Not extracting all fields!

The Columns must match the Attributes. ie.
ATT=SEARCHCODE,AAA,BBB,CCC
COL=[SEARCHCODE],[XXX] AS [AAA],[YYY] AS [BBB],[CCC]

If the column name is not the same as the Attribute you need to use the [ZZZ] AS [AAA] syntax.

Also as a rule I always use uppercase and no spaces for attribute name.(Is possibly ok to use what you have , but way back it use to be a problem so I never take the chance.

Regards
Gerry
Alexander Kriku
Honored Contributor.

Re: Data Exchange - Not extracting all fields!

hi!

you need to check the settings of the table in your excel file: Insert â > Name â > Define.
the range of the table should be equal to the range of your real data.
Alexander Kriku
Honored Contributor.

Re: Data Exchange - Not extracting all fields!

sorry didn't mention the attachment )

the answer of Gerry Allardice is correct, try to use the same attribute and colunm names and try to avoid spaces
Yousef Jamous
Respected Contributor.

Re: Data Exchange - Not extracting all fields!

Thanks Everyone
Phase one worked for me!
I managed to export all data to XML. But next step failed, which is populating SD :-(

I get the following message:
warning: Attribute not defined: TRA APPROVAL on entity LICENCE_CLASS (id = 28)
ERROR: Parsing error occurred at:
file:/C:/Documents and Settings/Administrator/Application Data/Hewlett-Packard/OpenView/Service Desk/tmp/YJ_Radio_Licenses.xml_1.xml_Entities.str, line 8:
character not allowed
warning: fatal_error occurred while processing YJ_Radio_Licenses.xml_1.xml


What is SD complaining about?
Yousef Jamous
Respected Contributor.

Re: Data Exchange - Not extracting all fields!

Ok
Thanks for everyone for contributing to my issue. I managed to progress, but let me give you my findings (inspired of everyone's feedback):
1) First Problem: Some of the columns did not export to XML. The reason is that the ATTRIBUTE name should match the COLUMN name (including the spaces and spelling). Weired, but it works.
For example:
I had an attribute called Serial Number. So, in the INI file, I called it SERIAL NUMBER.

2) Regarding Data import to Service desk. Service Desk does not like to see spaces in the PROPERTY NAME for the Field mapping.

So, what I had to do is:
After I created the XML, I edited it to match the entries in the XML with the field mapping PROPERTY.

For example:
The XML had SERIAL NUMBER. So, I changed it to SERIALNUMBER and I changed the property to SERIALNUMBER as well.

Now, I ran into another issue :-) I think it's minor.

For some of the fields like SERIAL NUMBER. The number is large 10812701999

When it's imported to service desk, it looks like:
1.0803461999E10 (Exponential format).

The customer field is STRING (40), so why it looks this way? Also the XML has the value similar way (Which makes me think that it's caused be EXCEL)?!! Any clue there?
Lisa Denison
Super Contributor.

Re: Data Exchange - Not extracting all fields!

Hi Yousef,

For your exponential number issue you could try using 'convert text to columns' in Excel on your column. I got that hint from someone in these forums to prevent '1' turning into '1.0' in the XML.

Highlight the column, go to Data -> Text to Columns -> Next -> Next -> select Text -> Finish

Cheers,
Lisa