Professional Web Applications Themes

"select into outfile" command with field names as the first lineof the file - MySQL

Hi all ;) The question is in the subject. I had a look in the export_option but nothing appeared. I don't want to use a sophisticated tool to do this, i only use command line. thanx in advance, f....

  1. #1

    Default "select into outfile" command with field names as the first lineof the file

    Hi all ;)

    The question is in the subject. I had a look in the export_option but
    nothing appeared.
    I don't want to use a sophisticated tool to do this, i only use command
    line.

    thanx in advance,

    f.
    fabrice régnier Guest

  2. #2

    Default Re: "select into outfile" command with field names as the first lineof the file

    fabrice régnier wrote:
    > Hi all ;)
    >
    > The question is in the subject. I had a look in the export_option but
    > nothing appeared.
    > I don't want to use a sophisticated tool to do this, i only use command
    > line.
    >
    > thanx in advance,
    >
    > f.
    SELECT ... INTO OUTFILE will only work if the desired output file is in the server itself.
    In addition, you can't overwrite an existing file. And finally, it won't create the header
    you are asking for.

    Enough with the bad news. To achieve what you want, from the command line, here's a couple
    of quick recipes:

    1) to create a simple tab-separated file, a simple redirection will do
    $ mysql -B -e 'select * from db_name.table_name' > filename.txt

    2) to create a CSV file, you need to manipulate the output. I usually do this in Perl
    $ mysql -B -e 'select * from db_name.table_name' | \
    perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} F ' \
    > filename.txt
    Examples:

    $ mysql -B -e 'select * from world.City where CountryCode="AFG"'
    ID Name CountryCode District Population
    1 Kabul AFG Kabol 1780000
    2 Qandahar AFG Qandahar 237500
    3 Herat AFG Herat 186800
    4 Mazar-e-Sharif AFG Balkh 127800

    $ mysql -B -e 'select * from world.City where CountryCode="AFG"' | \
    perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} F '
    "ID","Name","CountryCode","District","Populati on"
    1,"Kabul","AFG","Kabol",1780000
    2,"Qandahar","AFG","Qandahar",237500
    3,"Herat","AFG","Herat",186800
    4,"Mazar-e-Sharif","AFG","Balkh",127800

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: "select into outfile" command with field names as the first lineof the file

    Thanx a lot for your quick response.
    That's what i was looking for ;)

    f.



    Giuseppe Maxia wrote:
    > fabrice régnier wrote:
    >> Hi all ;)
    >>
    >> The question is in the subject. I had a look in the export_option but
    >> nothing appeared.
    >> I don't want to use a sophisticated tool to do this, i only use command
    >> line.
    >>
    >> thanx in advance,
    >>
    >> f.
    >
    > SELECT ... INTO OUTFILE will only work if the desired output file is in the server itself.
    > In addition, you can't overwrite an existing file. And finally, it won't create the header
    > you are asking for.
    >
    > Enough with the bad news. To achieve what you want, from the command line, here's a couple
    > of quick recipes:
    >
    > 1) to create a simple tab-separated file, a simple redirection will do
    > $ mysql -B -e 'select * from db_name.table_name' > filename.txt
    >
    > 2) to create a CSV file, you need to manipulate the output. I usually do this in Perl
    > $ mysql -B -e 'select * from db_name.table_name' | \
    > perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} F ' \
    > > filename.txt
    >
    > Examples:
    >
    > $ mysql -B -e 'select * from world.City where CountryCode="AFG"'
    > ID Name CountryCode District Population
    > 1 Kabul AFG Kabol 1780000
    > 2 Qandahar AFG Qandahar 237500
    > 3 Herat AFG Herat 186800
    > 4 Mazar-e-Sharif AFG Balkh 127800
    >
    > $ mysql -B -e 'select * from world.City where CountryCode="AFG"' | \
    > perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} F '
    > "ID","Name","CountryCode","District","Populati on"
    > 1,"Kabul","AFG","Kabol",1780000
    > 2,"Qandahar","AFG","Qandahar",237500
    > 3,"Herat","AFG","Herat",186800
    > 4,"Mazar-e-Sharif","AFG","Balkh",127800
    >
    > ciao
    > gmax
    >
    fabrice régnier Guest

  4. #4

    Default Re: "select into outfile" command with field names as the first line of the file


    fabrice régnier wrote:
    > Thanx a lot for your quick response.
    > That's what i was looking for ;)
    >
    > f.
    >
    >
    >
    > Giuseppe Maxia wrote:
    > > fabrice régnier wrote:
    > >> Hi all ;)
    > >>
    > >> The question is in the subject. I had a look in the export_option but
    > >> nothing appeared.
    > >> I don't want to use a sophisticated tool to do this, i only use command
    > >> line.
    > >>
    > >> thanx in advance,
    > >>
    > >> f.
    > >
    > > SELECT ... INTO OUTFILE will only work if the desired output file is inthe server itself.
    > > In addition, you can't overwrite an existing file. And finally, it won't create the header
    > > you are asking for.
    > >
    > > Enough with the bad news. To achieve what you want, from the command line, here's a couple
    > > of quick recipes:
    > >
    > > 1) to create a simple tab-separated file, a simple redirection will do
    > > $ mysql -B -e 'select * from db_name.table_name' > filename.txt
    > >
    > > 2) to create a CSV file, you need to manipulate the output. I usually do this in Perl
    > > $ mysql -B -e 'select * from db_name.table_name' | \
    > > perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ :qq("$_")} F ' \
    > > > filename.txt
    > >
    > > Examples:
    > >
    > > $ mysql -B -e 'select * from world.City where CountryCode="AFG"'
    > > ID Name CountryCode District Population
    > > 1 Kabul AFG Kabol 1780000
    > > 2 Qandahar AFG Qandahar 237500
    > > 3 Herat AFG Herat 186800
    > > 4 Mazar-e-Sharif AFG Balkh 127800
    > >
    > > $ mysql -B -e 'select * from world.City where CountryCode="AFG"' | \
    > > perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ :qq("$_")} F '
    > > "ID","Name","CountryCode","District","Populati on"
    > > 1,"Kabul","AFG","Kabol",1780000
    > > 2,"Qandahar","AFG","Qandahar",237500
    > > 3,"Herat","AFG","Herat",186800
    > > 4,"Mazar-e-Sharif","AFG","Balkh",127800
    > >
    > > ciao
    > > gmax
    > >
    a php solution is also described here:
    [url]http://www.experts-exchange.com/Web/Web_Languages/PHP/PHP_Databases/Q_21327293.html[/url]

    strawberry Guest

Similar Threads

  1. Replies: 0
    Last Post: August 26th, 06:01 AM
  2. changing the default folder for the "open file" command
    By amiperl@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 0
    Last Post: April 26th, 05:16 PM
  3. Replies: 0
    Last Post: November 18th, 09:56 AM
  4. #26287 [NEW]: sybase_fetch_array() "identical field names" feature
    By abazan at filnet dot es in forum PHP Development
    Replies: 0
    Last Post: November 17th, 01:51 PM
  5. Replies: 2
    Last Post: September 15th, 04:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139