Set separator delimiter in CSV file to open correctly in Excel

No Comments

Problem

I made a PHP script to export a SQL query (from MySql) to a CSV file for opening in Excel. I cannot control how different users have their Excel setup, ie what separator delimiter they have set (this differs with language settings or user may have changed setting)

So the field separator delimiter gave me some problems, ie Excel would not automatically open file columns correctly but put each row in a single cell, unless you use Import data to make it work.

Some forums suggested setting the separator delimiter in the csv file (delimiter is third argument for fputcsv) to tab (\t) but that did not help.

Solution (use csv header sep=)

Then I stumbled upon the possibility to add a parameter as a header to the actual csv file, simply add sep=<delimiter> to the beginning of your csv file and Excel will parse it. So for separator delimiter use comma (,), pipe (|), tab (\t) or whatever you set in your php code.

However I found a few caveats:

  • “sep=\t” needs to be in quotes (otherwise it will use header but also for some reason also show it as data in the spreadsheet and put it in its own cell and move all other cells on that row one position to the right)
  • You need to add newline \n after the sep= header (otherwise it will use header but also show it as data concatenated with first cells data)
  • Use fwrite as fputcsv will not work

Example to use tab as separator:

fwrite($fp, "\"sep=\t\"\n");

 

I could not find much info on the subject of csv headers but this is the full thread that lead me on the to it (thanks Burhan Ali)

Tested with Excel 2016 on Windows 10 and Excel for Mac (15.25) on OSX El Capitan (10.11.15).

I have not tested on other spreadsheets software like OpenOffice, LibreOffice etc, so please share in the comments if you have any success with this.

Solution: Bootstrap glyphicons-halflings-regular.woff 404 not found

17 Comments

I’m testing out Bootstrap 3.1.1 by redoing the design of a very old site of mine, running on Php 5.3 on a Windows 2008/IIS 7 server.

The new version of the site has been slow at loading the icons (wich are a Glyphicons web font) for buttons and when I check the Javascript console in Chrome (Ctrl+Shift+J) it shows the following error:

GET /fonts/glyphicons-halflings-regular.woff 404 (Not Found)

After much testing and Googling it turns out my old IIS server does not have Mime types registered for .woff files.

So I go in to the IIS console, click MIME Types icon and add .woff as application/octet-stream – problem solved!

Split text into multiple columns in PHP

4 Comments

I made a Swedish shopping directory using affiliate links for a site and wanted to split it into two columns.

I wrote the following php function that can be used to split a string in two or more pieces. One parameter is at what character or string you want to split. For example if you want to split at a space to not break up words, or split at a period to not split up sentences, at certain html tags such as <h1> to keep headlines and text in same column etc.

The function returns a string array with columns.

Example how to use:

	$TextString = "some text to split")
	$Columns = SplitIntoColumns ($TextString, 2, "<h1>");
	echo ($Columns[0]);
	echo ($Columns[1]);

Function

function SplitIntoColumns ($InputString, $Columns, $SplitString) {

	// Source: http://blog.tjitjing.com
	// 
	// Splits a string into x number of columns and returns result as an array of columns
	//
	// Parameters:
	// $InputString	String to be split into columns
	// $Columns		Number of columns
	// $SplitString	String to look for to not split midtext etc
	//
	// Change history:
	// 2011-01-25/Max	Version 1
	//

	// Find middle of text
	$ColLength = strlen($InputString)/$Columns;

	// Split into columns
	for($ColCount = 1; $ColCount <= $Columns; $ColCount++) {

		// Find $SplitString, position to cut
		$Pos = strpos($InputString , $SplitString , $LastPos + $ColLength);
		if ($Pos === false) {
			$Pos = strlen($InputString);
		}

		// Cut out column
		$Output[$ColCount - 1] = substr($InputString, $LastPos, $Pos - $LastPos);

		$LastPos = $Pos;
	
	}

	return $Output;

 }

[Edit: VB.NET version: Split text in two columns in VB.NET]

Install cURL PHP Extension on Windows IIS

1 Comment

I tried to run a new PHP script that someone else wrote for me on my Windows Server 2008 and got some errors relating to cURL such as this:

