2

I wrote the following script to select data from a database and save it in a CSV file using isql:

OUTPUT temp/filename.csv FORMAT csv DELIMITED BY ';';
SELECT dept_no, department, location FROM department;

I got the following result in the CSV file:

DEPT_NO DEPARTMENT                LOCATION        
======= ========================= =============== 
000     Corporate Headquarters    Monterey        
100     Sales and Marketing       San Francisco   
600     Engineering               Monterey        
900     Finance                   Monterey        
180     Marketing                 San Francisco   
620     Software Products Div.    Monterey        
621     Software Development      Monterey        
622     Quality Assurance         Monterey        
623     Customer Support          Monterey        
670     Consumer Electronics Div. Burlington, VT  
671     Research and Development  Burlington, VT  
672     Customer Services         Burlington, VT  
130     Field Office: East Coast  Boston          
140     Field Office: Canada      Toronto         
110     Pacific Rim Headquarters  Kuaui           
115     Field Office: Japan       Tokyo           
116     Field Office: Singapore   Singapore       
120     European Headquarters     London          
121     Field Office: Switzerland Zurich          
123     Field Office: France      Cannes          

DEPT_NO DEPARTMENT                LOCATION        
======= ========================= =============== 
125     Field Office: Italy       Milan

However, I need to get an output 1) without the repeating headings and 2) without the space between data in the columns.

An ideal output should look like this:

ID;LASTNAME;FIRSTNAME;DOB
1;Miller;Steve;1980-06-28
2;Jones;Martha;1965-11-02
3;Waters;Richard;1979-10-15

Would anyone help me to fix this problem?

greybeard
  • 2,249
  • 8
  • 30
  • 66
Aigerim
  • 71
  • 1
  • 5
  • I do not know isql but it looks like a bug as you have specified DELIMITED BY – Livius Oct 30 '19 at 08:18
  • 1
    I'm not aware that Firebird ISQL `output` has a `FORMAT` option. Where did you see this documented? – Mark Rotteveel Oct 30 '19 at 10:36
  • 1
    why not using tools like `fbexport` ? – Arioch 'The Oct 30 '19 at 11:30
  • If you are using IBExpert, which runs on windows only, you can use IbeScript to do exports into CSV and other formats. https://www.ibexpert.net/ibe/pmwiki.php?n=Doc.ExportCSVData2 – Ed Mendez Oct 30 '19 at 15:41
  • @Arioch 'The I used *FBExport* to execute a query and export the results to a csv file. I got an output without the repeating headings as I wanted. However, a row in the output looks like *"field_value","field_value","field_value" *. How can I changer the formatting to *field_value;field_value;field_value* (without comma and quotation marks)? – Aigerim Nov 01 '19 at 06:52
  • without comma it is no more C(omma)SV... Bad luck with Excel having no support for reading CSV files... Try some tool like `SED` to replace `","` sequence with semicolon. Or make it like `select field1 || ';' || field2 || ";" || field3 from table` – Arioch 'The Nov 01 '19 at 08:03

3 Answers3

1

This would help you. It makes isql repeats every

isql -pag 100000
Kinya Hagitani
  • 11
  • 2
1

You can add a semicolon ; to each field in the select (except the last one) as shown below:

select (fieldname) || ';' as xxxxx , (fieldname) || ';' as xxxxx ........

Heading is still one field.

1;Miller;Steve;1980-06-28
2;Jones;Martha;1965-11-02
3;Waters;Richard;1979-10-15
Pedram
  • 921
  • 1
  • 10
  • 17
Lo Tros
  • 11
  • 2
0

Here I am showing an example of output to a tab separated file output.tsv.

Turn headings off:

SET HEADING OFF;

Redirect output to the file:

OUTPUT 'output.tsv';

Output your own header:

SELECT 'DEPT_NO' || ASCII_CHAR(9) || 'DEPARTMENT' || ASCII_CHAR(9) || 'LOCATION' FROM RDB$DATABASE;

Output the data:

SELECT DEPT_NO || ASCII_CHAR(9) || DEPARTMENT || ASCII_CHAR(9) || LOCATION FROM DEPARTMENT;
Roman Mishin
  • 461
  • 5
  • 5