Time for action—inserting new products or updating existent ones

So far, you created the Jigsaw Puzzles database and loaded a list of puzzles manufacturers. It's time to start loading information about the products you will sell— puzzles.

Suppose, in order to show you what they are selling, the suppliers provide you with the lists of products made by the manufacturers themselves. Fortunately, they don't give you the lists in the form of papers, but they give you either plain files or spreadsheets. In this tutorial, you will take the list of products offered by the manufacturer Classic DeLuxe and load it into the puzzles table.

  1. From the Packt website, download the sample lists of products.
  2. Open Spoon and create a new transformation.
  3. Add a Text file input step and configure it to read the productlist_LUX_200908.txt file.

    Pay attention to the each field. It's the price of the product and must be configured as a Number with format $0.00.

  4. Preview the file. You should see the following:
    Time for action—inserting new products or updating existent ones
  5. In the Selected Files grid, replace the text productlist_LUX_200908.txt by ${PRODUCTLISTFILE}.
  6. Click on OK.
  7. After the Text file input step, add an Add constants step.
  8. Use it to add a String constant named man_code with value LUX.
  9. From the Output category of steps, drag an Insert/Update step to the canvas. Create a hop from the Add constants step to this new step.
  10. Double-click the step. Select js as Connection. As Target table, browse and select products. In the upper grid of the window, add the conditions pro_code = prodcod and man_code = man_code. Click the Edit mapping button. The mapping dialog window shows up.
  11. Under the Source fields list, click on prodcod, under the Target fields list click on pro_code, and then click the Add button. Again, under the Source fields list click on title, under the Target fields list click on pro_name, and then finally click Add. Proceed with the mapping until you get the following:
    Time for action—inserting new products or updating existent ones
  12. Click OK.
  13. Fill the Update column for the price row with the value Y. Fill the rest of the column with the value N. The following is how the final grid looks like:
    Time for action—inserting new products or updating existent ones
  14. After the Insert/Update step, add a Write to log step.
  15. Right-click the Insert/Update step and select Define error handling....
  16. Fill the error handling settings window just as you did in the previous tutorial.
  17. Save the transformation and run it by pressing the F9 key.
  18. In the settings window, assign the PRODUCTLISTFILE variable with the value productlist_LUX_200908.txt.
    Time for action—inserting new products or updating existent ones
  19. Click on Launch.
  20. When the transformation ends, check the Step Metrics. You will see the following:
    Time for action—inserting new products or updating existent ones
  21. Switch to the SQL Query Browser application.
  22. Type the following in the query entry box:
    SELECT * FROM products p;
  23. Click on Execute. The following result set is shown:
    Time for action—inserting new products or updating existent ones

What just happened?

You populated the products table with data found in text files. For inserting the data, you used the Insert/Update step.

As this was the first time you dealt with the products table, before you ran the transformation, the table was empty. After running the transformation, you could see how all products in the file were inserted in the table.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.149.249.211