Hello
I'm extracting information from an informix database via a java program. The program uses an xml file to generate it's sql which pushes the data to a flat file. This is then picked up by a stored procedure to populate a sql server reporting database expecting a set number of columns.
The problem I have is that in one of the fields the user has a free flowing text screen and can enter carriage returns. When this data is extracted from informix the data in the flat file contains multiple lines and is therefore rejected by the proc
I've tried using the REPLACE function but to no avail
Does anyone have the exact syntax to use or an alternative ?
Below is a sample of the kind of file I'm dealing with, the bet_id's are 70606608 and 70606615, this is how the rows should be separated but as can be seen there's lots of blank lines and carriage returns
Any help appreciated
Thanks
Rob
bet_id cr_date ipaddr aff_id bet_type source acct_id user_id stake token_value settled status max_payout num_selns stake_per_line num_lines leg_type num_legs receipt xfer_id view temp_desc_1 temp_desc_2 temp_desc_3 temp_desc_4 desc_1 desc_2 desc_3 desc_4 to_settle_at
70606608 2009-01-13 22:35:30.0 null 0 MAN P 842952 0 240.0 0.0 N A 10000.0 1 240.0 1 W1 P08498380000141 0 null ??40 Win Treble
Manchester United to beat WIgan @ 1/4
Chelsea to beat Stoke @ 1/4
Chelsea to beat Southend @ 1/4 null null null ??40 Win Treble
Manchester United to beat WIgan @ 1/4
Chelsea to beat Stoke @ 1/4
Chelsea to beat Southend @ 1/4 null null null 2009-01-13 00:00:00.0
70606615 2009-01-13 22:36:18.0 null 0 MAN P 1247632 0 40.0 0.0 N A 10000.0 1 40.0 1 W1 P12563100002695 0 null Bangladesh Vs Sri Lanka OD match list
Bangladesh to win - 11/2 ??40 null null null Bangladesh Vs Sri Lanka OD match list
Bangladesh to win - 11/2 ??40 null null null 2009-01-13 00:00:00.0
I'm extracting information from an informix database via a java program. The program uses an xml file to generate it's sql which pushes the data to a flat file. This is then picked up by a stored procedure to populate a sql server reporting database expecting a set number of columns.
The problem I have is that in one of the fields the user has a free flowing text screen and can enter carriage returns. When this data is extracted from informix the data in the flat file contains multiple lines and is therefore rejected by the proc
I've tried using the REPLACE function but to no avail
Does anyone have the exact syntax to use or an alternative ?
Below is a sample of the kind of file I'm dealing with, the bet_id's are 70606608 and 70606615, this is how the rows should be separated but as can be seen there's lots of blank lines and carriage returns
Any help appreciated
Thanks
Rob
bet_id cr_date ipaddr aff_id bet_type source acct_id user_id stake token_value settled status max_payout num_selns stake_per_line num_lines leg_type num_legs receipt xfer_id view temp_desc_1 temp_desc_2 temp_desc_3 temp_desc_4 desc_1 desc_2 desc_3 desc_4 to_settle_at
70606608 2009-01-13 22:35:30.0 null 0 MAN P 842952 0 240.0 0.0 N A 10000.0 1 240.0 1 W1 P08498380000141 0 null ??40 Win Treble
Manchester United to beat WIgan @ 1/4
Chelsea to beat Stoke @ 1/4
Chelsea to beat Southend @ 1/4 null null null ??40 Win Treble
Manchester United to beat WIgan @ 1/4
Chelsea to beat Stoke @ 1/4
Chelsea to beat Southend @ 1/4 null null null 2009-01-13 00:00:00.0
70606615 2009-01-13 22:36:18.0 null 0 MAN P 1247632 0 40.0 0.0 N A 10000.0 1 40.0 1 W1 P12563100002695 0 null Bangladesh Vs Sri Lanka OD match list
Bangladesh to win - 11/2 ??40 null null null Bangladesh Vs Sri Lanka OD match list
Bangladesh to win - 11/2 ??40 null null null 2009-01-13 00:00:00.0