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
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
answered 03 Sep '10, 17:35
Where did the file come from? Are you sure that every row is delimited by '\n'?
answered 07 Sep '10, 20:09