How to transfer the data from CSV file to Oracle database use the PL/SQL developer tool

 

 

1.        Open the PL/SQL developer tool, enter the user name, password and the database name that your target database.

2.        Click the Tools Option of menu bar of the PL/SQL developer tool, and select option.

With the text importer you can import ASCII files into the database. Most line oriented formats like comma and tab separated fields are supported. The importer will try to determine the file format automatically, so most of the time you don’t need to define anything, just select the file, select a table, and that’s it.

When you open the text importer, you will get something like the following:

3.        Click the  button open a data file(just select the source data file), and you will get something like the following.

If you select a csv file, the data will be show as follow:

The importer loads the first 100 lines from the file, this raw data is shown in the top "File Data" section. The middle section allows you to make the definition of the text file. The bottom "Result Preview" has a grid with the data as it would be imported.

4.        Configure the data field format for the data file that you need transferred, maybe you will get the information like this.

You can adjust the field one by one as you want. The configuration is auto determined as soon as you load a file, but if this is not correct, you can create of modify this yourself. Set the Fieldcount to the correct number, and select a field from the field list to define a field definition. In the file data section, the specified field data is highlighted, allowing you to check if the field definition is correct. You have the following options to configure the text import definition:

-      General --Fieldcount The number of fields per record. -      General --Name in Header Indicates if the first record holds the fieldnames. -      General --Quote Character Indicates the string characters, usually double or single quotes. -      Field Start --Relative Position The field starts at a relative position to the end of the previous field. 0 indicates that the field starts where the previous field ended. -      Field Start --Absolute Position This indicates that the field starts at a fixed position. -      Field Start --Character This indicates that the field starts with a specific character. This is relative to the end of the previous field. -      Field End --Length Indicates that the field has a fixed length. -      Field End –Character Indicates that the field end with a specific character.

5.        If configure finished, click the  tab to configure the relational of target data table. You will get something like this.

The top section has some general import parameters. The "Fields" section allows you to associate text file fields and Oracle fields. The bottom section is the result preview for your information.

6.        Configure the database information.

You can set the following general preferences:

-      Table The Oracle table (or view) you want to import the data to. After selecting a table, the importer will try to determine the field and field types automatically. This will only work if the text file has a header. -      Commit every?Indicates after how many records you want to do a commit. If you set this to 0, all data will be committed at the end of the entire import. -      Overwrite Duplicates A duplicate record will be updated in the database -      Ignore Duplicates Duplicate records will be ignored.

 

7.        Configure the database field information.

In the field definition you see a list with fields from the text file. For every field you can set the following:

-      Field The Oracle field you want to associate with the text field. You can leave this empty if you don抰 want to import this field. -      Fieldtype The basic fieldtype: String, Number or Date. -      SQL function This option allows you to define additional SQL processing. For date fields a to_date function is added automatically. This is a very powerful option, allowing you to convert the imported data. Basically, you can enter anything that can be processed by Oracle. You can add a # to indicate the data. The "Create SQL" button will fill this field with a to_date function for date fields

 

8.        If configure finished, you will get something like this.

When the definition is complete, you can decide to save it. The toolbar has buttons to save and load definition files. The text importer will remember which definition file was used for which text file, and the definition file will be loaded automatically the next time you open the same text file.(Optional)

9.        Click the button of the bottom window, put the data of the CSV file to Oracle table that you configure.

You have two import buttons, "Import" and "Import to Script". The fist option will start importing the data into the selected table. The second option will create an SQL script with insert statements.

If the data load finished, you can see the  button of main menu has be highlight, and you will click this button and close this process, and you will see the log information  on the status bar of bottom of the importer loads.

10.    Validate the data transfer has done.

Open a new SQL window of the PL/SQL developer tool.

Input the query SQL script, select it then click the function key F8 run it.

You should be got the data in result window that the data from data file. If you get the data of you want, congratulation, you are successful.