Tagged with Excel - Visokio Forums

Data Manager: Dates coming out wrong in a csv

JohnMR — Fri, 16 Jun 2017 09:58:03 -0400

Hi all,
I've created a file that has dates info. When I pull the dates in the UK format (dd/MM/yyyy) everything is fine but if I use the field organiser to change to US format (MM/dd/yyyy) the csv date column is half in dates (if you use show formula it's a number like 42045) but half is no dates (if you go to show formulas it's 02/10/2015). Why is this happening? It doesn't happen if I use UK dates.
Thanks,
John.

Scheduler Error: extra sheet exists when it opens the Excel file?

JamesPC — Fri, 04 Dec 2015 09:17:55 -0500

A batch file was meant to import a Excel file using the Omniscope reader then some logic gets applied and it ouputs, however the following error occurred. I think it's looking for another worksheet from Excel:

Chain action "omniscope" failure:
Error publishing block "Data file output 2"
com.visokio.util.jy: Error publishing block "Data file output 2"
at com.visokio.ent.actions.files.PublishDataManagerOutputBlocks.a(PublishDataManagerOutputBlocks.java:47)
at com.visokio.ent.actions.FileAction.a(FileAction.java:34)
at com.visokio.ent.actions.ChainAction.a(ChainAction.java:20)
at com.visokio.ent.actions.ChainAction.a(ChainAction.java:40)
at com.visokio.ent.actions.f.v(f.java:5)
at com.visokio.ent.actions.f.i(f.java:4)
at com.visokio.util.ut.run(ut.java:3)
at com.visokio.mxc.a(mxc.java:1)
at com.visokio.util.sy.a(sy.java:65)
at com.visokio.util.sy.a(sy.java:241)
at com.visokio.util.go.r(go.java:56)
at com.visokio.util.ph.s(ph.java:5)
at com.visokio.util.mh.o(mh.java:56)
at com.visokio.util.lh.l(lh.java:4)
at com.visokio.util.lh.a(lh.java:93)
at com.visokio.util.y3.run(y3.java:3)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: org.apache.poi.openxml4j.exceptions.PartAlreadyExistsException: A part with the name '/xl/worksheets/sheet2.xml' already exists : Packages shall not contain equivalent part names and package implementers shall neither create nor recognize packages with equivalent part names. [M1.12]
at org.apache.poi.openxml4j.opc.OPCPackage.createPart(OPCPackage.java:777)
at org.apache.poi.openxml4j.opc.OPCPackage.createPart(OPCPackage.java:741)
at org.apache.poi.POIXMLDocumentPart.createRelationship(POIXMLDocumentPart.java:373)
at org.apache.poi.POIXMLDocumentPart.createRelationship(POIXMLDocumentPart.java:357)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:661)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.createSheet(XSSFWorkbook.java:81)
at com.visokio.ms.th.d(th.java:42)
at com.visokio.ms.rh.a(rh.java:131)
at com.visokio.ms.rh.a(rh.java:65)
at com.visokio.ms.li.a(li.java:13)
at com.visokio.tvb.b(tvb.java:1)
at com.visokio.svb.a(svb.java:42)
at com.visokio.util.res.l.g(l.java:115)
at com.visokio.util.res.l.(l.java:145)
at com.visokio.svb.c(svb.java:29)
at com.visokio.svb.a(svb.java:57)
at com.visokio.svb.b(svb.java:34)
at com.visokio.svb.a(svb.java:31)
at com.visokio.mff.a(mff.java:28)
at com.visokio.mff.a(mff.java:14)
at com.visokio.mff.a(mff.java:7)
at com.visokio.lpd.a(lpd.java:147)
at com.visokio.lpd.a(lpd.java:144)
at com.visokio.amd.b(amd.java:22)
at com.visokio.zld.a(zld.java:23)
at com.visokio.datamanager.c6.run(c6.java:9)
at com.visokio.mxc.a(mxc.java:1)
at com.visokio.util.sy.a(sy.java:28)
at com.visokio.util.sy.a(sy.java:123)
at com.visokio.datamanager.hmb.a(hmb.java:79)
at com.visokio.ent.actions.files.PublishDataManagerOutputBlocks.a(PublishDataManagerOutputBlocks.java:29)
... 18 more

any ideas?

