Export data from SQL database to CSV file, some rows of data get split into more than one row

Question

When I export data from my SQL database to CSV file, some rows of data (records) get split into more than one row, as if there is a CR. I know that one reason is the following: One of the columns of the data is "Notes" that contains text that sometimes does contain a CR; I understand why this causes a new row in the CSV, but I would like that not to happen, either. How can I strip the CR, but add a period+space to format the Note so it's readable even without the CR?

However, I also get the extra row even if there is no CR, meaning the CSV has a blank row after a record, or the Note is on an extra line. I've included a screenshot of a portion of the CSV file to illustrate this and also illustrate that not all records show the behavior.

Here is my code. I did not write this, I inherited it. Also, I am not very experienced writing code.

header('Content-Type: application/msexcel-tab');
header('Content-Disposition: attachment; filename="Invaders of Texas Data -- '.date("Y-m-d").'.xls"');

$whereclause = '';
$passclause = '';
$satellite = $_REQUEST['satellite'];
$collector = $_REQUEST['collector'];
$sn = $_REQUEST['sn'];
$cn = $_REQUEST['cn'];

if ($satellite){
    $whereclause .= " AND `satellite_id` = ".$satellite." ";
    $passclause .= "&satellite=".$satellite;
}
if ($collector){
    $whereclause .= " AND `collector_id` = ".$collector." ";
    $passclause .= "&collector=".$collector;    
}
if ($sn){
    $whereclause .= " AND `plant_id` LIKE '".$sn."' ";  
    $passclause .= "&sn=".$sn;
}
if ($cn){
    $whereclause .= " AND `plant_id` LIKE '".$cn."' ";  
    $passclause .= "&cn=".$cn;
}

$count_sql = "
    SELECT COUNT(*) AS `counttotal`
    FROM `inv_sites`
    WHERE 1
    $whereclause
    AND `valid` LIKE 'Yes'
    ;
";
//echo $count_sql;
$count_total = mysql_fetch_array(mysql_query($count_sql));


$sql = "
    SELECT *
    FROM `inv_sites`
    WHERE 1
    $whereclause 
    AND `valid` LIKE 'Yes'
    ORDER BY `collection_date` ASC
    ;
";
$the_result = mysql_query($sql);
?>

Invaders of Texas
www.texasinvasives.org
Exported: <?= date("Y-m-d G:i"); ?> 

Obs_ID  Date    USDA    Species Time_Spent  Satellite   Collector   Lat Long    Location_Error  Loc_Err_Units    Disturbance    Patch_Type  Abundance   Validated   Valid_Name  Valid_Date  Notes

<?php
if ($this_row = mysql_fetch_array($the_result)){
    do {
?>
<?=$this_row['site_id'];?>  <?=$this_row['collection_date'];?>  <?=$this_row['plant_id']?>  <?=sn_from_usda($this_row['plant_id'])?>    <?=$this_row['collection_time'];?>  <?=satellite_from_id($this_row['satellite_id']);?>  <?=$this_row['collector_id'];?> <?=$this_row['latitude'];?> <?=$this_row['longitude'];?>    <?=$this_row['error'];?>    <?=$this_row['error_unit'];?>   <?=$this_row['disturbance'];?>  <?=$this_row['patch_type'];?>   <?=$this_row['abundance'];?>    <?=$this_row['valid'];?>    <?=$this_row['valid_name'];?>   <?=$this_row['valid_date'];?>   <?=$this_row['notes'];?>

<?php
    } while ($this_row = mysql_fetch_array($the_result));
}
?>

I'd appreciate any help!! Thanks.

Peter H · Answer 1 · 2018-05-08T07:28:59.903

You could replace the newlines in the PHP or the SQL query.

You have the following line above.

<?=$this_row['site_id'];?>  <?=$this_row['collection_date'];?>  <?=$this_row['plant_id']?>  <?=sn_from_usda($this_row['plant_id'])?>    <?=$this_row['collection_time'];?>  <?=satellite_from_id($this_row['satellite_id']);?>  <?=$this_row['collector_id'];?> <?=$this_row['latitude'];?> <?=$this_row['longitude'];?>    <?=$this_row['error'];?>    <?=$this_row['error_unit'];?>   <?=$this_row['disturbance'];?>  <?=$this_row['patch_type'];?>   <?=$this_row['abundance'];?>    <?=$this_row['valid'];?>    <?=$this_row['valid_name'];?>   <?=$this_row['valid_date'];?>   <?=$this_row['notes'];?>

Try replacing it with the below (the change is on the very end).

<?=$this_row['site_id'];?>  <?=$this_row['collection_date'];?>  <?=$this_row['plant_id']?>  <?=sn_from_usda($this_row['plant_id'])?>    <?=$this_row['collection_time'];?>  <?=satellite_from_id($this_row['satellite_id']);?>  <?=$this_row['collector_id'];?> <?=$this_row['latitude'];?> <?=$this_row['longitude'];?>    <?=$this_row['error'];?>    <?=$this_row['error_unit'];?>   <?=$this_row['disturbance'];?>  <?=$this_row['patch_type'];?>   <?=$this_row['abundance'];?>   <?=$this_row['valid'];?>    <?=$this_row['valid_name'];?>   <?=$this_row['valid_date'];?>   <?=trim(preg_replace('/\s+/', ' ', $this_row['notes']));?>

The preg_replace allows you to use regular expressions in php to remove the newlines.

If this doesn't work you may need to alter your SQL query to remove the newline from the database query. See this post

Pete

Export data from SQL database to CSV file, some rows of data get split into more than one row

1 Answers1