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,

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,

Not sure what version of WF you are using, but the docs for 8202M are here: Using the Adapter for Excel (via Direct Retrieval)

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?

John

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

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.

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

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.

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,

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.