Get Column name in csv file

Question

Working on creating a CSV file and wondering how I can get the column name in the first row. Right now I think it's trying to echo the Column name but getting 0'nulls. I am also wondering if it's possible to put each of the values in each cell, I can do it manually in excel but that is troublesome.

enter image description here

This is the code:

$result=sqlsrv_query($conn,$sql) or die("Couldn't execute query:<br>" . sqlsrv_error(). "<br>" . sqlsrv_errno()); 

$file_ending = "csv";
$reals=array();
//header info for browser
header("Content-Type: application/csv");    
header("Content-Disposition: attachment; filename=test.csv");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character

$i=0;
foreach( sqlsrv_field_metadata( $result ) as $fieldMetadata ) {    
       echo $fieldMetadata["Name"]+"\t";
       if($fieldMetadata["Type"]=="real")//$fieldMetadata["Type"]=== SQL_REAL
       {
           $reals[] = $i;
       }
       $i++;
}


print("\n");    
//end of printing column names  
//start while loop to get data
while($row = sqlsrv_fetch_array($result))
{

   $schema_insert = "";
   for($j = 0; $j < sqlsrv_num_fields($result); $j++)
   {
      if ($row[$j] != "") {
         if (in_array($j, $reals)) {
            $schema_insert .= str_replace(".",",", $row[$j]) . $sep;
         } else {
            $schema_insert .= $row[$j] . $sep;
         }
      }
      else
         $schema_insert .= "" . $sep;
   }

   $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
   $schema_insert .= "\t";
   print(trim($schema_insert));
   print "\n";
}

With your sql; I think CSV file just have data but without field name. Can you get first row ( field name ) in your csv file ? — fanfan1609, Apr 29 '14 at 07:39
I am not sure how to get field name, but I am getting nulls in the first field. Not sure why either. — , Apr 29 '14 at 08:48

MyWetSocks · Accepted Answer · 2014-04-30T07:40:53.550

2

Would this work ? Since sqlsrv_fetch_array return an associative array with the name of the field as key we can take that to input as the first row.

$result=sqlsrv_query($conn,$sql) or die("Couldn't execute query:<br>" . sqlsrv_error(). "<br>" . sqlsrv_errno()); 

$file_ending = "csv";
$reals=array();
//header info for browser
header("Content-Type: application/csv");    
header("Content-Disposition: attachment; filename=test.csv");  
header("Pragma: no-cache"); 
header("Expires: 0");
/*******Start of Formatting for Excel*******/   
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character

$firstRow = true;


//start while loop to get data
while($row = sqlsrv_fetch_array($result))
{
  if($firstRow)
  {
    $names = array_keys($row);
    $namesToPrint = '';

    foreach($names as $idx => $name)
    {
        if($idx % 2 != 0)
        {
            $namesToPrint .= $name.',';
        }
    }

    $namesToPrint = substr($namesToPrint, 0, -1);

    print $namesToPrint."\n";

    $firstRow = false;
  }

   $schema_insert = "";
   for($j = 0; $j < sqlsrv_num_fields($result); $j++)
   {
      if ($row[$j] != "") {
         if (in_array($j, $reals)) {
            $schema_insert .= str_replace(".",",", $row[$j]) . $sep;
         } else {
            $schema_insert .= $row[$j] . $sep;
         }
      }
      else
         $schema_insert .= "" . $sep;
   }

   $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
   $schema_insert .= "\t";
   print(trim($schema_insert));
   print "\n";
}

edited Apr 30 '14 at 07:40

answered Apr 29 '14 at 20:55

MyWetSocks

592
1
4
10

Thanks! That worked. But now I am getting numbers with the name, how can I remove them and only show the names ? https://www.dropbox.com/s/4azrphiekb4vsdz/verdi.png – Apr 30 '14 at 07:24
I have updated the code to remove the numerical indexes too ;) – MyWetSocks Apr 30 '14 at 07:41
Also note that I have used comma as a seperator and I think you are using tabs but that shouldn't be too difficult to modify ;) – MyWetSocks Apr 30 '14 at 07:42
Thanks! Can I ask if you know how can i make it to put the column in each cell automatic instead of doing it manually? – Apr 30 '14 at 07:45
In excel there is a Data Tabs where you should select your column and click on Text to Columns and then follow the instructions ;) Don't forget to mark your question as solved. – MyWetSocks Apr 30 '14 at 07:49
Yes! That's what I am doing now. But it would be much easier if it was done automatic when the excel opens. Or is it possible to make it standard in excel to make it delimate with tab? – Apr 30 '14 at 07:49
I have no idea sorry :'( – MyWetSocks Apr 30 '14 at 07:52

Get Column name in csv file

1 Answers1