Tag: PHP

Converting Large XLS, XLSX files to CSV using Python

I was in troubles to convert big xlsx files in PHP because there is no such a library that could make it as good as possible. What I’ve found out were libraries that could manage xls / xlsx files but it took a lot of time and performance was horrible.

So, not spending too much time to think about it I’ve figured out to leave PHP and try to make it in Python. What I wanted to achive was converting xls files into csv and handle it in PHP.

In Python we got xlrd library that enables us to make this “crazy” thing. Here it is: https://pypi.python.org/pypi/xlrd. You can install it using pip, such like:

$ pip install xlrd

1	$ pip install xlrd

But for my case it was better to use VirtualENV which is better solution when someday you’d like to move your software somewhere and you forgot about installing all those stuff. However, each way you chose would be fine till it’s working.

After installing required items all you need is to run the script that converts xls into csv. I have already written simple script that can make it. Please find code below and save it to your local system setting the proper permissions right (chmod +x filename).

convert_xls.py

import logging
import time
import traceback
import xlrd
import csv
import sys
import re

logging.basicConfig(level=logging.INFO, format='%(asctime)s %(message)s')


def csv_from_excel():
    xls = sys.argv[1]
    target = sys.argv[2]

    logging.info("Start converting: From '" + xls + "' to '" + target + "'. ")

    try:
        start_time = time.time()
        wb = xlrd.open_workbook(xls)
        sh = wb.sheet_by_index(0)

        csvFile = open(target, 'wb')
        wr = csv.writer(csvFile, quoting=csv.QUOTE_ALL)

        for row in xrange(sh.nrows):
            rowValues = sh.row_values(row)

            newValues = []
            for s in rowValues:
                if isinstance(s, unicode):
                    strValue = (str(s.encode("utf-8")))
                else:
                    strValue = (str(s))

                isInt = bool(re.match("^([0-9]+)\.0$", strValue))

                if isInt:
                    strValue = int(float(strValue))
                else:
                    isFloat = bool(re.match("^([0-9]+)\.([0-9]+)$", strValue))
                    isLong  = bool(re.match("^([0-9]+)\.([0-9]+)e\+([0-9]+)$", strValue))

                    if isFloat:
                        strValue = float(strValue)

                    if isLong:
                        strValue = int(float(strValue))

                newValues.append(strValue)

            wr.writerow(newValues)

        csvFile.close()

        logging.info("Finished in %s seconds", time.time() - start_time)

    except Exception as e:
        print (str(e) + " " +  traceback.format_exc())


csv_from_excel()

import logging

import time

import traceback

import xlrd

import csv

import sys

import re

logging.basicConfig(level=logging.INFO, format='%(asctime)s %(message)s')

def csv_from_excel():

xls = sys.argv[1]

target = sys.argv[2]

logging.info("Start converting: From '" + xls + "' to '" + target + "'. ")

try:

start_time = time.time()

wb = xlrd.open_workbook(xls)

sh = wb.sheet_by_index(0)

csvFile = open(target, 'wb')

wr = csv.writer(csvFile, quoting=csv.QUOTE_ALL)

for row in xrange(sh.nrows):

rowValues = sh.row_values(row)

newValues = []

for s in rowValues:

if isinstance(s, unicode):

strValue = (str(s.encode("utf-8")))

else:

strValue = (str(s))

isInt = bool(re.match("^([0-9]+)\.0$", strValue))

if isInt:

strValue = int(float(strValue))

else:

isFloat = bool(re.match("^([0-9]+)\.([0-9]+)$", strValue))

isLong = bool(re.match("^([0-9]+)\.([0-9]+)e\+([0-9]+)$", strValue))

if isFloat:

strValue = float(strValue)

if isLong:

strValue = int(float(strValue))

newValues.append(strValue)

wr.writerow(newValues)

csvFile.close()

logging.info("Finished in %s seconds", time.time() - start_time)

except Exception as e:

print (str(e) + " " + traceback.format_exc())

csv_from_excel()

If you have a python script created in your local filesystem the next step would be manage it from PHP. And this is most simplest solution. Using exec, system, or shell_exec solves the problem.

<?php
exec(‘/usr/bin/python convert_xls.py input.xls out.csv’);
?>

<?php

exec(‘/usr/bin/python convert_xls.py input.xls out.csv’);

OR in command line.

$ /usr/bin/python convert_xls.py input.xls out.csv

1	$ /usr/bin/python convert_xls.py input.xls out.csv

I know that solution is not really clean but it works. To have it more elegant I would suggest to create some kind of service in Python that handles it in a good way. I will think about it …

Note: I have also fixed issue with Long numbers in XLS.

Update
If you need to parse big XLS, XLSX files I would suggest not to make in in PHP. Just parse this using JAVA where PHP would be used only for UI.

Any questions, let me know. Please find comment form below.

21 Comments by strife / December 10, 2014 / Posted in: PHP, Programming, Python

Tagged: chmod, csv, exec, PHP, python, VirtualENV, xlrd, xls, xlsx

How to Modify Sth After Submiting in Drupal 6

In Drupal 6 we have some constructions which are very helpful. You want to modify sth after node has been submited? No problem you can easly do it by using hooks. In some module create some functions which will called:

function my_module_form_alter(&$form, $form_state, $form_id) {
  if($form_id == 'name_of_your_form') {
    $form['#after_build'][] = '_my_module_after_build';
  }
}
function _my_module_after_build(&$form, $form_state) {
    // here you should add that piece of code
    // 	$form['#redirect'] = 'your-page-after-submit'; 
  	$form['#submit'][] = 'my_module_action_after_submit';
    
}
function my_module_action_after_submit($form, &$form_state) {
 // and here you have all you want, you can modify $form_state variable
 // to change status or other variables in node
 // $form_state['values']['status'] = false;
   return $form_state;
}

