Search code examples
javascriptsql-serverpentahopentaho-spoonspoon

Error importing tsv from Spoon to SQL Server

In Microsoft SQL Server I've created the following table with this sentence:

IF OBJECT_ID(N'dbo.STG_HICP', N'U') IS NOT NULL DROP TABLE dbo.STG_HICP;
CREATE TABLE dbo.STG_HICP(
    freq nvarchar(50)  not null,
unit nvarchar(100) not null,
    coicop varchar(50) not null,
    geo nvarchar(50) not null,
    period int not null,
    HICP numeric(12,2) null
)

Once I created this table in SQL, in Spoon I created a transformation like this:

Step 1:

Text file input:

(Preview data)

enter image description here

Step 2:

Split fields:

enter image description here

Step 3:

Table output:

When I run to import this tsv to SQL server it doesn't work and I get this message:

2023/05/18 18:12:06 - Spoon - Running transformation using the Kettle execution engine
2023/05/18 18:12:06 - Spoon - Transformation opened.
2023/05/18 18:12:06 - Spoon - Launching transformation [Transformation IN_HICP]...
2023/05/18 18:12:06 - Spoon - Started the transformation execution.
2023/05/18 18:12:06 - Transformation IN_HICP - Dispatching started for transformation [Transformation IN_HICP]
2023/05/18 18:12:06 - Table output.0 - Connected to database [CN_STG] (commit=1000)
2023/05/18 18:12:06 - Text file input.0 - Opening file: file:///F:/PRA2/Fuentes/prc_hicp_mv12r_tabular.tsv
2023/05/18 18:12:06 - Text file input.0 - Finished processing (I=14176, O=0, R=0, W=14175, U=1, E=0)
2023/05/18 18:12:07 - Table output.0 - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy) : Because of an error, this step can't continue:
2023/05/18 18:12:07 - Table output.0 - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy) : org.pentaho.di.core.exception.KettleException: 
2023/05/18 18:12:07 - Table output.0 - Error batch inserting rows into table [STG_HICP].
2023/05/18 18:12:07 - Table output.0 - Errors encountered (first 10):
2023/05/18 18:12:07 - Table output.0 - 
2023/05/18 18:12:07 - Table output.0 - 
2023/05/18 18:12:07 - Table output.0 - Error updating batch
2023/05/18 18:12:07 - Table output.0 - Sintaxis incorrecta cerca de '2020'.
2023/05/18 18:12:07 - Table output.0 - 
2023/05/18 18:12:07 - Table output.0 - 
2023/05/18 18:12:07 - Table output.0 -  at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:348)
2023/05/18 18:12:07 - Table output.0 -  at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:125)
2023/05/18 18:12:07 - Table output.0 -  at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2023/05/18 18:12:07 - Table output.0 -  at java.lang.Thread.run(Unknown Source)
2023/05/18 18:12:07 - Table output.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseBatchException: 
2023/05/18 18:12:07 - Table output.0 - Error updating batch
2023/05/18 18:12:07 - Table output.0 - Sintaxis incorrecta cerca de '2020'.
2023/05/18 18:12:07 - Table output.0 - 
2023/05/18 18:12:07 - Table output.0 -  at org.pentaho.di.core.database.Database.createKettleDatabaseBatchException(Database.java:1440)
2023/05/18 18:12:07 - Table output.0 -  at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:295)
2023/05/18 18:12:07 - Table output.0 -  ... 3 more
2023/05/18 18:12:07 - Table output.0 - Caused by: java.sql.BatchUpdateException: Sintaxis incorrecta cerca de '2020'.
2023/05/18 18:12:07 - Table output.0 -  at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:2088)
2023/05/18 18:12:07 - Table output.0 -  at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:291)
2023/05/18 18:12:07 - Table output.0 -  ... 3 more
2023/05/18 18:12:07 - Table output.0 - Finished processing (I=0, O=999, R=1000, W=0, U=0, E=1)
2023/05/18 18:12:07 - Transformation IN_HICP - Transformation detected one or more steps with errors.
2023/05/18 18:12:07 - Transformation IN_HICP - Transformation is killing the other steps!
2023/05/18 18:12:07 - Split fields.0 - Finished processing (I=0, O=0, R=11001, W=11001, U=0, E=0)
2023/05/18 18:12:07 - Transformation IN_HICP - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy) : Errors detected!
2023/05/18 18:12:07 - Spoon - The transformation has finished!!
2023/05/18 18:12:07 - Transformation IN_HICP - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy) : Errors detected!
2023/05/18 18:12:07 - Transformation IN_HICP - ERROR (version 9.2.0.0-290, build 9.2.0.0-290 from 2021-06-02 06.36.08 by buildguy) : Errors detected!

I think it could be caused by 'period int not null', but I don't know how to solve it.

Thank you very much for your help!

Solution

  • i think you need to use the row normalizer step first.

    Hope it helps you with pra2 ;)

    enter image description here