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.

asked 03 Sep '10, 16:41

Thang's gravatar image

Thang
1311
accept rate: 0%


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

permanent link

answered 03 Sep '10, 17:35

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273
accept rate: 40%

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

permanent link

answered 07 Sep '10, 20:09

Jason%20Hinsperger's gravatar image

Jason Hinspe...
2.7k63447
accept rate: 35%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34
×14
×1

question asked: 03 Sep '10, 16:41

question was seen: 2,033 times

last updated: 07 Sep '10, 20:09