Solved

Replacing line breaks before outputing to excel?

Posted on 2010-09-08
8
1,270 Views
Last Modified: 2012-05-10
Hello, I found a good script to export orders from Magento but when someone has address1 and address2 filled in, it combines them in the table as "street". It must be putting a line break or some charater i can't see, because the excel file is messed up. I attached an export to see what i'm talking about. See line 8

I'm trying to figure out how to strip the "street" column of breaks (if that's what's even happening?)

Thanks in advance.


<?
&#9;//
&#9;// Magento CSV Report Script -  Unprocessed Sales Orders
&#9;//
&#9;
&#9;// DATABASE CONNECTION
&#9;
&#9;
&#9;$hostname_MySQLCon="localhost";
&#9;$database_MySQLCon = "db";
&#9;$username_MySQLCon = "user";
&#9;$password_MySQLCon = "password";

&#9;$MySQLCon = mysql_pconnect($hostname_MySQLCon, $username_MySQLCon, $password_MySQLCon);
&#9;$MySQLDb = mysql_select_db($database_MySQLCon, $MySQLCon);
&#9;
&#9;// FUNCTION AREA
&#9;
&#9;function get_DB_Row($query){
&#9;&#9;$result = @mysql_query($query);
&#9;&#9;$numresults = @mysql_num_rows($result);
&#9;&#9;
&#9;&#9;for ($i = 0; $i < $numresults; $i++) 
&#9;&#9;{
&#9;&#9;&#9;$row = @mysql_fetch_array($result);
&#9;&#9;}
&#9;&#9;
&#9;&#9;return $row;
&#9;}
&#9;
&#9;// Using this temporary function until I find correct way to pull country names from the database according to two digit code
&#9;// Put in your own until a database polled solution is uncovered
&#9;
&#9;function getCountryName($code){
&#9;&#9;switch($code) {
&#9;&#9;&#9;case "GB" &#9;:&#9;$country = "United Kingdom";
&#9;&#9;&#9;&#9;&#9;&#9;&#9;break;
&#9;&#9;&#9;case "IE" &#9;:&#9;$country = "Ireland";
&#9;&#9;&#9;&#9;&#9;&#9;&#9;break;
&#9;&#9;}
&#9;&#9;
&#9;&#9;return $country;
&#9;}
&#9;
&#9;// INITILISATION AREA
&#9;
&#9;$sitename = "boyesen.com";  &#9;&#9;&#9;&#9;// update this to customise your filename
&#9;$datestring = date('j-m-y');
&#9;$filename = $sitename."-".$datestring;
&#9;$output = "";
&#9;
&#9;$fieldlist = array("orderid","firstname","lastname","address","city","region","country",
&#9;"postcode","telephone","email","itemcode","itemname","quantity","shipping_amount","shipping_instructions","order_date");
&#9;
&#9;$checklist = array("country", "quantity");
&#9;
&#9;// FIELDLIST INSTRUCTIONS 
&#9;// The order of the CSV fields are set here as well as the field titles
&#9;// The field titles must correspond to a number of SQL Variables being set with the AS operator below
&#9;// If you change the default titles be sure to also update the SQL command accordingly
&#9;// Please note that 'country' and 'quantity' values must be processed but should still be put in your prefered sequence
&#9;
&#9;$numfields = sizeof($fieldlist);
&#9;
&#9;// *********************   NOW START BUILDING THE CSV
&#9;
&#9;// Create the column headers
&#9;
&#9;for($k =0; $k < $numfields;  $k++) { 
&#9;&#9;$output .= $fieldlist[$k];
&#9;&#9;if ($k < ($numfields-1)) $output .= ", ";
&#9;}
&#9;$output .= "\n";
&#9;
&#9;

