Hoday!
Is it possible to change the default pipe symbol delimiter of the FDelimitedParser() parser for flex tables?
Thanks!
Change the default delimiter of FDelimitedParser()
Moderator: NorbertKrupa
Change the default delimiter of FDelimitedParser()
Have a GREAT day!
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Change the default delimiter of FDelimitedParser()
Hi,
Take a look fdelimitedparser options...
Example:
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](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
![Image](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Change the default delimiter of FDelimitedParser()
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!
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Change the default delimiter of FDelimitedParser()
Sarah,
One option is to modify the view that Vertica created:
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](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
![Image](http://vertica-forums.com/MyUploads/ASE_ebadge.png)
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Change the default delimiter of FDelimitedParser()
This is not define by and classified those merchantsarah wrote: ↑Thu Jul 30, 2015 8:44 pmThanks 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... https://phenq-reviews.com/uk/
![Mr. Green :mrgreen:](./images/smilies/icon_mrgreen.gif)
![Mr. Green :mrgreen:](./images/smilies/icon_mrgreen.gif)