Pivotal SQLFire export/importing CSV files

Pivotal SQLFire offers 2 built in procedures that allow table data to be exported in CSV format as well as imported back from CSV format as shown below. The procedures are as follows

• SYSCS_UTIL.EXPORT_TABLE
• SYSCS_UTIL.IMPORT_TABLE

Example

In this example we are using a table as follows

  
sqlf> select * from apples_dept;
DEPTNO     |DNAME         |LOC          
----------------------------------------
70         |SUPPORT       |SYDNEY       
60         |DEV           |PERTH        
50         |MARKETING     |ADELAIDE     
40         |OPERATIONS    |BRISBANE     
30         |SALES         |CHICAGO      
20         |RESEARCH      |DALLAS       
10         |ACCOUNTING    |NEW YORK     

7 rows selected

1. Export the table as shown below.

  
sqlf> CALL SYSCS_UTIL.EXPORT_TABLE('APP', 'APPLES_DEPT', '/Users/papicella/sqlfire/vFabric_SQLFire_111_b42624/pasdemos/sqlfireweb/sql/apples_dept.csv', null, null, null);
Statement executed.

2. View export data as shown below.

[Wed Sep 04 20:10:18 papicella@:~/sqlfire/vFabric_SQLFire_111_b42624/pasdemos/sqlfireweb/sql ] $ cat apples_dept.csv 
70,"SUPPORT","SYDNEY"
60,"DEV","PERTH"
50,"MARKETING","ADELAIDE"
40,"OPERATIONS","BRISBANE"
30,"SALES","CHICAGO"
20,"RESEARCH","DALLAS"
10,"ACCOUNTING","NEW YORK"

3. Truncate table

  
sqlf> truncate table apples_dept;
0 rows inserted/updated/deleted

4. Import data back into the table

  
sqlf> CALL SYSCS_UTIL.IMPORT_TABLE('APP', 'APPLES_DEPT', '/Users/papicella/sqlfire/vFabric_SQLFire_111_b42624/pasdemos/sqlfireweb/sql/apples_dept.csv', ',', '"', null, 0);
Statement executed.
sqlf> select * from apples_dept;
DEPTNO     |DNAME         |LOC          
----------------------------------------
10         |ACCOUNTING    |NEW YORK     
20         |RESEARCH      |DALLAS       
30         |SALES         |CHICAGO      
40         |OPERATIONS    |BRISBANE     
50         |MARKETING     |ADELAIDE     
60         |DEV           |PERTH        
70         |SUPPORT       |SYDNEY       

7 rows selected  

More Information

http://pubs.vmware.com/vfabric53/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.1/reference/system_procedures/derby/rrefimportproc.html

http://pubs.vmware.com/vfabric53/index.jsp?topic=/com.vmware.vfabric.sqlfire.1.1/reference/system_procedures/derby/rrefexportproc.html