Fatal error: Call to undefined function curl_multi_init()

First I spent a lot of time trying to find information on how to install cURL on a Windows IIS installation, downloaded the cURL package, moved around files, copied to c:\windows\system32, updated php.ini and did all sorts of things described in forums, blogs and manuals but could not get it to work.

Finally I decided to upgrade my PHP from 5.3.1 to the latest 5.3.2 release. I downloaded the PHP Windows MSI installer and there in the Setup Wizard you can simply select what extensions you want to install. Selected cURL and it worked like a charm 🙂 I guess it was just to easy…

(I post about this not only to let everyone know how stupid I am but since I could not find this info anywhere it could be good to document it)

I also tried running the installer again in “Change” mode to add and remove Extensions and the installer seems to be doing a pretty good job at adding and removing files + updating php.ini the way you would expect it to.

At one point I tried installing all Extensions but that got me in to trouble giving me a “oci.dll not found” trying to load an Oracle dll. My tip here is to install only the extensions you need or at least not any related to databases you do not use.

After clicking “Next” when you have selected what Extensions to install, the setup wizard may tell you files are locked and you have to restart the server after installing. A way to avoid this can be to (before clicking “Next”) stop the World Wide Web Publishing Service (from command line: NET STOP W3SVC). Just remember to start it again after!

Extensions available from install package in 5.3.2
bzip2
Curl
Enchant
EXIF
Fileinfo
GD2
Gettext
GMP
IMAP
Internationalization (intl)
LDAP
Multi-Byte String
MySQL
MySQLi
OpenSSL
Oracle (10)
Oracle (11g)
PDO
PostgreSQL
Shared Memory
SNMP
SOAP
Sockets
SQLite
SQLite 3
Tidy
XML-RPC
XSL

PHP 5.3.1 upgrade on IIS7 and MySQL gives Internal Server Error

3 Comments

Solution

After a lot of googling and testing I finally found Bruce Kirkpatrick’s comment at the end of the PHP install manual

Seems it is no longer enough to have localhost in the servers local hosts file (C:\Windows\System32\drivers\etc\hosts) you also have to comment out or remove the IPv6 equivalent.

127.0.0.1 localhost
#::1 localhost

After I did this everything worked ok again. Thanks Bruce!

Problem

I just upgraded PHP on my Windows Server 2008 running IIS7 from PHP 5.2.10 to 5.3.1. Used the non-threaded install package and installed for FastCGI.

After the upgrade installation worked fine for some non-database driven pages and phpinfo worked fine but whenever a page tries to accessed the MySQL database the browser only gave me a 500 Internal Server Error.

Checking the php error log (C:\Windows\Temp\php-errors.log) I get messages like this:

