Thread: using tab delimiter for csv format import
I have a tab-delimited text file I want to import into a table. However, I do not want \t translated to tab characters, etc., which is what the "text" format does. My file does not contain any special characters in need of escaping, and \ always just represents itself. So instead I want to use the "csv" format and specify the tab as the delimiter (on the "misc. options" tab). But, tab does not appear in the drop down list, and since typing tab changes the focus, a literal tab cannot by typed into this field. I could get around this by typing a tab into WordPad, then copying and pasting it into the delimiter field. But it would much nicer if the tab character was present in the delimiter drop down list (visibly represented by, say, "<tab>"). Cheers, Jeff
On Sun, 2013-01-13 at 11:08 -0800, Jeff Janes wrote: > I have a tab-delimited text file I want to import into a table. > However, I do not want \t translated to tab characters, etc., which is > what the "text" format does. My file does not contain any special > characters in need of escaping, and \ always just represents itself. > > So instead I want to use the "csv" format and specify the tab as the > delimiter (on the "misc. options" tab). But, tab does not appear in > the drop down list, and since typing tab changes the focus, a literal > tab cannot by typed into this field. I could get around this by > typing a tab into WordPad, then copying and pasting it into the > delimiter field. But it would much nicer if the tab character was > present in the delimiter drop down list (visibly represented by, say, > "<tab>"). > I wonder how this could work through a COPY command. Did you try? because the import tool is a simple wrapper around the COPY command. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Sun, Feb 24, 2013 at 1:47 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
Yes, I can do this with a COPY command. For example, I can use from psql:
copy foo from '/tmp/foo.txt' with (format csv, delimiter E'\t')
I can also use a literal tab character within single quotes rather than the E'\t'. Because psql wants to do tab completion when you hit tab, you have to first hit ctrl-V, then tab, in order to get a literal tab. (A trick that works in readline-based programs, but of course not GUIs)
Cheers,
Jeff
I wonder how this could work through a COPY command. Did you try?On Sun, 2013-01-13 at 11:08 -0800, Jeff Janes wrote:
> I have a tab-delimited text file I want to import into a table.
> However, I do not want \t translated to tab characters, etc., which is
> what the "text" format does. My file does not contain any special
> characters in need of escaping, and \ always just represents itself.
>
> So instead I want to use the "csv" format and specify the tab as the
> delimiter (on the "misc. options" tab). But, tab does not appear in
> the drop down list, and since typing tab changes the focus, a literal
> tab cannot by typed into this field. I could get around this by
> typing a tab into WordPad, then copying and pasting it into the
> delimiter field. But it would much nicer if the tab character was
> present in the delimiter drop down list (visibly represented by, say,
> "<tab>").
>
because the import tool is a simple wrapper around the COPY command.
Yes, I can do this with a COPY command. For example, I can use from psql:
copy foo from '/tmp/foo.txt' with (format csv, delimiter E'\t')
I can also use a literal tab character within single quotes rather than the E'\t'. Because psql wants to do tab completion when you hit tab, you have to first hit ctrl-V, then tab, in order to get a literal tab. (A trick that works in readline-based programs, but of course not GUIs)
Cheers,
Jeff
On Tue, 2013-02-26 at 12:41 -0800, Jeff Janes wrote: > On Sun, Feb 24, 2013 at 1:47 AM, Guillaume Lelarge > <guillaume@lelarge.info>wrote: > > > On Sun, 2013-01-13 at 11:08 -0800, Jeff Janes wrote: > > > I have a tab-delimited text file I want to import into a table. > > > However, I do not want \t translated to tab characters, etc., which is > > > what the "text" format does. My file does not contain any special > > > characters in need of escaping, and \ always just represents itself. > > > > > > So instead I want to use the "csv" format and specify the tab as the > > > delimiter (on the "misc. options" tab). But, tab does not appear in > > > the drop down list, and since typing tab changes the focus, a literal > > > tab cannot by typed into this field. I could get around this by > > > typing a tab into WordPad, then copying and pasting it into the > > > delimiter field. But it would much nicer if the tab character was > > > present in the delimiter drop down list (visibly represented by, say, > > > "<tab>"). > > > > > > > I wonder how this could work through a COPY command. Did you try? > > because the import tool is a simple wrapper around the COPY command. > > > > Yes, I can do this with a COPY command. For example, I can use from psql: > > copy foo from '/tmp/foo.txt' with (format csv, delimiter E'\t') > > I can also use a literal tab character within single quotes rather than the > E'\t'. Because psql wants to do tab completion when you hit tab, you have > to first hit ctrl-V, then tab, in order to get a literal tab. (A trick > that works in readline-based programs, but of course not GUIs) > OK, understood. I added the [tab] option for the next feature release. Thanks for the idea. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com