Change the default delimiter of FDelimitedParser()

Moderator: NorbertKrupa

Post Reply
sarah
Intermediate
Intermediate
Posts: 77
Joined: Mon Aug 27, 2012 1:34 pm

Change the default delimiter of FDelimitedParser()

Post by sarah » Thu Jul 30, 2015 7:29 pm

Hoday!

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

Thanks!
Have a GREAT day!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1817
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Change the default delimiter of FDelimitedParser()

Post by JimKnicely » Thu Jul 30, 2015 8:11 pm

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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

sarah
Intermediate
Intermediate
Posts: 77
Joined: Mon Aug 27, 2012 1:34 pm

Re: Change the default delimiter of FDelimitedParser()

Post by sarah » Thu Jul 30, 2015 8:44 pm

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...
Have a GREAT day!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1817
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Change the default delimiter of FDelimitedParser()

Post by JimKnicely » Tue Aug 04, 2015 4:03 pm

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)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica Database Development”