[24-Feb-2010 13:44:36] PHP Warning: mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: [2002] A connection attempt failed because the connected party did not (trying to connect via tcp://localhost:3306) in C:\inetpub\wwwroot\XXX\init_page.inc on line 32
[24-Feb-2010 13:44:36] PHP Warning: mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
in C:\inetpub\wwwroot\XXX\init_page.inc on line 32
[24-Feb-2010 13:44:36] PHP Fatal error: Maximum execution time of 60 seconds exceeded in C:\inetpub\wwwroot\XXX\init_page.inc on line 32

PHP 5 with IIS 7 on Windows Server 2008 Tutorial

1 Comment

A few years back I wrote a simple tutorial on how to install and configure PHP5 with IIS6 on Windows Server 2003 in five easy steps. Back then things were not as straight forward as they turned out to be when I tried it again today on a different server setup.
The following worked like a charm on my Windows Server 2008 SP2 Standard Edition to install and configure PHP 5.2.10.

This tutorial presupposes that you have your Windows Server 2008 up and running with IIS (Internet Information Services) configured.

So this time I’m down to four easy steps for installing PHP:

  1. For reasons unknown to me ISAPI is no longer an option so make sure you have activated CGI support on your Windows Server, it is not done by default. Open up Server Manager, select Roles from navigation tree, and under Role Services click Add Role Services and make sure Web Server – Application Development – CGI is checked.
  2. Download the latest version of PHP. Download the PHP installer version found under Windows Binaries.
  3. Run the downloaded MSI install pacakge to install PHP on your server. When asked what web server version to install select IIS FastCGI.
  4. Done!

To test your PHP installation simply create text a file with the php extension, eg. test.php. Add the following three lines of text to it and then save it to your web site directory, eg. C:\INETPUB\WWWROOT. Then use your browser to read the file, eg. http://localhost/test.php

<?php
phpinfo();
?>

Some other tips:

  • If you want to use PHP with MySQL (or any other extensions) make sure you click this option when you install. Don’t worry if you miss it you can run install again at a later time to reconfigure.

PHP5 with IIS6 on Windows Server 2003 in five easy steps

230 Comments

[Update: Follow this link if you are looking for an updated tutorial on PHP5 with IIS7 on Windows Server 2008]

How to install and configure PHP5 with IIS6 on Windows Server 2003 in five easy steps.

I thought installing PHP would be as easy as just using the install program. Well it wasn’t… But by learning from my mistakes it can be almost that easy.

Just follow these five easy steps on how to install and configure PHP 5 on a Windows Server 2003 running IIS – after trying the manual and several guides and tutorials found on the net (of which some were very long and very complicated and some were short and easy but partially incorrect) this is what it all came down to. Enjoy.

This is tested on PHP 5.0.4, IIS6 on Windows Server 2003 SP1. Be aware that IIS is not automatically installed with all editions of Windows Server 2003, this guide assumes that IIS 6 and Windows Server 2003 is already up and running on your server.

1. Download PHP at http://www.php.net/downloads.php. Make sure you dowload the “zip package”, the installer package won’t work.

2. Extract the downloaded zip file to a directory of choice on your harddrive. The rest of this guide will assume you are using C:\PHP

3. Add C:\PHP to your path. From the Start menu, right click My Computer and select Properties. From the Advanced tab click the Environment Variables button. Under System Variable find Path and click Edit. At the end of what is already present in Variable Value add a semicolon (;) and then C:\PHP.

4. Configure IIS. Open the Internet Information Services (IIS) Manager from Administrative Tools (found directly in the Start menu or in the Control Panel)

a) Web Service Extension. Click down to the Web Service Extension folder. Right click the folder and select Add New Web Service Extension. Set Extension Name to .PHP and add C:\PHP\PHP5ISAPI.DLL to Required Files . Check Set Extension Status To Allowed.

b) Web Sites. Click down to Web Sites. Right click the folder and select Properties. From the Home Directory tab click the Configuration button. Click Add to add an Application Extension. Enter C:\PHP\PHP5ISAPI.DLL as Executable and PHP as Extension. Leave the rest as default and click Ok.

5. All set! To test your PHP installation simply create text a file with the php extension, eg. test.php. Add the following three lines of text to it and then save it to your web site directory, eg. C:\INETPUB\WWWROOT. Then use your browser to read the file, eg. http://localhost/test.php

<?php
phpinfo();
?>

Other things you might want to consider:

– Some tutorials state that you need to restart the World Wide Web Publishing Service after having installed and configured php. I haven’t needed to (ie everything worked fine without restarting the service) but if you do it is found under Services in the Control Panel.

– As you start using PHP, in the not too distant future you will probably need to make some changes to php.ini. PHP works fine without the php.ini file but you really should have one and it should be in your Windows directory. Copy C:\PHP\PHP.INI-RECOMMENDED to C:\WINDOWS\PHP.INI

– If you are going to use MySQL you will need to make sure to uncomment the line “extension=php_mysql.dll” in php.ini and copy C:\PHP\LIBMYSQL.DLL to C:\WINDOWS\SYSTEM32 (Simply setting the PATH won’t work as this is apparently hard coded in PHP5). If you don’t follow these steps you will get an error message similar to this: “Call to undefined function mysql_connect”

– Read the manual at http://www.php.net/manual/en/install.windows.php

[Updated: If you have any problems, make sure you read the comments to this post. You will find both tips and questions there. If you know the answer to someone’s question please answer it.]

(This article is previously published and has been moved to this blog)