on 11-21-2014 9:13 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.