Export data to Database

Support
1

Hi guys.
I can't use bulk load because my test license has expires.
I'm try use "Export to Database" with Batch Size = 1000, my txt table has 5500 records.
When the task finalized, y have only 500 records in my database table.
I think easymorph clean all previous records in any batch loaded.
Someone who help me?.

also, i try use database command, but i can't found all correct instructions for that. Anyone who have an example, please?

2

Hi, @mesh1964, and welcome to the Community!

EasyMorph doesn't clean records unless a corresponding action is added to the workflow.

Can you please send your EasyMorph project to our support email so we can check the settings of the "Export to Database" action?

As for the"Database command" action, the actual command syntax depends on your target database.

3

Hi.

This is the documentation about my project.
Only 4 steps.

  1. Select a text file as plain text .
  2. Rename 1 column
  3. Send to the database command to create table
  4. Export data with "Export to database".

There i'snt an erase command for any batch loaded, but at the end only appear the records of the last batch.

Project "Carga_D04_DTMT_Oracle"
Location: XXXXXXXXXXXXX\Carga_D04_DTMT_Oracle.morph

Embedded Connectors
The project doesn't have embedded connectors.

Shared Connectors
The project uses the following shared connectors:

  1. XXXXXX_B (Oracle)

Setting Value
Hostname XXX.XX.X.X
Port XXXX
Service name XXXX
Use Windows authentication No
User XXXXXX
Modules
The project consists of 1 module.

Module: Main
The module requires 1 source of data:

Text file "D04_DTMT_11_2023.TXT"
It exports data to 1 destination:

Database XXXXXX_B
The module creates 1 table that is later transformed using 4 actions.

Parameters
The module has the following parameters:

Parameter Type Value Label Note
Tabla Text or number XXXXXX.D04_DTMT D04_DTMT
File name File name XXXXXXXXXXXXXXXXXXXXXX\D04_DTMT_11_2023.TXT
Table: "D04.TXT"
Step 1: Import a text file as plain text into a single column.

Load file "D04_DTMT_11_2023.TXT".

The encoding is encoding is UTF-8. Import every line as new row.

Step 2: Rename 1 column:

Old Name New Name
[Column] [BCP_GLOSA]
Step 3: Send the database command below to shared SQL data source XXXXXX_B:

Create table "D04_DTMT".

Assign column types:

Column Name Database Type
BCP_GLOSA VARCHAR2(214)
If table already exists, delete rows, keep table.

Timeout is 1800 seconds.

Step 4: Export data to shared SQL data source XXXXXX_B. Table name: "XXXXXX_B.D04_DTMT". Batch size: 1000.

When value doesn't match column type, export as NULL.

When exporting a batch fails, export all batches that didn't fail, keep in EasyMorph only failed batches.

Export into database columns with the same names as in EasyMorph.

4

@mesh1964,

Thank you for the documentation.

First of all, the "Create table" database command has the "If table already exists: Delete rows, keep table" option selected. So, all the previously exported rows are deleted when the project is executed.

Also, the "Export to database" action has the "When exporting a batch fails: Add new column to flag rows in batches that failed" option selected. So it seems that in your case, only the last batch that actually has only 500 rows is being exported successfully, and all the other five batches contain at least a single row that can't be exported.

You can see the actual error for each batch in the "Export result" column, created by the "Export to database" action.

It may be simpler to pinpoint failing rows with a smaller batch size. Or you can try to switch the "When exporting a batch fails" option to the "Halt execution" mode. This mode will show you an error message that will tell you the number of the first failing row.

5

Thanks for the response.

I'm disaccord with you in some aspects.

  1. "Create table" is the third step in the poject, when the batch is loaded, It shouldn't affect data loading because this is no loop charge, all steps are separated each other. If was true the expressed, then that type of load will be inapropiate and shouldn't have batch process possibilities.
  2. The action "Add the columns to flag rows", i'snt the problem, because in the case of error, add columns with errors, then i can solve each problem separated. Is only one way to operate the charge of data.
  3. I was test with batch smaller, but the result is the same- Por example, 47 records, each batch with 10 records. At end of charge, only 7 records in DataBase. The action "Export to database", erase previous records for every batch loaded.
  4. There is another way to charge data into databases?. I have no valid license at this moment, because i was testing the tool and i think exists only two ways for charge data into databases. May be the third, using commands, but i can't found one best example for that. In this cases, the online help could be better.

Another question, how can i see the progress of charge while the data is loaded?, only can i see one bar on the right corner, but i don't see what the progress o number of records loaded and i must wait to the end of loading.

6

Hi @mesh1964,

The "Export to database" action doesn't erase records in the database. Is it possible that the target database table has a trigger that deletes records on some condition?

As for the example with 47 records and batch size 10. Can you please send me a screenshot of the
"Export result" column that is created when you run the export?

You can use the "Run program" action to call the SQL*Loader utility to load data into an Oracle database. Here is the help for the "Run program" action: transformations:runprogram [EasyMorph Help] and for the SQL*Loader utility: SQL*Loader

Currently, there is no way to track the export progress. As a workaround, you can manually execute the "SELECT COUNT(*) FROM table_name" query from another EasyMorph window or from some other software.