Problems importing data with Microsoft Excel Input

Asked

Viewed 303 times

1

I’m importing several spreadsheets into a database, but some of them are having input problems. I’ve even tried to transform the format and use CSV File Input, but the problem remains the same.

The data in Excel is like this:

inserir a descrição da imagem aqui

When I try to import, some columns "change places" and the data of one goes to another:

inserir a descrição da imagem aqui

In this case, for example, the data in the column camada_nome ended up in the spine profund_inf Does anyone know why this occurs? There is a solution?

1 answer

1


If you are using Step Table Output to perform the Insert, remember that if you do not specify where each column should be inserted, Pentaho will behave assuming that the received table is in the same order as the Database table, for this reason the data of the third input column (nameplate) had been inserted in the third column of the table (profund_inf).

Solution 1(the easiest) - Match the input table layout to the database table layout. This can be done through the 'Select Values' step. Just mirror the layouts, if the input table does not have all the columns of the database table, you can create the columns with empty values with the step 'Add constants'.

Solution 2 (not so hard) - In step Table output, in the 'Database Fields' tab you can specify which data will be inserted into which database table fields (such as a de-stop). It is good practice to make the names of both tables identical so there are no major problems. inserir a descrição da imagem aqui

  • 1

    The problem is that I was deleting some columns inside the Microsoft Excel Input step and this causes Kettle to move the columns, generating the error of the exchanged columns. Now it’s working fine, thank you very much!

  • 1

    Correct, Microsoft Excel input columns have to be the same as the file layout, if you need to remove columns, or reorder them, use the Select Values step right after. Also remembering that adding extra columns to the right of the layout does not influence, the problem is adding columns to the left of the layout, which causes a new organization of the data, and consequently the error that was finding happens.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.