Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Import From Excel File With Merged Cells

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Import From Excel File With Merged Cells
 Login/Join
 
Virtuoso
posted
I have a data source that is non-negotiable -- an ancient system that spits out an Excel spreadsheet with the exact data I want.

So . . . I can't build a master file for it. It has merged cells and attempts to connect to it result in only a couple of my fields being available.

ODBC is being whiney about connecting at all . . . get an error, might be that 64-bit/32-bit thing that Microsoft still hasn't cleared up.

How are all you folks out there making connections to data in Excel spreadsheets? I need data migrator to make a connect to this file weekly and pull the data from it. I do this for csv files super-easy, but this excel file is tripping me up. I think the Merged cells are part of the problem.

This message has been edited. Last edited by: FP Mod Chuck,



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Virtuoso
posted Hide Post
John

There is now an EXCEL direct connection you don't need ODBC anymore. It shows up as one of the adapter types

This message has been edited. Last edited by: FP Mod Chuck,


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Master
posted Hide Post
Not sure what version of WF you are using, but the docs for 8202M are here: Using the Adapter for Excel (via Direct Retrieval)


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Virtuoso
posted Hide Post
Thank you for the documentation link!

I've tried using the Excel adapter and it works great on my incoming Excel file IF I remove the merged cells from it first. With the merged cells in the original version I only get the first five columns when there's seven, and only two of them (4 and 5) are populated. No clue why 6 and 7 disappear completely.

This is in a spreadsheet where the first three columns are Merged columns, i.e., one entry covers multiple rows. The first three columns get that property internal thing added --

 
FILENAME=CUSTOMER_SERVICE_CENTER_ORIGINAL, SUFFIX=DIREXCEL,
 DATASET=call_center/Customer_Service_Center.xls, $
  SEGMENT=CUSTOMER_SERVICE_CENTER_ORIGINAL, SEGTYPE=S0, $
    FIELDNAME=FIELD_1, ALIAS=FIELD_1, USAGE=A1V, ACTUAL=A1V,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      TITLE='FIELD_1', $
    FIELDNAME=FIELD_2, ALIAS=FIELD_2, USAGE=A1V, ACTUAL=A1V,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      TITLE='FIELD_2', $
    FIELDNAME=FIELD_3, ALIAS=FIELD_3, USAGE=A1V, ACTUAL=A1V,
      MISSING=ON, ACCESS_PROPERTY=(INTERNAL),
      TITLE='FIELD_3', $
    FIELDNAME=FIELD_4, ALIAS=FIELD_4, USAGE=I8, ACTUAL=A11V,
      MISSING=ON,
      TITLE='FIELD_4', $
    FIELDNAME=FIELD_5, ALIAS=FIELD_5, USAGE=I8, ACTUAL=A11V,
      MISSING=ON,
      TITLE='FIELD_5', $
 


Removing that from the .mas file changes nothing.

So, I tried to hoodwink the Synonym builder by deleting all but the first row of date in the Excel sheet before running against it. Success! A plain, straightforward synonym with all seven fields in it. Data types are perfect too. But the minute I put the original Excel file in the Dataset location I only get fields 4 and 5.

I'm open to creative solutions. If I can pull from this critter as all the columns in one long string per line I'll make that work. Anyone have any ideas?



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Virtuoso
posted Hide Post
John

I know you don't like to open cases but you may need to for this one


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Virtuoso
posted Hide Post
I might. This one has time to develop, not a rush job.

I've gotten very close in the last hour. I've identified it's the final line in the file, the Grand Total line that is really causing the problem. When I delete that line out of the spreadsheet I can finagle a procedure that lets me pull the data I need. So I just need to work that one problem out. I'll post if I can clear this final issue.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Virtuoso
posted Hide Post
John

Just an idea and I know you have been doing this forever but.. If it is the total line you can let WebFOCUS do that part as part of the report


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Virtuoso
posted Hide Post
Yep, I just need that puppy out of the way so I can process the file. There's no way to modify the format of the incoming file (I'm consuming it via Data Migrator) so I only need the detail lines.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
Master
posted Hide Post
How exactly is the spreadsheet laid out? Are the merged cells only in the header row? Maybe something like this?

A B C D E F G
1 FIELD_1 FIELD_2 FIELD_3 FIELD_4 FIELD_5
2 DATA_1 DATA_2 DATA_3 DATA_4 DATA_5 DATA_6 DATA_7
3 DATA_1 DATA_2 DATA_3 DATA_4 DATA_5 DATA_6 DATA_7

Or, is there another row below that could uniquely identify each field (column)? Maybe something like this:

A B C D E F G
1 FIELD_1 FIELD_2 FIELD_3 FIELD_4 FIELD_5
2 TITLE_1 TITLE_2 TITLE_3 TITLE_4 TITLE_5 TITLE_6 TITLE_7
3 DATA_1 DATA_2 DATA_3 DATA_4 DATA_5 DATA_6 DATA_7
4 DATA_1 DATA_2 DATA_3 DATA_4 DATA_5 DATA_6 DATA_7

This message has been edited. Last edited by: Hallway,


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Virtuoso
posted Hide Post
Alright so I think I'm rightly hosed now.

I've managed to create a proper master file for the spreadsheet by only including the header row and one row of detail data underneath that. That gave me all my fields in the master file for use, and they populate correctly both through Sample Data and creating a rudimentary report. So that takes the merged fields out of the picture. I have my master, when I put the original file in place it works and I get all my data.

Except. I've been eyeballing the last line, which seems to still hose the select.

When I removed the last line and saved, I got success.

Then I just changed the value in the first column in the last line and saved, and got success.

Then I replaced the value in the first column of the last line with the exact same value and saved, and got success.

Then I just opened the spreadsheet and saved, and got success.

So . . . I'm beginning to suspect that the version of the excel file that is being exported by the source system is maybe not quite exactly right, and that's stinging me. When I open the file in Excel and save it, that flaw is corrected and I can run successfully.

I may be able to do that with an external tool. These files arrive via email and I vector them off into folders on a drive via macros, I may be able to do an open and save in the email's macro language.

Took me a day to walk this all the way down to this point.



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Import From Excel File With Merged Cells

Copyright © 1996-2020 Information Builders