I have been trying to load Sybase ASE query data into a text file. The text file data is going to be loaded into a Postgres table.
But many rows are sent as 2 separate rows to the output file. The output in isql itself has this issue.
I have tried the below options still no success.
- Tried ltrim(rtrim(cast(column_name as varchar))) -- tried for all columns in the query.
- Tried sed to streamline the output format
- Tried different column widths, delimiters etc. in the isql connection.
None of the above steps fixes my issue.
Below is a part of the query output with the above said issue.
3240 1MB MGMT AB -8377 NULL LEGACY PASSED 3240 1MB MGMT AB -8377 D22600484 DISCONNECT DISCONNECT The above query result has 2 rows(first column has the value 3240 in both the rows)
As you see, the 'DISCONNECT DISCONNECT' part in the second row comes to the next line and this is treated as 3rd row. The datatype of the last 2 columns are varchar(10), so there is no space issue seemingly.
There is no space before or after the column values either.
Please let me know if there is any way to overcome this issue.
61 Answer
As @Shelter suggested have a look at the -w option for isql. It controls the width of the output from isql. Provided -w is wider than any of the rows, every row will appear all on one line.
You may also want to remove other extraneous stuff:
- Column headings
- Row count
Column headings can be removed with the -b option.
The row count can be removed with the option
set nocount on go in your SQL script.
Another alternative is to create a view using the SQL used to create a view and use the BCP tool to export the data in character format.
