Hitachi Vantara Pentaho Community Forums
Results 1 to 12 of 12

Thread: excel to database using kettle API

  1. #1

    Default excel to database using kettle API

    hi,

    i have seen sample code for database to database transformation using kettle API.but i didn't get any idea how to load data from excel file.
    if you provide any details or sample code it will be useful


    thanks,
    bhuvana

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Just replace the calls to the database Meta objects to calls to an ExcelInputMeta object, and you're good to go.

    For the moment there are no real examples availables using Excel input via the Java API.

    Regards,
    Sven

  3. #3

    Default

    hi sboden,

    my code:

    package net;


    import java.io.DataOutputStream;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.util.ArrayList;

    import be.ibridge.kettle.core.Const;
    import be.ibridge.kettle.core.LogWriter;
    import be.ibridge.kettle.core.NotePadMeta;
    import be.ibridge.kettle.core.database.Database;
    import be.ibridge.kettle.core.database.DatabaseMeta;
    import be.ibridge.kettle.core.exception.KettleException;
    import be.ibridge.kettle.core.util.EnvUtil;
    import be.ibridge.kettle.trans.StepLoader;
    import be.ibridge.kettle.trans.Trans;
    import be.ibridge.kettle.trans.TransHopMeta;
    import be.ibridge.kettle.trans.TransMeta;
    import be.ibridge.kettle.trans.step.StepMeta;
    import be.ibridge.kettle.trans.step.StepMetaInterface;
    import be.ibridge.kettle.trans.step.excelinput.ExcelInput;
    import be.ibridge.kettle.trans.step.excelinput.ExcelInputData;
    import be.ibridge.kettle.trans.step.excelinput.ExcelInputField;
    import be.ibridge.kettle.trans.step.excelinput.ExcelInputMeta;
    import be.ibridge.kettle.trans.step.selectvalues.SelectValuesMeta;
    import be.ibridge.kettle.trans.step.tableinput.TableInputMeta;
    import be.ibridge.kettle.trans.step.tableoutput.TableOutputMeta;


    public class TestExcel
    {
    public static final String[] databasesXML = {

    "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
    "<connection>" +
    "<name>target</name>" +
    "<server>localhost</server>" +
    "<type>MYSQL</type>" +
    "<access>Native</access>" +
    "<database>test</database>" +
    "<port>3306</port>" +
    "<username>root</username>" +
    "<password>dbadmin</password>" +
    "</connection>"
    };

    public static final TransMeta buildCopyTable(String transformationName, String targetDatabaseName, String targetTableName, String[] targetFields) throws KettleException
    {
    EnvUtil.environmentInit();

    try
    {

    ExcelInputMeta excelMeta = new ExcelInputMeta();
    TransMeta transMeta = new TransMeta();
    transMeta.setName(transformationName);
    String[] list = new String[1];
    list[0] = "D:\\Documents and Settings\\rammohan\\Desktop\\test.xls";
    String[] sheet = new String[1];
    sheet[0] = "sheet1";
    excelMeta.setFileName(list);
    excelMeta.setSheetName(sheet);
    int[] row = new int[1];
    row[0] = 0;
    excelMeta.setStartRow(row);
    int[] column = new int[1];
    column[0] = 0;
    excelMeta.setStartColumn(column);
    ExcelInputField[] excelField = new ExcelInputField[2];
    excelField[0].setName("Name");
    excelField[1].setName("Age");
    excelMeta.setField(excelField);


    for (int i=0;i<databasesXML.length;i++)
    {
    DatabaseMeta databaseMeta = new DatabaseMeta(databasesXML[i]);

    System.out.println("databasemets value in ----------->"+databaseMeta.getDatabaseName());
    transMeta.addDatabase(databaseMeta);

    }

    DatabaseMeta targetDBInfo = transMeta.findDatabase(targetDatabaseName);

    String fromstepname = "read from [" + excelMeta + "]";
    StepLoader steploader = StepLoader.getInstance();
    String fromstepid = steploader.getStepPluginID(excelMeta);
    StepMeta fromstep = new StepMeta(fromstepid, fromstepname, (StepMetaInterface) excelMeta);
    fromstep.setLocation(150, 100);
    fromstep.setDraw(true);
    transMeta.addStep(fromstep);

    String tostepname = "write to [" + targetTableName + "]";
    TableOutputMeta toi = new TableOutputMeta();
    toi.setDatabaseMeta(targetDBInfo);
    toi.setTablename(targetTableName);
    toi.setCommitSize(200);
    toi.setTruncateTable(true);

    String tostepid = steploader.getStepPluginID(toi);
    StepMeta tostep = new StepMeta(tostepid, tostepname, (StepMetaInterface) toi);
    tostep.setLocation(550, 100);
    tostep.setDraw(true);
    tostep.setDescription("Write information to table [" + targetTableName + "] on database [" + targetDBInfo + "]");
    transMeta.addStep(tostep);


    TransHopMeta hi = new TransHopMeta(fromstep, tostep);
    transMeta.addTransHop(hi);

    return transMeta;
    }
    catch (Exception e)
    {
    throw new KettleException("An unexpected error occurred creating the new transformation", e);
    }
    }


    public static void main(String[] args) throws Exception
    {
    EnvUtil.environmentInit();

    LogWriter log = LogWriter.getInstance("TransBuilder.log", true, LogWriter.LOG_LEVEL_DETAILED);

    StepLoader stloader = StepLoader.getInstance();
    if (!stloader.read())
    {
    log.logError("TransBuilder", "Error loading Kettle steps & plugins... stopping now!");
    return;
    }


    String fileName = "NewTrans.xml";
    String transformationName = "Test Transformation";

    String targetDatabaseName = "target";
    String targetTableName = "person";
    String targetFields[] = {
    "Name",
    "Age",
    };

    TransMeta transMeta = TestExcel.buildCopyTable(
    transformationName,
    targetDatabaseName,
    targetTableName,
    targetFields
    );

    String xml = transMeta.getXML();
    DataOutputStream dos = new DataOutputStream(new FileOutputStream(new File(fileName)));
    dos.write(xml.getBytes("UTF-8"));
    dos.close();
    System.out.println("Saved transformation to file: "+fileName);

    String sql = transMeta.getSQLStatementsString();

    Database targetDatabase = new Database(transMeta.findDatabase(targetDatabaseName));
    targetDatabase.connect();
    targetDatabase.execStatements(sql);

    Trans trans = new Trans(log, transMeta);
    trans.execute(null);
    trans.waitUntilFinished();

    }


    }

    if i run this code it shows error as:

    Exception in thread "main" be.ibridge.kettle.core.exception.KettleException:
    An unexpected error occurred creating the new transformation
    at net.TestExcel.main (TestExcel.java:240)
    at net.TestExcel.buildCopyTable (TestExcel.java:94)

    at net.TestExcel.buildCopyTable(TestExcel.java:200)
    at net.TestExcel.main(TestExcel.java:240)
    Caused by: java.lang.NullPointerException
    at net.TestExcel.buildCopyTable(TestExcel.java:94)
    ... 1 more



    the way i proceeded is correct or not, where i made mistake.guide me i need your help.

    thanks & regards,
    bhuvana

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    Looks ok at first sight (for v2.5)... what's the full stack trace?

    Regards,
    Sven

  5. #5

    Default

    what i send was the full stack trace.

    now again i made changes to my code

    ExcelInputField[] excelField = new ExcelInputField[2];
    excelField[0].setName("Name");
    excelField[1].setName("Age");
    excelMeta.setField(
    excelField);

    as

    ExcelInputField excelField = new ExcelInputField(sourceFields[0],0,5);
    ExcelInputField excelField1 = new ExcelInputField(sourceFields[1],0,5);
    ExcelInputField excelfields[] ={excelField,excelField1};
    excelMeta.setField( excelfields);

    then new error as

    2008-01-09 16:27:22,890 INFO Test Transformation - Test Transformation - Dispatching started for transformation [Test Transformation]
    2008-01-09 16:27:22,890 INFO Test Transformation - Test Transformation - This is not a replay transformation
    2008-01-09 16:27:22,937 INFO Test Transformation - Test Transformation - This transformation can be replayed with replay date: 2008/01/09 16:27:22
    2008-01-09 16:27:22,937 INFO Test Transformation - Test Transformation - Initialising 2 steps...
    2008-01-09 16:27:23,000 ERROR read from [[Ljava.lang.String;@1034bb5] - read from [[Ljava.lang.String;@1034bb5] - Error initializing step [read from [[Ljava.lang.String;@1034bb5]]
    2008-01-09 16:27:23,031 INFO write to [person].0 - write to [person].0 - Connected to database [target] (commit=200)
    2008-01-09 16:27:23,062 ERROR read from [[Ljava.lang.String;@1034bb5] - read from [[Ljava.lang.String;@1034bb5] - java.lang.NullPointerException
    at be.ibridge.kettle.core.util.StringUtil.environmentSubstitute(StringUtil.java:163)
    at be.ibridge.kettle.trans.step.fileinput.FileInputList.createFileList(FileInputList.java:78)
    at be.ibridge.kettle.trans.step.fileinput.FileInputList.createFileList(FileInputList.java:69)
    at be.ibridge.kettle.trans.step.excelinput.ExcelInputMeta.getFileList(ExcelInputMeta.java:915)
    at be.ibridge.kettle.trans.step.excelinput.ExcelInput.init(ExcelInput.java:648)
    at be.ibridge.kettle.trans.step.StepInitThread.run(StepInitThread.java:45)
    at java.lang.Thread.run(Unknown Source)

    2008-01-09 16:27:23,109 ERROR Test Transformation - Test Transformation - Step [read from [[Ljava.lang.String;@1034bb5].0] failed to initialize!
    2008-01-09 16:27:23,156 ERROR Test Transformation - Test Transformation - We failed to initialize at least one step. Execution can not begin!
    2008-01-09 16:27:23,218 INFO Test Transformation - Test Transformation - Transformation ended.

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    Put a breakpoint on be.ibridge.kettle.trans.step.excelinput.ExcelInput Meta.getFileList and start debugging from there.

    Regards,
    Sven

  7. #7

    Default

    i gave breakpoint for getFileList()
    it's showing

    Exception in thread "main" be.ibridge.kettle.core.exception.KettleException:
    An unexpected error occurred creating the new transformation
    at net.TestExcel.main (TestExcel.java:166)
    at net.TestExcel.buildCopyTable (TestExcel.java:87)
    at be.ibridge.kettle.trans.step.excelinput.ExcelInputMeta.getFileList (ExcelInputMeta.java:915)
    at be.ibridge.kettle.trans.step.fileinput.FileInputList.createFileList (FileInputList.java:69)
    at be.ibridge.kettle.trans.step.fileinput.FileInputList.createFileList (FileInputList.java:78)
    at be.ibridge.kettle.core.util.StringUtil.environmentSubstitute (StringUtil.java:163)

    at net.TestExcel.buildCopyTable(TestExcel.java:131)
    at net.TestExcel.main(TestExcel.java:166)
    Caused by: java.lang.NullPointerException
    at be.ibridge.kettle.core.util.StringUtil.environmentSubstitute(StringUtil.java:163)
    at be.ibridge.kettle.trans.step.fileinput.FileInputList.createFileList(FileInputList.java:78)
    at be.ibridge.kettle.trans.step.fileinput.FileInputList.createFileList(FileInputList.java:69)
    at be.ibridge.kettle.trans.step.excelinput.ExcelInputMeta.getFileList(ExcelInputMeta.java:915)
    at net.TestExcel.buildCopyTable(TestExcel.java:87)


    it's not taking file name. but i already set filename in excel inputmeta.
    is there any other way to set(using fileinputlist) or we have to add filename to transmeta


    and one more thing transmeta.getXML() also showing null value.

  8. #8

    Default

    after setting values for filemask and filerequired
    log changed to


    2008-01-11 18:37:35,812 INFO Test Transformation - Test Transformation - Dispatching started for transformation [Test Transformation]
    2008-01-11 18:37:35,828 INFO Test Transformation - Test Transformation - This is not a replay transformation
    2008-01-11 18:37:35,843 INFO Test Transformation - Test Transformation - This transformation can be replayed with replay date: 2008/01/11 18:37:35
    2008-01-11 18:37:35,843 INFO Test Transformation - Test Transformation - Initialising 2 steps...
    2008-01-11 18:37:36,000 INFO write to [person].0 - write to [person].0 - Connected to database [target] (commit=200)
    2008/01/11 18:37:36:140 IST [INFO] DefaultFileReplicator - Using "D:\DOCUME~1\rammohan\LOCALS~1\Temp\vfs_cache" as temporary files store.
    2008-01-11 18:37:36,531 ERROR FileInputList - FileInputList - org.apache.commons.vfs.FileSystemException: Could not list the contents of "file:///C:/test.xls" because it is not a folder.
    at org.apache.commons.vfs.provider.AbstractFileObject.getChildren(AbstractFileObject.java:527)
    at be.ibridge.kettle.trans.step.fileinput.FileInputList.createFileList(FileInputList.java:140)
    at be.ibridge.kettle.trans.step.fileinput.FileInputList.createFileList(FileInputList.java:69)
    at be.ibridge.kettle.trans.step.excelinput.ExcelInputMeta.getFileList(ExcelInputMeta.java:915)
    at be.ibridge.kettle.trans.step.excelinput.ExcelInput.init(ExcelInput.java:648)
    at be.ibridge.kettle.trans.step.StepInitThread.run(StepInitThread.java:45)
    at java.lang.Thread.run(Unknown Source)

    2008-01-11 18:37:36,546 ERROR read from [Excel Input].0 - read from [Excel Input].0 - No file(s) specified! Stop processing.
    2008-01-11 18:37:36,546 ERROR read from [Excel Input] - read from [Excel Input] - Error initializing step [read from [Excel Input]]
    2008-01-11 18:37:36,546 ERROR Test Transformation - Test Transformation - Step [read from [Excel Input].0] failed to initialize!
    2008-01-11 18:37:36,546 ERROR Test Transformation - Test Transformation - We failed to initialize at least one step. Execution can not begin!
    2008-01-11 18:37:36,546 ERROR Test Transformation - Test Transformation - Errors detected!
    2008-01-11 18:37:36,593 INFO Test Transformation - Test Transformation - Transformation detected 1 steps with errors!
    2008-01-11 18:37:36,593 INFO Test Transformation - Test Transformation - Transformation is killing the other steps!
    2008-01-11 18:37:36,593 INFO Test Transformation - Test Transformation - Looking at step: read from [Excel Input]
    2008-01-11 18:37:36,593 INFO Test Transformation - Test Transformation - Looking at step: write to [person]


    then i did modifications in setting file name using fileinputlist createfilelist method

    as

    FileInputList fil = new FileInputList();
    FileInputList list = fil.createFileList(sourceFileName, sourceFileMask, sourceFileRequired);
    String slist[] = list.getFileStrings();
    excelMeta.setFileName(slist);

    for this log as

    2008-01-11 18:44:55,546 INFO Test Transformation - Test Transformation - Dispatching started for transformation [Test Transformation]
    2008-01-11 18:44:55,562 INFO Test Transformation - Test Transformation - This is not a replay transformation
    2008-01-11 18:44:55,578 INFO Test Transformation - Test Transformation - This transformation can be replayed with replay date: 2008/01/11 18:44:55
    2008-01-11 18:44:55,578 INFO Test Transformation - Test Transformation - Initialising 2 steps...
    2008-01-11 18:44:55,671 INFO write to [person].0 - write to [person].0 - Connected to database [target] (commit=200)
    2008-01-11 18:44:55,828 INFO read from [Excel Input].0 - read from [Excel Input].0 - Starting to run...
    2008-01-11 18:44:55,859 INFO write to [person].0 - write to [person].0 - Starting to run...
    Exception in thread "read from [Excel Input].0 (Thread-1)" java.lang.NoSuchMethodError: jxl.Workbook.getWorkbook(Ljava/io/InputStream;Ljxl/WorkbookSettingsLjxl/Workbook;
    at be.ibridge.kettle.trans.step.excelinput.ExcelInput.getRowFromWorkbooks(ExcelInput.java:481)
    at be.ibridge.kettle.trans.step.excelinput.ExcelInput.processRow(ExcelInput.java:387)
    at be.ibridge.kettle.trans.step.excelinput.ExcelInput.run(ExcelInput.java:711)
    2008-01-11 18:44:55,890 INFO read from [Excel Input].0 - read from [Excel Input].0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)



    at both time i got transmeta.getXML() value.


  9. #9
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Could not list the contents of "file:///C:/test.xls" because it is not a folder.
    The file mask should be null if you want to read a file.
    If you do specify a wild-card, the filename parameter should be folder.

    Matt

  10. #10

    Default

    i specified in both way
    filename = "D:\\Documents and Settings\\rammohan\\Desktop\\test.xls"
    filemask = ""

    and
    filename = "D:\\Documents and Settings\\rammohan\\Desktop\\"
    filemask = "test.xls"


    for both it shows bug as

    2008-01-15 11:13:04,616 INFO Test Transformation - Test Transformation - Dispatching started for transformation [Test Transformation]
    2008-01-15 11:13:04,632 INFO Test Transformation - Test Transformation - This is not a replay transformation
    2008-01-15 11:13:04,648 INFO Test Transformation - Test Transformation - This transformation can be replayed with replay date: 2008/01/15 11:13:04
    2008-01-15 11:13:04,648 INFO Test Transformation - Test Transformation - Initialising 2 steps...
    2008/01/15 11:13:04:819 IST [INFO] DefaultFileReplicator - Using "D:\DOCUME~1\rammohan\LOCALS~1\Temp\vfs_cache" as temporary files store.
    2008-01-15 11:13:04,960 INFO write to [person].0 - write to [person].0 - Connected to database [target] (commit=200)
    2008-01-15 11:13:05,273 INFO read from [Excel Input].0 - read from [Excel Input].0 - Starting to run...
    2008-01-15 11:13:05,288 INFO write to [person].0 - write to [person].0 - Starting to run...
    2008-01-15 11:13:05,304 INFO read from [Excel Input].0 - read from [Excel Input].0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=0)
    Exception in thread "read from [Excel Input].0 (Thread-0)" java.lang.NoSuchMethodError: jxl.Workbook.getWorkbook(Ljava/io/InputStream;Ljxl/WorkbookSettingsLjxl/Workbook;
    at be.ibridge.kettle.trans.step.excelinput.ExcelInput.getRowFromWorkbooks(ExcelInput.java:481)
    at be.ibridge.kettle.trans.step.excelinput.ExcelInput.processRow(ExcelInput.java:387)
    at be.ibridge.kettle.trans.step.excelinput.ExcelInput.run(ExcelInput.java:711)

  11. #11
    Join Date
    Mar 2008
    Posts
    9

    Default excel to database using kettle API

    Hi,
    We have a requirement to load Excel file into MySQL database for generating Pentaho Graphs. If you have any solution for loading Excel file. please let us know the APIs that are to be used.

    Appreciate, if you could send the sample program.

    Thanks,
    Raj

  12. #12
    Join Date
    Aug 2016
    Posts
    10

    Default

    XML to database using kettle API

    hi,

    i have seen sample code for database to database transformation using kettle API.but i didn't get any idea how to load data from XML file.
    if you provide any details or sample code it will be useful


    thanks in advance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.