Service Desk Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Excel export problem- no data

SOLVED
Go to solution
Highlighted
Gabor Radvanyi
Occasional Contributor

Excel export problem- no data

Hi!

When I generated the xml file everything went OK. Got no error messages. When I checked the file, ther were no data in the fields where only numbers were supposed to be. Instead, the fields got NULL value. For example. I have a column in the excel called PROCSPEED with the values 1700, 1600 etc. Only numbers.And they just get lost during the process. But only the fields that contain only numbers. I tried to change the field format to number, but got the same result. Anyone heard of it before?

Gabor
13 REPLIES
Robert S. Falko
Honored Contributor
Solution

Re: Excel export problem- no data

Gabor,

It is hard to diagnose your problem without a sample of the source data and the .ini file.

However, it sounds like the issue is either one of formatting or perhaps it has something to do with the ODBC configuration.

Finally, which SP are you on ?

-Josh
Gabor Radvanyi
Occasional Contributor

Re: Excel export problem- no data

Josh!

I use SD4.5 SP17.

Here is the ini file.
--
-- This data exchange configuration file has been generated
-- by the data exchange configuration file wizard.
-- Wed Jun 28 16:03:11 GMT+02:00 2006
--

[DSN]
NAME=upc_excel
USR=
PWD=

[SYSTEM]
LOG=TRUE
XML=TRUE
DUMP=FALSE
TXT=FALSE
LOG_FILE=C:\Program Files\Hewlett-Packard\OpenView\service desk 4.5\client\data_exchange\log\upc_ci.log
XML_OUTPUT_FILE=C:\Program Files\Hewlett-Packard\OpenView\service desk 4.5\client\data_exchange\xml\upc_ci.xml
APPLICATION_NAME=UPC_CI

[CLASSES]
NAME=SD

[SD]
SOURCE=[SD]
ATT=[SD_MEMORYSIZE], [SD_DISKSIZE], [SD_PROCSPEED], [SD_PROCNUMER], [SD_SIZE], [SD_OPTICALDRIVE], [SD_PURCHASEDATE], [SD_MODEL], [SD_SEARCHCODE], [SD_STATUS]
COLUMNS=[SD].[MEMORYSIZE] AS [SD_MEMORYSIZE], [SD].[DISKSIZE] AS [SD_DISKSIZE], [SD].[PROCSPEED] AS [SD_PROCSPEED], [SD].[PROCNUMER] AS [SD_PROCNUMER], [SD].[SIZE] AS [SD_SIZE], [SD].[OPTICALDRIVE] AS [SD_OPTICALDRIVE], [SD].[PURCHASEDATE] AS [SD_PURCHASEDATE], [SD].[MODEL] AS [SD_MODEL], [SD].[SEARCHCODE] AS [SD_SEARCHCODE], [SD].[STATUS] AS [SD_STATUS]
LOADTABLE=FALSE

And I attached a screenshot, showing the data in the excel. Meanwhile I figured out that if I use " ' " in front of the numbers (not 1700, but '1700) excel saves the numbers as text. And the numbers appear in the xml. Is there any other way to do it? Or I have to put "'" in front of all the numbers?

Gabor
Bc'tje
Collector

Re: Excel export problem- no data

Gabor (and others)

To convert numbers stored as text to real Excel numbers:

1. Find an unused cell somewhere in your workbook file (or in a blank file) and enter the number 1 in it.
2. Select the cell containing the number 1 and Copy it to the Clipboard.
Select all the cells containing the numbers stored as text.
3. From the menus do Paste Special Multiply.
I didn't do it
Judit_Pongracz
Super Collector

Re: Excel export problem- no data

Hi Gabor,

just a shot in the dark: have you tried formatting the whole column as text in Excel? (Right click the column- Format cells- select Text from the list.)
I guess you defined your import area correctly in Excel, for example you didn't miss to include in the area the columns which should be imported?

Judit
Gabor Radvanyi
Occasional Contributor

Re: Excel export problem- no data

bcjte!

Ok, thx, I'll try it.

Gabor
Gabor Radvanyi
Occasional Contributor

Re: Excel export problem- no data

Judit!

Yes I tried it. Didn't work :(
Judit_Pongracz
Super Collector

Re: Excel export problem- no data

Gabor,

sorry I couldn't help.
Just one suggestion, though it's not my business, but probably it would be better to use the users' ID for importing, as I imagine there might be several guys called Szabo Jozsef, and if SD cannot decide which one to choose, it will skip that record in the import, it won't get loaded in. Or ID and name at the same time, that's the safest perhaps.
Sorry to give advice while you never asked for it.:)
Judit
Again, sorry if the id was there, but was not visible from the screenshot.:)

Re: Excel export problem- no data

Hi Gabor,

Try this, in the .ini file find the columns section and convert the field to string.
To do this, find the field for example [SD_DISKSIZE] and replace for STR([SD_DISKSIZE])

Hope this helps.
Regards,
Mariana
There's nothing impossible!
Gabor Radvanyi
Occasional Contributor

Re: Excel export problem- no data

Judit

Thanks.I know what you mean. But I only try to export the data to the xml, the importing just starts after this. That is going to be easier. Hopefully....:)
Gabor Radvanyi
Occasional Contributor

Re: Excel export problem- no data

Mariana and all of you guys!

The STR thing did not work. Got no idea why. But I only tried it once, and moved on. Since this is only a one time data upload, I simply saved the data in Access, and generated the xml, from the Access data source. The number fields apeared just right. Thanks for all of your help!

Re: Excel export problem- no data

Gabor,
Did you try using the ecw (wizard for generating xml from excel or another source)?
Did you check system DSN is well configured?
There's nothing impossible!

Re: Excel export problem- no data

forgot to comment that, in my case, the first name that you enter to define the data area on excel don't work. I always define two names: aaaa and the correct name.
Then, I use the correct name to import data.
There's nothing impossible!
Gabor Radvanyi
Occasional Contributor

Re: Excel export problem- no data

Mariana!
Thx, I'll try it, hopefully it'll work

Gabor
//Add this to "OnDomLoad" event