export data from MS SQL via PHP script to CSV file

Dear experts,

I came across a task that required user to export data table from MS SQL via PHP to a CSV file. There is one difficulty that I couldn't export one of the column that contains html tag.

I wish to find a solution for this.

my SQL query is very simple:
SELECT productID, description, price, on_hand_qty, ava_qty, cost
FROM inventory
WHERE ava_qty > 0;

Open in new window


I use the odbc connection with PHP.  I used this basic concept:

<?php

$connect = odbc_connect(#I placed the MSSQL connection string in here);

$query = #I stick my above query into this variable

#I fetched the data result with the odbc_exec
$result = odbc_exec($connect, $query);

#I create a file and ready to write 
$myfile = fopen("testfile.csv", "w")

#create an empty variable to story the result for string concatination
$filedata = null;

#I loop through the result with while loop
while(odbc_fetch_row($result)){
  $productID = odbc_result($result, 1);
  $desc = odbc_result($result, 2);
  ........

 $fileData .= $productID.",";
 $fileData .= $desc.",";
 ........... 
}
#write the entire data into the file and close the file
fwrite($myfile, $fileData);
fclose($myfile);

# close the connection
odbc_close($connect);
?>

Open in new window


When I open up the CSV file in Excel, the data is out of aligned. The data in Description column wrapped down to next row for some reason. What can I do to fix this issue? Thanks

1st Sample: The actual data suppose to be like this if I open up the CSV file

JAX-0009921    <p>this is a test</p>   11.22    99   80  5.16
                         <p>another line</p>

2nd Example: But my result looks like this
JAX-0009921    <p>this is a test</p>  
<p>another line</p>  11.22    99   80  5.16

each space represents the lines that separate the columns in excel CSV file. (I use comma as my delimiter)

Is there any way that I can align these columns  as the output in my first sample instead of the 2nd? Thanks
Kinderly WadeprogrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Peos JohnPHPCommented:
<?php
$filename = "FileName.csv"; 
 header("Content-Type: application/xls");    
header("Content-Disposition: attachment; filename=$filename.xls");  
header("Pragma: no-cache"); 
header("Expires: 0");
?>
<!-- UNCOMMENT THIS AND USE THE STYLE FOR FORMATING TABLE, IF REQUIRED
 <style>
 td
 {
	  width: 350px;
 }
 .th
 {
	  width: 350px;
 }
 </style>-->
 <table border="1" width="100%"  >
  <thead>
    <tr>      
                     <th class="th">Field 1</th>
                     <th class="th">Field 2</th>
                     <th class="th">Field 3</th>
                     <th class="th">Field 4</th>
                     <th class="th">Field 5</th>
           
     </tr>
  </thead>
 <?php //Start you loop here ?> 
  <tr>
                <td align="center"><?php echo $field1; ?></td>
                <td align="center"><?php echo $field2; ?></td>
                <td align="center"><?php echo $field3; ?></td>
                <td align="center"><?php echo $field4; ?></td>
                <td align="center"><?php echo $field5; ?></td>
  </tr>
  <?php //End loop ?>
  
  </table>

Open in new window



This is easier way for exporting the data to csv. Please check if this is is useful to you.
Dave BaldwinFixer of ProblemsCommented:
The reason for that mis-alignment is that there is nothing for the first column in the second row.  For CSV to work right, you have to have exactly the same number of fields in each row even if some of them are blank.  Put a space ' ' in there if nothing else.

In addition, it appears that you are not quoting the text parts which is the most common method.  Your CSV should look like this in a text editor:
"JAX-0009921","<p>this is a test</p>",11.22,99,80,5.16
"","<p>another line</p>",,,,

Open in new window

Kinderly WadeprogrammerAuthor Commented:
Hi Dave,

I tried to put quotes around but... there are already quotes in some of my html tag. Is it possible that I can escape the double quotes in this example?

 <img src="smiley.gif" alt="Smiley face" height="42" width="42"/>

If I have the above html tag and if I do this:

" <img src="smiley.gif" alt="Smiley face" height="42" width="42"/>",
the most outter double quote will not be matched because the double quotes in the <img> tag.

How can I resolve this? Thanks
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Julian HansenCommented:
You can try doing this

$field += '"' . str_replace('"', '""', $variable) . '",';

Open in new window

In other words change the quotes in the var to double quotes and put double quotes around the string.
You might also want to check for CR in your data - that would also mess up your csv if a CR is sent to the file as part of a field.
Dave BaldwinFixer of ProblemsCommented:
I don't know if Excel will accept the escaped quotes.  How many rows do you have in the database?  I ask because you might be able to replace the double quotes with single quotes.  They will work just as well in the HTML tags.
Julian HansenCommented:
@Dave - that is how Excel allows quotes in formulas
See attached .csv and screenshot Screenshott1213.csv

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
Ok, that works in LibreOffice Calc also.  It is different than what you would do in PHP.
Olaf DoschkeSoftware DeveloperCommented:
It's a big misunderstanding a single record is s single line of a csv file, if a varchar(max) or text field has multiple lines the line feeds in there are put into csv as is. You delimit any string type fields with double quotes, then Excel can also cope with this.

What you do is not CSV in some ways, ie you use tabs instead of comma, or are you even having a fixed width format aligned with spaces? Then variable length texts can't be put in there anyway.

A valid csv record could look like this:

"JAX-0009921","<p>this is a test</p>  
<p>another line</p>",11.22,99,80,5.16

Open in new window


Saved as csv file this is imported as one row in Excel, the second column has two lines within its cell.

Bye, Olaf.
Julian HansenCommented:
@Kinderly,

In terms of the opening question Dave Baldwin answered the question in his opening post regarding the padding and enclosing fields in quotes - yet he was left out of the points?

Was that the intention - it looks like you gave me double when you might have meant to include Dave - personally I think he should have been included.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.