Announcement

Collapse
No announcement yet.

Select Into Outfile with columns name. How to ?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Select Into Outfile with columns name. How to ?

    Dear all,
    I did a parametric export of selected tables of a database.
    Following select works correctly.
    SELECT * INTO OUTFILE 'filename.csv' FIELDS TERMINATED BY ';' FROM tableName"

    $sql_1 = "SELECT * INTO OUTFILE '". $dest_file ."' ". $outparam . "FROM ". $valori[$i];
    sc_exec_sql($sql_1);

    Now I would like to add to it also a first line with columns name separated with ',' .
    I've tried to use UNION but it give me an error about the nr of columns that are different .

    Just to test it with a only table I did the following.

    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'calendar'
    UNION
    SELECT * FROM associations_mng.calendar

    error: #1222 - The used SELECT statements have a different number of columns
    It's correct becouse first select is about 1 column with all columns names that they are 9 records,
    the second is about 9 columns with some records. (1 vs 9)

    Have you got an easy solution to have something like that as csv file in output

    COL_NAME1, COL_NAME2, COL_NAME3, ...... COL_NAME n,
    AAAAA, bbbbbb, CCCCC, ..... NNNNNN
    AAAAA, bbbbbb, CCCCC, ..... NNNNNN
    AAAAA, bbbbbb, CCCCC, ..... NNNNNN

    In XLS it would be similar to make the reverse copy of first colum....

    Bye
    Thanks

  • #2
    Re: Select Into Outfile with columns name. How to ?

    A little step forward is to add a GROUP_CONCAT but still problem about different number of colums

    SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'calendar'
    UNION
    SELECT * FROM associations_mng.calendar

    #1222 - The used SELECT statements have a different number of columns

    STUCKED HERE !

    Comment


    • #3
      Re: Select Into Outfile with columns name. How to ?

      Hi Giovannino, maybe this can be useful to export to csv:

      <?php
      $conexion = mysql_connect("localhost", "root", "sistemas");
      mysql_select_db("escolar", $conexion);

      $f = fopen("archalumnos.csv","w"); // abre el archivo CSV de salida
      $separador = ",";

      $encabezado = "Id_Escuela,No_Control,Nombre,Nivel,Grado,Ciclo_Es colar,Beca,Activo"."
      "; fwrite($f,$encabezado);

      $queData = "SELECT * FROM alumnos ORDER BY nombre";
      $resData = mysql_query($queData, $conexion) or die(mysql_error());
      $totData = mysql_num_rows($resData);

      if ($totData> 0) {

      while($reg = mysql_fetch_array($resData) ) {
      $linea = $reg&#91;'idescuela'] . $separador . $reg&#91;'nocontrol'] . $separador . $reg&#91;'nombre'] . $separador . $reg&#91;'nivelestudios'] . $separador . $reg&#91;'grado'] . $separador . $reg&#91;'cicloescolar'] . $separador . $reg&#91;'beca'] . $separador . $reg&#91;'activo'] . "
      "; fwrite($f,$linea);
      }
      }

      fclose($f); // cierra el archivo CSV de salida

      echo "<a href=archalumnos.csv>Descargar CSV</a>";

      mysql_close($conexion); //cierra la conexion
      ?>

      Bye

      Alberto Aguilar
      México

      Comment

      Working...
      X