Import: Unable to use Excel to read data

Phillipa — Wed, 25 Nov 2015 08:50:57 -0500

Hi all,

I have just opened a Omniscope IOK file which loads an xlsx file into Omniscope.
I last worked on it a month ago and everything was fine. Now when I go in I get an error message:

Unable to use Excel to read data. Using Omniscope reader instead.

Can anyone help with this?
Phillipa

Export: Excel Sharepoint existing vs new worksheet file?

benjamin — Mon, 10 Aug 2015 06:33:52 -0400

Hi
I have a series of Omniscope files which run sequentially and export in each file multiple output blocks to varying location on Sharepoint. Each block is using "Replace Data" and "Excel Direct".

My problem is that when exporting the data it sometimes creates a new worksheet for the export instead of exporting to the excel file on Sharepoint.

For instance i have one Omniscope file and i have three (3) blocks exporting to the same Excel file on Sharepoint. These blocks sometimes export to the Excel file and sometimes create their own new Excel file.

I have looked at version history on Sharepoint and for instance the first two block publish ok and then the last block will just publish to a newly created Excel file and save it on Sharepoint.

Thanks, Ben

Batch Append: Importing Workbook Tabs from Varying Files in Folder

benjamin — Tue, 24 Mar 2015 04:55:56 -0400

Hi

I would like to batch append Excel files in a folder. Each Excel file has multiple tabs. I only want to import and append certain tabs...e.g. tabs named "abc" from each workbook file in the folder

How do I do this ?

Currently in the batch append source block I can only batch append based on names of Excel files rather then the name of the tab? Or can I specify the name of the file and the name of the tab as a extension somehow to overcome this?

Thanks, Ben

Outputs: Publishing to Excel 2003 - fonts issue?

Barcl — Thu, 19 Dec 2013 10:53:11 -0500

Omniscope Server 2.8 b1185
Microsoft Windows Server 2003 R2 - Server Pack 2
Microsoft Office Excel 2003 (11.8169.8172) SP3

Can you suggest what to do to resume working?

Thanks

Outputs: Publish to Excel - decimals converted to text?

Alexander — Thu, 29 Nov 2012 10:37:37 -0500

Hi - I have a workflow that publishes into an Excel file. In that Excel file I have set up a pivot-table. The source for this pivot-table is the data that Omniscope publishes on another Excel worksheet. Two of the published fields/columns should be numeric values. However, in the Excel file, the data is interpreted as text? The Excel pivot-table is not able to sum these values, and is giving all zero's as result.

Can you help? Thanks!

Sources: Excel-Decimals imported as integers using Excel Reader?

Alexander — Thu, 27 Mar 2014 08:04:09 -0400

Hi all,

I am encoutering a bug in Omniscope. My source is a group of .xls files. In the configuration of the "Group Import" I have chosen the Omniscope reader option, as there is no Office installed on our server.

In the Excel files there are decimals, but Excel shows them as integers. Omniscope reads them as integers, instead of decimals.

When importing into the same Omniscope file on my local machine, I use the Excel direct reader option. Then the decimals are imported into the DataManager source block. Therefore, I assume that the problem is the Omniscope Reader. I am available for a screen share.

(Using 2.8 b.1235)

Sources: Excel Error - Communicating with Application?

LukeWhiteley — Tue, 09 Jul 2013 06:36:07 -0400

Hi,
We are trying to open local .xlsx files in a DataManager and are receiving the following errors:

2.7:
Invoke of: Open
Source: Microsoft Excel
Description: Unable to get the Open property of the Workbooks class

2.8:
Unable to get the open property of the workbook class.

We only get this error when trying to read from a local file or Google Drive, files from our network drives open without any problems. We suspect this may be down to having concurrent installations of both 2.7 and 2.8 that look at the same 'Visokio' folder (see attached).

Thanks,
Luke

Mac: Import - Excel formula-calculated decimal values as integers?

Barcl — Thu, 07 Feb 2013 13:15:39 -0500

I need to read/import an Excel file in which few cells contain decimal numbers calculated using an Excel formula. Omniscope reads/imports the values contained in those cells as if they were integers... not taking into account decimal position? i.e. a value calculated and displayed in Excel as 58096.6 is received/imported as 580966?