function my_module_form_alter(&$form, $form_state, $form_id) {

if($form_id == 'name_of_your_form') {

$form['#after_build'][] = '_my_module_after_build';

}

function _my_module_after_build(&$form, $form_state) {

// here you should add that piece of code

// $form['#redirect'] = 'your-page-after-submit';

$form['#submit'][] = 'my_module_action_after_submit';

}

function my_module_action_after_submit($form, &$form_state) {

// and here you have all you want, you can modify $form_state variable

// to change status or other variables in node

// $form_state['values']['status'] = false;

return $form_state;

}

And that’s all you should know about it. Now you can simply modify whatever you want to modify.

See you!

No Comments by strife / October 9, 2011 / Posted in: Programming

Tagged: alter, drupal, drupal6, form, hooks, node, PHP, submit

Getting Cart From Prestashop

If you have a problem with getting cart from prestashop that would be interesting for you.

I had a huge problem with Prestashop (version 1.4.3) because I didn’t know how exactly I should get cart from Prestashop to the WordPress or somewhere else. However I spent some time to resolve that issue and I wrote that:

First of all we should get session data from Pretashop:

require_once dirname(__FILE__) . '/config/config.inc.php' ;
require_once(dirname(__FILE__) . '/config/settings.inc.php');
require_once dirname(__FILE__) . '/init.php' ; // this initializes the Cookie singleton, which is available in any script

function getAllCookies(){
global $cookie ;

// var_dump($cookie);

return $cookie;

}

$cookie = getAllCookies();

require_once dirname(__FILE__) . '/config/config.inc.php' ;

require_once(dirname(__FILE__) . '/config/settings.inc.php');

require_once dirname(__FILE__) . '/init.php' ; // this initializes the Cookie singleton, which is available in any script

function getAllCookies(){

global $cookie ;

// var_dump($cookie);

return $cookie;

}

$cookie = getAllCookies();

As you see you should add some lines from Prestashop configuration and then you should create a function in which you have cart ID. And then you can easily get all data from the cart:

$query = "SELECT p.id_product, pl.name, p.quantity, pp.price, p.id_product_attribute, pp.price * cu.conversion_rate AS orderprice, cu.sign, cu.format FROM ps_cart c
LEFT JOIN ps_cart_product p ON (p.id_cart = c.id_cart)
LEFT JOIN ps_product pp ON (pp.id_product = p.id_product)
LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)
LEFT JOIN ps_currency cu ON (cu.id_currency = c.id_currency)

WHERE c.id_cart = " . (int) $cookie->id_cart . " AND pl.id_lang = 1
ORDER BY p.date_add ASC";


$link1 = mysql_connect(_DB_SERVER_, _DB_USER_, _DB_PASSWD_);

if ($link1) {
$db_selected = @mysql_select_db(_DB_NAME_, $link1);
$res = mysql_query($query);
$arrProducts = array();
while ($row = mysql_fetch_assoc($res)) {
$arrProducts[] = $row;
}

var_dump($arrProducts); // all products from session cart
}

mysql_close($link1);

$query = "SELECT p.id_product, pl.name, p.quantity, pp.price, p.id_product_attribute, pp.price * cu.conversion_rate AS orderprice, cu.sign, cu.format FROM ps_cart c

LEFT JOIN ps_cart_product p ON (p.id_cart = c.id_cart)

LEFT JOIN ps_product pp ON (pp.id_product = p.id_product)

LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product)

LEFT JOIN ps_currency cu ON (cu.id_currency = c.id_currency)

WHERE c.id_cart = " . (int) $cookie->id_cart . " AND pl.id_lang = 1

ORDER BY p.date_add ASC";

$link1 = mysql_connect(_DB_SERVER_, _DB_USER_, _DB_PASSWD_);

if ($link1) {

$db_selected = @mysql_select_db(_DB_NAME_, $link1);

$res = mysql_query($query);

$arrProducts = array();

while ($row = mysql_fetch_assoc($res)) {

$arrProducts[] = $row;

}

var_dump($arrProducts); // all products from session cart

}

mysql_close($link1);

(Sorry for quality of code but I had to wrote this as quick as possible)

And that’s all

14 Comments by strife / September 30, 2011 / Posted in: PHP, Programming

Tagged: basket, cart, PHP, prestashop, prestashop 1.4.3

Archives
- February 2015 (2)
- December 2014 (1)
- July 2014 (1)
- June 2014 (2)
- May 2013 (1)
- June 2012 (1)
- March 2012 (1)
- February 2012 (1)
- December 2011 (3)
- November 2011 (1)
- October 2011 (2)
- September 2011 (2)
Recent Comments
- TerryJiz on Converting Large XLS, XLSX files to CSV using Python
- Feederoyo on Converting Large XLS, XLSX files to CSV using Python
- AllenTug on Converting Large XLS, XLSX files to CSV using Python
- Businesstbp on Converting Large XLS, XLSX files to CSV using Python
- Flukeero on Converting Large XLS, XLSX files to CSV using Python
Recent Posts

strife's devLog

Converting Large XLS, XLSX files to CSV using Python

How to Modify Sth After Submiting in Drupal 6

Getting Cart From Prestashop

Archives

Recent Comments

Recent Posts