cancel
Showing results for 
Search instead for 
Did you mean: 

Export and Import

Former Member
0 Kudos

Hi experts,

1. I use the dbisql to export a table to a file

(DBA) > SELECT * FROM Employees  ># /sybase/empfile.csv


How I can specify the codepage (eg: 1208) for the export?


2. I want to import the above file to the table again.

I have studied the "LOAD TABLE" statement, but it has too many parameters. I feel difficult to find the correct

parameters to load the file.


For example, I use the statement below:

LOAD TABLE Employees

(EmployeeID,ManagerID,Surname,GivenName,DepartmentID,Street,City,State,Country,PostalCode,Phone,Status,SocialSecurityNumber,Salary,StartDate,

TerminationDate,BirthDate,BenefitHealthInsurance,BenefitLifeInsurance,BenefitDayCare,Sex)

from '/sybase/empfile.csv'

ESCAPES OFF

FORMAT ascii

DELIMITED BY ','


I did not modify the file after exporting the file.

But it returns the "non-space text found after ending quote character for an enclosed field" errors.


It seems Sybase IQ does not take the "import" and "export" as a pair of actions.

It is difficult to import the file to a table after exporting the data to a file. Strange.


Thanks & Regards,

Gordon

Accepted Solutions (1)

Accepted Solutions (1)

tayeb_hadjou
Advisor
Advisor
0 Kudos

Hi Gordon,

The recommended IQ export method is extract.

The test below works fine for me:

-- Create new table with same data definitions as Employees

select * into Employees_new  from Employees where 1=2 ;

-- Define the target export file name

set temporary option temp_extract_name1='/sybase/empfile.csv';

-- set this option. Nulls will be empty fields in exported file

set temporary option Temp_Extract_Null_As_Empty='ON' ;

-- export Employees table

select * from Employees ;

-- Disable the extracts (IMPORTANT. Otherwise any new query will be sent data to the file specified)

set temporary option temp_extract_name1='';

-- Load the table

load table Employees_new
(EmployeeID,ManagerID,Surname,GivenName,DepartmentID,Street,City,State,Country,PostalCode,Phone,Status,SocialSecurityNumber,Salary,StartDate,
TerminationDate,BirthDate,BenefitHealthInsurance,BenefitLifeInsurance,BenefitDayCare,***)
from '/sybase/empfile.csv'

escapes off
format ascii
delimited by ','
row delimited by '\n' ;

-- Compare Employees_new content with source table Employees

Hope this helps.

Regards,

Tayeb.

tayeb_hadjou
Advisor
Advisor
0 Kudos

Forgot. Add "commit" just after the load :

Load table ....

...

row delimited by '\n' ;

commit;


Answers (1)

Answers (1)

tayeb_hadjou
Advisor
Advisor
0 Kudos

For data files exported using ">#" or "output to" , the last column doesn't have row delimiter.

In this situation use "format bcp".

load table Employees_bis

(EmployeeID,ManagerID,Surname,GivenName,DepartmentID,Street,City,State,Country,PostalCode,Phone,Status,SocialSecurityNumber,Salary,StartDate,

TerminationDate,BirthDate,BenefitHealthInsurance,BenefitLifeInsurance,BenefitDayCare,***)

from '/sybase/empfile.csv'

escapes off

delimited by ','

format bcp

row delimited by '\n' ;

commit;