&#9;$orderquery = "SELECT * FROM sales_order";  &#9;// Query to find unprocessed orders
&#9;$orderresult = mysql_query($orderquery);
&#9;$numorders = @mysql_num_rows($orderresult);
&#9;
&#9;for($i =0; $i < $numorders; $i++) {
&#9;&#9;$order = @mysql_fetch_array($orderresult);&#9;// Place each order into an array for proccessing
&#9;&#9;$orderid = $order['entity_id'];   &#9;&#9;&#9;// Grab the orderid for use in the main SQL command
&#9;&#9;
&#9;&#9;// The following SQL command will find all the individual items associated with this order
&#9;&#9;
&#9;&#9;$itemquery = &#9;"SELECT sales_flat_order_item.order_id AS orderid,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_order_item.product_type,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_order_item.quote_item_id,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_order_item.parent_item_id AS parentid,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_order_item.sku AS itemcode,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_order_item.name AS itemname,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_order_item.qty_ordered AS qty_ordered,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.shipping_amount AS shipping_amount,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_order_item.qty_shipped AS qty_shipped,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.email AS email,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.prefix AS title,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.firstname AS firstname,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.lastname AS lastname,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.street AS address,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.city AS city,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.region AS region,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.country_id AS country,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.postcode AS postcode,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.telephone AS telephone,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.shipping_description AS shipping_instructions,
&#9;&#9;&#9;&#9;&#9;&#9;sales_flat_quote_address.created_at AS order_date
&#9;&#9;&#9;&#9;&#9;&#9;FROM sales_flat_order_item 
&#9;&#9;&#9;&#9;&#9;&#9;INNER JOIN sales_flat_quote_item
&#9;&#9;&#9;&#9;&#9;&#9;ON sales_flat_order_item.quote_item_id = sales_flat_quote_item.item_id
&#9;&#9;&#9;&#9;&#9;&#9;INNER JOIN sales_flat_quote_address
&#9;&#9;&#9;&#9;&#9;&#9;ON sales_flat_quote_item.quote_id = sales_flat_quote_address.quote_id
&#9;&#9;&#9;&#9;&#9;&#9;WHERE sales_flat_order_item.order_id = $orderid
&#9;&#9;&#9;&#9;&#9;&#9;AND sales_flat_quote_address.address_type = 'shipping' 
&#9;&#9;&#9;&#9;&#9;&#9;AND sales_flat_order_item.product_type <> 'configurable'
&#9;&#9;&#9;&#9;&#9;&#9;AND sales_flat_order_item.qty_shipped < sales_flat_order_item.qty_ordered";
&#9;&#9;&#9;&#9;&#9;&#9;
&#9;&#9;$itemresult = mysql_query($itemquery);&#9;&#9;// Run the query
&#9;&#9;$numitems = @mysql_num_rows($itemresult);   // Check the number of items in the order
&#9;&#9;
&#9;&#9;for($j =0; $j < $numitems; $j++) {
&#9;&#9;&#9;$item = @mysql_fetch_array($itemresult); // Place the item in an array for processing
&#9;&#9;&#9;
&#9;&#9;&#9;// Output the order item values in the same sequence set in the fieldlist to match headers
&#9;&#9;&#9;
&#9;&#9;&#9;for($m =0; $m < sizeof($fieldlist); $m++) { 
&#9;&#9;&#9;&#9;
&#9;&#9;&#9;&#9;$fieldvalue = $fieldlist[$m];
&#9;&#9;&#9;&#9;
&#9;&#9;&#9;&#9;if(in_array($fieldvalue, $checklist)) {    &#9;&#9;&#9;&#9;// check if on special processing list
&#9;&#9;&#9;&#9;
&#9;&#9;&#9;&#9;&#9;if($fieldvalue == "country") $output .= getCountryName($item[$fieldvalue]);
&#9;&#9;&#9;&#9;&#9;
&#9;&#9;&#9;&#9;&#9;if($fieldvalue == "quantity") {
&#9;&#9;&#9;&#9;&#9;&#9;$parentid = $item['parentid'];
&#9;&#9;&#9;&#9;&#9;&#9;if($parentid == 'NULL') {  &#9;&#9;&#9;&#9;&#9;&#9;// simple product sold on its own (no parent)
&#9;&#9;&#9;&#9;&#9;&#9;&#9;$qty_ordered = $item["qty_ordered"];
&#9;&#9;&#9;&#9;&#9;&#9;&#9;$qty_shipped = $item["qty_shipped"];
&#9;&#9;&#9;&#9;&#9;&#9;} else { &#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;// simple product was part of bundle or configurable group
&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;&#9;// If so, use the parent quanities to calculate
&#9;&#9;&#9;&#9;&#9;&#9;&#9;$parentitem = get_DB_row("SELECT * FROM sales_flat_order_item WHERE item_id = $parentid");
&#9;&#9;&#9;&#9;&#9;&#9;&#9;$qty_ordered = $parentitem["qty_ordered"];
&#9;&#9;&#9;&#9;&#9;&#9;&#9;$qty_shipped = $parentitem["qty_shipped"];
&#9;&#9;&#9;&#9;&#9;&#9;}
&#9;&#9;&#9;&#9;&#9;&#9;$output .= ($qty_ordered - $qty_shipped);
&#9;&#9;&#9;&#9;&#9;}
&#9;&#9;&#9;&#9;&#9;
&#9;&#9;&#9;&#9;} else {
&#9;&#9;&#9;&#9;&#9;$output .= $item[$fieldvalue];
&#9;&#9;&#9;&#9;}
&#9;&#9;&#9;&#9;
&#9;&#9;&#9;&#9;if ($m < ($numfields-1)) $output .= ", ";
&#9;&#9;&#9;}
&#9;&#9;&#9;
&#9;&#9;&#9;$output .= "\n";
&#9;&#9;}
&#9;&#9;
&#9;&#9;
&#9;}
&#9;
&#9;// Send the CSV file to the browser for download
&#9;
&#9;header("Content-type: text/x-csv");
&#9;header("Content-Disposition: attachment; filename=$filename.csv");
&#9;echo $output;
&#9;exit;
&#9;
?>

