TERADATA -- How to Import and Export data to text file in TERADATA

How to Import and Export data to text file in TERADATA

à To do this process first has to set some properties of TERADATA environment.

Go to Tools in menu bar – go to Options – go to Export/Import tab --- change the property

------ Use this delimiter between columns (|), here I used PIPE

Instead of PIPE based on the requirement use what you want.

Note: - If your column data having existing delimiter mean, need to do some update in the columns. Because if you are using the PIPE to separate each column, if PIPE already present in the column means it leads to treat that PIPE also as one column separator.

------- Enclose column data in: Nothing

à Next go to Export tab checked the below options

                -- Write all exported answers sets to a single file

                -- Write column header to the export file

                -- Write Byte order mark [BOM] to Unicode export file

                -- Include a Row Number column in Microsoft Access export tables

à Next go to IMPORT tab, checked the below option

                -- Ignore the first record in the import file (Skip Header)

IMPORTANT: repeat the above steps in both end environments

E.g.: If I am importing data from PROD environment to DEV, must and should do the above settings for both environments.

Export settings & Process:

- To export he output of the query (Select * from Employee), go to FILE option and select Export Results and execute the query. It will ask to save the file and give the name of the file (Employee.txt) and click ok will save to the flat file.

Import Process & Settings:

- To import the text file data to TERADATA table first you have to create insert query

E.g. INSERT INTO EMPLOYEE (?,?,?,?,?)

How many columns you have in the table that many question marks (?) have to give in the query

How you do the import query settings like the way go to FILE options and select EXPORT RESULT option and execute the import query.

It will load the data from text file to TERADATA tables.

The text file data will be look like below (with PIPE delimiter its splitting the column)

---------------------------------------------------------------------------------------------

EMPNO|ENAME|JOB|HIREDATE|SAL|DEPTNO
7369|SMITH|CLERK|34133|800|20
7499|ALLEN|SALESMAN|36022|1600|30
7521|WARD|SALESMAN|35150|1250|30
7566|JONES|MANAGER|35003|2975|20
7698|BLAKE|MANAGER|33766|2850|30
7782|CLARK|MANAGER|34103|2450|10
7788|SCOTT|ANALYST|35129|3000|20
7839|KING|PRESIDENT|33033|5000|10
7844|TURNER|SALESMAN|34854|1500|30
7876|ADAMS|CLERK|36315|1100|20
7900|JAMES|CLERK|36700|950|30
7934|MILLER|CLERK|36546|1300|10
7902|FORD|ANALYST|35769|3000|20
7654|MARTIN|SALESMAN|36134|1250|30