|
Home > Archive > SQL Anywhere Feedback > November 2005 > LOAD TABLE option to recognize empty lines
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
LOAD TABLE option to recognize empty lines
|
|
| Breck Carter [TeamSybase] 2005-11-08, 9:24 am |
| The LOAD TABLE statement ignores empty input lines. Please add an
option that will cause LOAD TABLE to insert a row for each empty input
line; the default behavior should continue as it does currently
(ignore empty input lines).
Some applications need to read and store text files as raw text,
stored as-is line by line, with order preserved.
The following code shown below works just fine as long you don't mind
losing all the empty lines; the output file looks exactly like the
input, minus the empty lines.
CREATE LOCAL TEMPORARY TABLE raw_text (
line_number BIGINT NOT NULL DEFAULT AUTOINCREMENT,
line_text LONG VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY ( line_number ) )
NOT TRANSACTIONAL;
LOAD TABLE raw_text ( line_text )
FROM 'c:/temp/xxx.txt'
DEFAULTS ON DELIMITED BY '' ESCAPES OFF QUOTES OFF STRIP OFF;
UNLOAD SELECT line_text
FROM raw_text
ORDER BY line_number
TO 'c:/temp/yyy.txt'
DELIMITED BY '' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;
An option to preserve empty input lines would be very welcome.
Note that the UNLOAD statement is *happy* to create empty output
lines; e.g.,
UNLOAD SELECT 'Hello \x0d\x0a\x0d\x0a World'
TO 'c:/temp/yyy.txt'
DELIMITED BY '' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;
Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| John Smirnios 2005-11-08, 9:24 am |
| It didn't used to ignore empty lines... that must have been an added
"feature". I seem to recall problems with trailing carriage returns in
hand-edited files. An option would indeed be better.
In your unload example, we are not really leaving "empty lines" as much
as we are just writing a column value and the options you have specified
have tied the hands of the engine so that we cannot escape the values in
the rows. When using UNLOAD/LOAD you always need to be very careful
about what options you use and determine whether unloading the data in
that format will create ambiguities when loading that data again.
For example, if you were to
UNLOAD TABLE raw_text
TO 'c:/temp/yyy.txt'
DELIMITED BY '' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;
then you would be very, very unlikely to get anything close to your
original data back.
-john.
--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering
Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer
Breck Carter [TeamSybase] wrote:
> The LOAD TABLE statement ignores empty input lines. Please add an
> option that will cause LOAD TABLE to insert a row for each empty input
> line; the default behavior should continue as it does currently
> (ignore empty input lines).
>
> Some applications need to read and store text files as raw text,
> stored as-is line by line, with order preserved.
>
> The following code shown below works just fine as long you don't mind
> losing all the empty lines; the output file looks exactly like the
> input, minus the empty lines.
>
> CREATE LOCAL TEMPORARY TABLE raw_text (
> line_number BIGINT NOT NULL DEFAULT AUTOINCREMENT,
> line_text LONG VARCHAR NOT NULL DEFAULT '',
> PRIMARY KEY ( line_number ) )
> NOT TRANSACTIONAL;
>
> LOAD TABLE raw_text ( line_text )
> FROM 'c:/temp/xxx.txt'
> DEFAULTS ON DELIMITED BY '' ESCAPES OFF QUOTES OFF STRIP OFF;
>
> UNLOAD SELECT line_text
> FROM raw_text
> ORDER BY line_number
> TO 'c:/temp/yyy.txt'
> DELIMITED BY '' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;
>
> An option to preserve empty input lines would be very welcome.
>
> Note that the UNLOAD statement is *happy* to create empty output
> lines; e.g.,
>
> UNLOAD SELECT 'Hello \x0d\x0a\x0d\x0a World'
> TO 'c:/temp/yyy.txt'
> DELIMITED BY '' HEXADECIMAL OFF ESCAPES OFF QUOTES OFF;
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
|
|
|
|
|