Please see the attached Excel file and the Ominscope file that show how different cells are processed. If you type the decimal number in a cell Omniscope read it as a decimal number. If you use a formula in a cell to produce exactly the same value Omniscope believe the cell is an integer and produce wrong result.

Can you please advise on how to solve this issue? I cannot believe Omniscope cannot process calculated value correctly.
Probably I'm doing something wrong. Thanks

Installation: Need Excel installed on Server machine?

cgamolo — Tue, 23 Oct 2012 15:50:49 -0400

Hello - We currently do not have MS Office installed on the server were Omniscope sits. Is it required to have Office installed in order to read Excel files for import via Omniscope? What limitations can we expect if we use just the Omniscope reader? We aren't really looking to view the Excel file outside of Omniscope.

Thanks, Catherine

Scheduler: workaround for MS Office applications read/write issues

antonio — Fri, 08 Jun 2012 10:11:13 -0400

This only applies if you are running the Scheduler as a Windows service.

If you have Excel sources in your DataManager workflow, and the Reader type for the file block is Excel rather than different Omniscope Reader, then you may encounter the following problem (found in the Scheduler log):

Microsoft Office error
Error communicating with Application. Please try again. Check for solutions here: http://www.visokio.com/kb/scheduler-troubleshooting style='font-size: 9pt; color: #666666'>Invoke of: Open
Source: Microsoft Excel
Description: Microsoft Excel cannot access the file 'C:\Users\Visokio\Desktop\file.xlsx'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

Possible solution, suggested on the msdn forum, is to create the following folders:
- C:\Windows\SysWOW64\config\systemprofile\Desktop
- C:\Windows\System32\config\systemprofile\Desktop

If the above workaround does not work, then change the reader type to "Omniscope".

Summary and Granular level data

adam_varney — Mon, 12 Dec 2011 12:15:52 -0500

I am currently having a small issue with a data file that contain both summary and granular level data in it. In the Excel files the Summary level data hosts lots of blanks under fields such as Category then a sub category which is filled in for each one. However i need to fill in these blanks based on the main categoy. For example when i want to show a total score for the main category "washing liquids".

Sorry if this is not the best explanation for the issue but any help with gettining this data to the correct format would be appreciated

Thanks
Adam,

Batch Output: Excel document with formatting applied

adam_varney — Wed, 07 Sep 2011 11:07:42 -0400

I have been trying to output some data based over several different markets, where each market requires a separate excel file that has conditional formatting applied as a template.

Is it possible to batch output the file and filter it per market to the template excel file and then have it save as a different name so in the end i have a file for each market that has been filtered accordingly and the excel file has the conditional formatting in it?

I hope this makes sense and if any further clarification is required i can provide this.

How to import different sheets in one excel file

xwang — Thu, 14 Jul 2011 10:43:57 -0400

Hi, I am using Omniscope recently, and I think it's a really good visualization tools. However, I don't know how to import different sheets in one excel file each time. It seems that each time I can just import one sheet. Does anyone can help me solve this problem ?

Import: Excel file source - bug?

edtclarke — Mon, 13 Jun 2011 09:53:54 -0400

I have just been trying to import an Excel spreadsheet and have found some strange behaviour:

The sheet has 86 rows of data and 4061 columns which need to be transposed. When I import the sheet via the [File] block I get no error message but it only imports 500 fields (columns) and all 86 records (rows).

I then tried changing the [Reader] from 'Excel as CSV' to 'Excel direct'. This then gives me a warning message headed 'Row/column limit':
"It is not possible to import more than 676 columns and 1048576 records. Your excel document has 4061 columns and 86 records. Your data has been truncated to fit the limits"

So 3 questions really:
1 - Why is there a '676' limit when Excel 2010 allows 16384 columns?
2 - Why does it only import 500 columns NOT 676?
3 - Why do you not get a warning when using the 'Excel as CSV' option (also the same for the 'Omniscope' [Reader]?

Populate Field Notes via Files

Keesup — Fri, 13 May 2011 09:38:49 -0400

Descriptive Field Notes currently must be entered through the Data > Manage fields > Options menu for each individual field. Could field note generation be automated via uploading a .txt look up file in the field organiser?

In addition it would be useful to populate the Field Notes from Excel comments, perhaps as an option in the File Source Block.