Page 1 of 1

Change the default delimiter of FDelimitedParser()

Posted: Thu Jul 30, 2015 7:29 pm
by sarah
Hoday!

Is it possible to change the default pipe symbol delimiter of the FDelimitedParser() parser for flex tables?

Thanks!

Re: Change the default delimiter of FDelimitedParser()

Posted: Thu Jul 30, 2015 8:11 pm
by JimKnicely
Hi,

Take a look fdelimitedparser options...

Example:

Code: Select all

dbadmin=> create flex table test_flex();
CREATE TABLE

dbadmin=> \! cat /home/dbadmin/test.txt
1,"test",test1
"test3",2

dbadmin=> copy test_flex from '/home/dbadmin/test.txt' parser fdelimitedparser(header=false,delimiter=',');
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> select compute_flextable_keys_and_build_view('test_flex');
                                 compute_flextable_keys_and_build_view
--------------------------------------------------------------------------------------------------------
 Please see public.test_flex_keys for updated keys
The view public.test_flex_view is ready for querying
(1 row)

dbadmin=> select * from public.test_flex_view;
  ucol0  | ucol1  | ucol2
---------+--------+-------
 1       | "test" | test1
 "test3" | 2      |
(2 rows)

Re: Change the default delimiter of FDelimitedParser()

Posted: Thu Jul 30, 2015 8:44 pm
by sarah
Thanks for the quick response Jim! Is there a way to lose the quotes too? There doesn't seem to be an ENCLOSED BY option on flex tables...

Re: Change the default delimiter of FDelimitedParser()

Posted: Tue Aug 04, 2015 4:03 pm
by JimKnicely
Sarah,

One option is to modify the view that Vertica created:

Code: Select all

dbadmin=> CREATE OR REPLACE VIEW public.test_flex_view AS
dbadmin->  SELECT TRIM(BOTH '"' FROM (MapLookup(test_flex.__raw__, 'ucol0'::varchar(5)))::varchar(20)) AS ucol0,
dbadmin->         TRIM(BOTH '"' FROM (MapLookup(test_flex.__raw__, 'ucol1'::varchar(5)))::varchar(20)) AS ucol1,
dbadmin->         TRIM(BOTH '"' FROM (MapLookup(test_flex.__raw__, 'ucol2'::varchar(5)))::varchar(20)) AS ucol2
dbadmin->  FROM public.test_flex;
CREATE VIEW

dbadmin=> SELECT * FROM public.test_flex_view;
 ucol0 | ucol1 | ucol2
-------+-------+-------
 1     | test  | test1
 test3 | 2     |
(2 rows)