cancel
Showing results for 
Search instead for 
Did you mean: 

IQ doesn't skip rows correctly with "FORMAT BCP" ?

Former Member
0 Kudos
4,909

I'm using Sybase IQ v15. I have a file like this:

header|20100523
col1_1|col1_2|col1_3
col2_1|col2_2|col2_3
trailer|2

and want to load it into IQ skipping header and trailer rows. To do that I use below queries with "SKIP 1" and "FORMAT BCP" option - as the data rows not ending with the column delimiter:

CREATE TABLE TB_TEST_1 (
    column1 VARCHAR(512),
    column2 VARCHAR(512),
    column3 VARCHAR(512),
)
/
LOAD TABLE TB_TEST_1 (
    column1,
    column2,
    column3
) 
FROM '/data/20100326_two_rows.csv'
FORMAT BCP
SKIP 1
STRIP RTRIM ESCAPES OFF QUOTES OFF
DELIMITED BY '|'
ROW DELIMITED BY '\\n' 
/
SELECT * from TB_TEST_1
/

The output is:

 Command was executed successfully

[Executed: 03/09/2010 17:34:29] [Execution: 109ms]

1 record(s) affected

[Executed: 03/09/2010 17:34:29] [Execution: 172ms]

column1     column2     column3    
 ----------  ----------  ---------- 
 col2_1      col2_2      col2_3

1 record(s) selected [Fetch MetaData: 0ms] [Fetch Data: 0ms]

[Executed: 03/09/2010 17:34:29] [Execution: 94ms] 

Only the 2nd data row is inserted, it seems IQ skips one row more that it is told. I have also tried the below query instead of using "FORMAT BCP" but still have the same problem - only 1 row inserted

LOAD TABLE TB_TEST_1 (
    column1,
    column2,
    column3'\\n'
) 
FROM '/data/20100326_two_rows.csv'
SKIP 1
STRIP RTRIM ESCAPES OFF QUOTES OFF
DELIMITED BY '|'

Any advice is much appreciated.

Accepted Solutions (0)

Answers (2)

Answers (2)

MarkCulp
Participant

First please note that this forum is meant for questions relating to SQL Anywhere. Since SA is the "front end" to IQ there may be questions that you may have about IQ that may be answered in this forum but the users that use this site will not be experts on IQ (and many may not be familar with IQ at all). As such it would likely be better to direct IQ-specific questions to IQ support.

Your questions appears to indicate that you have discovered a bug (but I have not verified that this is the case) and therefore you would be better to direct your question to your IQ Tech Support representative or report the bug by opening a case using Case Express

Former Member
0 Kudos

Where did the file come from? Are you sure that every row is delimited by '\\n'?