Load data from excel

Hi everybody,

 I need to load data from excel to a class in cache, but this class has foreign key and property like AS ..... (other class). This class represent an item product that belongs to a line, the line blelongs a group and the group belongs a Category. 

Example:  

Property bdGrupo As Innova.CL.Core.BD.Operacion.Producto.Grupo;

Property bdLineaPR As Innova.CL.Core.BD.Operacion.Producto.Linea [ Cardinality = parent, Inverse = bdProductoCH ];

Property bdCategoriaProducto As Innova.CL.Core.BD.Operacion.Producto.Mantenedor.CategoriaProducto;

Property Portafolio As %Boolean [ InitialExpression = 1 ];

Property modelo As %String;

Property alianzaObligatoria As %String;

Property bdMembresia As Innova.CL.Core.BD.Operacion.Producto.Mantenedor.Membresia;

Property bdAlianza As Innova.CL.Core.BD.Operacion.Producto.Alianza;

Property bdMoneda As Innova.CL.Core.BD.Configuracion.Base.Moneda;

Property bdMetrica As Innova.CL.Core.BD.Operacion.Producto.Mantenedor.Metrica;

Property plazo As %String;

Property precioLista As %Integer;

Property Descripcion As %String(MAXLEN = 2500);

 Someone has an example of a  routine or method that loads data from CSV file with these characteristics.

Thanks a lot.

Jaqueline Krieger

Answers

There is a bunch of useful articles you find here searching for CSV.

I just picked one out of them: 
https://community.intersystems.com/post/how-read-csv-file-contents-objects-cache

In a Windows environment, you may also use an ODBC driver (DSN) and handle
Excel as what it is: As a Table using the SQLgateway as you would to with other DBs

You just have to take care to get the bits matching  (32/64). More details:
https://support.office.com/en-us/article/overview-of-connecting-to-importing-data-c0ad7aec-ff1a-4e48-9c21-dc18a102433f
 


 

Thanks for your information Robert, very useful.

What I was wondering if you are aware of a mechanism to directly read an xls and .xlsx file to get the information. CSV is fine as it can handled similar to text, but without modifying the file type, is there a direct mechanism to read the contents of the .xls and .xlsx file.

Your input would be appreciated.

first option:

get the schema behind *.XSLX and import it using  XML Schema wizard.
It's incredible huge covering all XLS features you know and also that you don't want to know.
several hundred classes. XML Schema Wizard
I gave up by lack of understanding where to start. I'm not strong with XLS features.

second option:

use ActiveX_64  to access *.XLS
in Windows, your Studio has an Activate wizard that creates access to your local installed DLL.
so you need to have EXCEL installed on your server.

The wizard generates still a remarkable number of classes but it is more comprehensive
using them you can do Scripting in EXCEL. Activate Wizard

be careful to really get the 64bit version of the DLL
 


 

About XLSX files, they are not one XML but a ZIP archive with arbitrary large number of xml files referencing each other.

For example there is a separate XML file for cells' text values.

It's possible to extract information from it but it seems a rather daunting task.

Here's a example of a direct manipulation of a Docx file.

There are several approaches available:

1. Automatic conversion of XSL(X) to CSV is possible using LibreOffice. Here's an article on how to do that.

2. Use Java library, such as Apache POI to traverse the cells and send results back. To send results back quickly and efficiently you can use String[] type and fill it with $lb built on Java side. String[] would become %ListOfDataTypes in Cache and $lb would remain $lb.

I've done some preliminary work but didn't get around to finishing or publishing it so here's a sample code that outputs list to console:

package isc.poi;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Row;

import java.util.ArrayList;
import java.util.Iterator;
import java.io.File;

import static org.apache.poi.ss.usermodel.CellType.*;

public class Main {

    public static String ROWSEPARATOR = "\t\t\t";

    public static void main(String[] args) {
        try {
            Test1();
        } catch (Exception ex) {
        }
    }

    public static String[] Test1 () throws Exception{
        ArrayList<String> list = new ArrayList<String>();

        File file = GetFile();
        Workbook workbook = WorkbookFactory.create(file);
        Iterator<Sheet> sheetIterator = workbook.sheetIterator();
        while(sheetIterator.hasNext()){
            Sheet sheet = sheetIterator.next();
            String name  = sheet.getSheetName();
            String value = null;

            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                Row row = (Row) rows.next();

                for(int i=0; i<row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    if (cell.getCellTypeEnum() == FORMULA) {
                        switch(cell.getCachedFormulaResultTypeEnum()) {
                            case NUMERIC:
                                value = String.valueOf(cell.getNumericCellValue());
                                break;
                            case STRING:
                                value = cell.getRichStringCellValue().getString();
                                break;
                        }
                    } else {
                        value = cell.toString();
                    }
                    list.add(value);
                    ///System.out.print("'" + cell.toString() + "'"+" ");
                }
                list.add(ROWSEPARATOR);
                System.out.println();
            }


            /*for (Row row : sheet) {
                for (Cell cell : row) {
                    System.out.print(cell.toString()+" ");
                    //int i=1;
                }
                System.out.println();
            }*/
        }
        String[] result = list.toArray(new String[list.size()]);
        return result;
    }

    public static File GetFile () {
        File file = new File("D:\\Cache\\POI\\Book1.xlsx");

        return file;
    }

    public static Object Test(Object in)
    {
        String[] ret = new String[1];
        ret[0] = "144";
        return ret;
    }
}