I'm having a problem with my field formats on an Excel table synonym. One of the fields is alphanumeric, but the synonym generated sees it as D8, so that when I run a query, it won't return any values that have alphas, dashes, etc in them. Apparently it does not matter how you format the cells in the Excel spreadsheet, Webfocus will decide for you? I tried manually changing the field formats in the .mas file, to no avail. Any ideas?

Hi Jen,

Can you please make sure that you prepare the Excel worksheet correctly?

The following topic from Information Builders' online documents may be of interest:

How to create a synonym for an ODBC to an EXCEL RANGE name

Hope this helps.

Cheers,

Kerry

My problem is not creating the synonym, it's getting the right data back when I run a query against it. I get all the data except for this one field when the value has letter or dash in it. Webfocus returns a null.

Hi Jen,

We have not heard of any issue like this. It may be possible to reproduce the issue with the Excel file.

Have you opened a case with Information Builders' Customer Support Services? If not, I would strongly suggest you to do so as we will need more details to test and reproduce the issue, i.e. the Excel file. To open a case, please either call at 1-800-736-6130, or access the online system InfoResponse at http://techsupport.ibi.com. Here is a list of information to be ready when you call: http://techsupport.ibi.com/before_you_call.jsp.

Hope this helps.

Cheers,

Kerry

Jen,

I know you said you changed the master manually, did you try making the field an Alpha? Usage and Format. The other thing I would try is add an APP PREPENDPATH dir
to your code to make sure an old master is not being read.

Just a thought!

Carol

Carol - yup, tried that already, doesn't work.

Kerry - I submitted my case. Thanks.

On the off chance that anyone else was interested in the outcome of this...

Tech support sent me this link...

http://www.idude.net/excel/articles/using_excel_file_datasources.asp

The solution was to set the data type in Excel to Text (which it already was), re-enter the data in the cells (really? why? can we fix that, Mr Gates?), and finally, sort the column so that values with alphas appear in the first 8 rows (some ODBC issue there)

Voila! WF now returns ALL my data. Yippee!

Hi Jen,
I too have had the same problems as you and cannot my read my Excel data as expected. I have a column of numbers (bank account numbers) up to 34 chars. long but can't get Webfocus to treat these as text despite my master showing A34 and the Excel spreadsheet showing the cells as Text. What's really strange is that some account numbers seem to be read ok and some are displayed as a '.'. I tried to reference your link
http://www.idude.net/excel/articles/using_excel_file_datasources.asp

but like many US websites was full of adverts and marketing and not much substance. Any suggestions gratefully received ? Surely we don't have to re-enter all our data ?

Not sure what your comment about US websites means, but obviously the link is no longer valid. My guess would be the owner of the site didn't renew his domain name. Too bad, the article was actually quite helpful. But yes, the answer was to re-enter the data in the spreadsheet, and sort the column so that values with alphas appear in the first 8 rows.

Hi Jen, Thanks for your prompt reply. My data is all numeric characters but I just want them to be treated as alphanumeric ie. text. If they were a mixture of alphabetic and numeric chars. and you had more than 8 rows what would you do then ? Sounds very hit and miss. I will try the re-enter option as a test but typically we have hundreds of rows so re-typing in the future is not really an option. By the way if you have a copy of the article that is now no longer available I would love to see it. Is there also a person at IBI that you could possibly point me to should I need to follow up ?

Jen, this was what I call a great post. I have wrestled with these issues and have long wondered how others deal with them. It's like sometimes you have to attempt to "fool" the system. Although this thread has been quiet for a month now, my guess in reference to Ian's question on "8 rows" mentioned above is that it could be considered an arbitrary but sufficient number of records to insure you will end up with an alphanumeric field(?) If anyone knows differently, and 8 is a significant number in this case, please tell us.
Bill S

Hi Bill,
Excel has a lot to answer for !! I am unsure about the 8 rows but I think the key to the whole thing is to ensure the users key the data in correctly in the first place.
By that I mean if they are keying in a bunch of numbers as bank accounts details ensure the format is set to Text before you start.
I think Excel has lots of hidden algorithms behind the scenes trying to 'best guess' what type of data you are entering.
Alas it causes grief !!
Anyway I got it working but I had to do lots of 'fiddling' with the spreadsheet beforehand.

Hi Ian,
Great hearing from you. Just to follow up a little further on what you said. I think you are right. One thing that I was working on during the last couple of days was a repair job on the old Focus Windows Desktop Version 4.3.6. The reason for the repair was that the Excel feed, which formerly came from a People Soft 7.6.2 application, was upgraded to 8.8, now web-based. Under the older application when using this data with ODBC and a synonymn file, the alpha fields were much more real or should I say "tailor fit" like A6, A5 etc. Now when I bring this data through the Master has A255 for every last alpha field (and D20.2 for numeric). So in this case I know it has to be the Excel file held suspect.

Incidentally on this latter subject, I have read on several different Focal Point posts about editing the master in such cases. I tried it here but got a nasty SQL message so had to revert back. Don't know if there is something else that needs to be done or if the problem has to do with a difference between Desktop 4.3.6 and full WebFOCUS versions 5.x or 7.x. Have used ODBC very little so far on the more recent WF releases.
Bill Scott