Problems importing data with Microsoft Excel Input

Question

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:

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

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?

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.