Exporting and Importing Delimited Data
Go Up to Importing and Exporting Data
Use the TIBSQL component, along with TIBOutputDelimitedFile and TIBInputDelimitedFile objects to perform batch exports and imports of data to and from a database into pipe-tilde (|~) and Z -W-F delimited files.
Use a SQLSELECT statement to export the data to the delimited file, and an INSERT statement to import the delimited data into another database.
By default, the column delimiter is a tab, and the row delimiter is a tab-line feed (Z -{{{1}}}-F). Use the ColDelimiter and RowDelimiter properties to change the column delimiter and row delimiter, respectively.
For example, to set the column delimiter to a comma, you could use the following line of code:
DelimOutput.ColDelimiter := ',';
Columns may contain spaces before the delimiter. For example, if you have a column called NAME which is defined as a CHAR(10), and the name “Joe” is in that column, then “Joe” will be followed by 7 spaces before the column is delimited.
Exporting Delimited Data
To export delimited data, you will need TIBSQL, TIBDatabase, and TIBTransaction components. Set up the database component, and associate the components with each other. In the following example, the database and transaction components are set to active in the code.
Use the Database Editor to set up the database component. To start the Database Editor, right click the database component with the mouse and select Database Editor from the drop-down menu.
The following code snippet outputs selected data with a SQLSELECT statement from the SOURCE table to the file source_delim.
procedure TForm1.Button3Click(Sender: TObject);
var
DelimOutput : TIBOutputDelimitedFile;
begin
IBSQL3.Database.Open;
IBSQL3.Transaction.StartTransaction;
IBSQL3.SQL.Text := 'Select name, number, hired from Source';
DelimOutput := TIBOutputDelimitedFile.Create;
try
DelimOutput.Filename := 'source_delim';
IBSQL3.BatchOutput(DelimOutput);
finally
DelimOutput.Free;
IBSQL3.Transaction.Commit;
end;
end;
Importing Delimited Data
To import delimited data, you will need TIBSQL, TIBDatabase, and TIBTransaction components.et up the database component, and associate the components with each other. In the following example, the database and transaction components are set to active in the code.
Use the Database Editor to set up the database component. To start the Database Editor, right click the database component with the mouse and select Database Editor from the drop-down menu.
It is important to note that you must import data into a table with the same column definitions and data types, and in the same order; otherwise, all sorts of unpredictable and undesirable results may occur.
The following code snippet inputs selected data with a SQL INSERT statement from the source_delim file created in the last example into the DESTINATION table.
procedure TForm1.Button4Click(Sender: TObject);
var
DelimInput : TIBInputDelimitedFile;
begin
IBSQL4.Database.Open;
IBSQL4.Transaction.StartTransaction;
IBSQL4.SQL.Text := 'Insert into Destination values(:name, :number, :hired)';
DelimInput := TIBInputDelimitedFile.Create;
try
DelimInput.Filename := 'source_delim';
IBSQL4.BatchInput(DelimInput);
finally
DelimInput.Free;
IBSQL4.Transaction.Commit;
end;
end;