Open in new window

testexport-1-09-10.xls
0
Comment
Question by:remedy_rider
8 Comments
 
LVL 4

Assisted Solution

by:EFernandes
EFernandes earned 100 total points
ID: 33630261
Check if your data on the db contain embedded line breaks like this:

firstname: Chrissy              
lastname: Parr
address: 1020 james drive
                Suite 101

A field that contains embedded line-breaks must be surounded by double-quotes so:

firstname: Chrissy              
lastname: Parr
address: "1020 james drive
                Suite 101"

You should add the double-quotes to the string.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 33630282
I cannot really follow the code but maybe I can help a little anyway.

You can (and should) use var_dump() to print out variables when you're not sure what they contain.  It will reveal a multitude of valuable information.

You can (and should) normalize the addresses in your data base.  You can do this with a call to the Google Geocoder.  It will return the correct, normalized address.

This function is how to write a CSV correctly:
http://us3.php.net/manual/en/function.fputcsv.php

You might also want to ask a moderator to remove your credentials from the code snippet.

HTH, ~Ray
0
 
LVL 3

Accepted Solution

by:
Marbleman earned 300 total points
ID: 33630409
try this:

Change Line 149 to

$output .= str_replace("\n", " ", $item[$fieldvalue]);


0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:remedy_rider
ID: 33630472
GEEEZE i'm flakin here! can't believe i left those in the code. thanks for pointing that out Ray!

How do i add the double quotes when it's coming into an array. Sorry i'm a  php novice. I was trying to find where it inserts via magento, but that's like finding a needle in a haystack.
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
ID: 33630508
fputcsv() will do most of what you need.  Like all PHP functions it is documented in the online man pages:
http://us3.php.net/manual/en/function.fputcsv.php
0
 

Author Comment

by:remedy_rider
ID: 33630638
Ok, i'm still not sure what the best way to alot points is? Marbleman's fix worked like a charm, but everyone elses input pointed me to some good info.
0
 

Author Comment

by:remedy_rider
ID: 33640776
thank you modus. that's what i get for being in a hurry. arrgh.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Replacement for Great Plain Dynamics 22 57
XML extra information 8 29
Custom Wordpress Loop 22 38
Ajax and PHP 9 29
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question