Excel, CSV and SYLK

Anyone who often works with CSV exports programmatically may have had this issue before, and I am sure that you may have been confused about what is causing it. You may have gotten one of the below, or both.

Error message one:

Excel has detected that ‘exported_file.csv’ is an SYLK file, but cannot load it. Either the file has error or it is not a SYLK file format. Click OK to try to open the file in a different format.

Error message two:

SYLK: File format is not valid

The good news is that it has a very simple solution:

If the first letters of the file is “ID”, you will get this problem. In my case, I had to rename the column header from “ID” to “Record ID” to solve the problem.

View the solution directly on Microsoft’s website. They suggest that you can also change “ID” to “id”. For me, that was not an option; I am pedantic about writing style in my reports. There is also a suggestion to add an apostrophe. Also viable, but my solution works easier for me. Do whatever works best for you!

Select all columns except one from a MySQL table

I am not sure how often you would stumble upon this problem, however, I did recently, and after some research, found a nifty way to overcome it.

Here is the scenario:

  • You want to import a large CSV file into a database table.
  • The files have potential to be very large, therefore you do not want to get rid of the old data before you have imported the new data successfully into a temporary table.

How do you do this?

  • You import your data into a temporary table, let’s call it `tempdata`. This will take some time with large files, especially if you are processing each line before inserting.
  • Once the import is successful, you simply and copy from the `tempdata` to the `realdata` table by doing a simple query: INSERT INTO `realdata` SELECT * FROM `tempdata`. While this would work when there are no keys defined, you run into problems when you have UNIQUE or PRIMARY keys defined, because the key columns in the `tempdata` and `realdata` tables will overlap.

Oops. Now what?

You have two possible solutions:

  • Select only the columns that you want to import from `tempdata` and import into the same columns into `realdata`, or
  • You can be a little adventurous if you do not know beforehand what all the column names are (for example when you create tables on the fly).

Here is how you bring the adventure into your SQL mission:

First, you need to get the columns from the `realdata` table from the INFORMATION SCHEMA table:

SELECT `column_name` FROM information_schema.columns WHERE `table_name` = '" . $table_from . "' and column_name != 'id'

Now, this is assuming that your primary key or unique key’s name is “id”.

The result of this query will then contain all the columns in the `realdata` table except the ID column.

Now, with some PHP magic, you build up your query using these names and some string concatenation, and you can simply do this:

INSERT INTO `realdata` (**COLSTRING**) SELECT **COLSTRING** FROM `tempdata`

Voila!

[REVIEW] Laravel Design Patterns and Best Practices

I saw a post on LinkedIN inviting people to review a new book, and I thought – what the heck. I have reviewed books before, so this could be fun. So – I signed up, and was asked to blog about the book when I am done.

The book I had to review was “Laravel Design Patterns and Best Practices”. Written by Arda Kılıçdağı and H. İbrahim YILMAZ. The book is available at Packt Publishing.

As I am reading the first few chapters, I was generally impressed. As a beginner in Laravel, I found it easy, informative, and the usage of analogies were quite clever. This resonated well with me, as I learn well by understanding difficult concepts by analogy.

Talking of analogies, I am going to describe my overall experience with the book using an analogy. Imagine a comic book with nice big letters. It reads nice. The text forms pictures in your mind as you read them. Awesome. As you turn each page in the comic book, you get more immersed in the book, and you really start to enjoy it.

But suddenly, when you turn the page somewhere near the middle of the comic book, things get a little hairy. Suddenly, you are reading a printed book. No more pictures and very small print that is highly condensed with very little line spacing.

What happened here is one of two things:

  • The book was aimed at absolute beginners, and then switched to advanced level without a gradual build-up. It became far too difficult for me to understand.
  • The book was aimed at intermediate Laravel developers, but the inclusion of the first few chapters were to fill the pages: It is far too elementary for intermediate Laravel developers.

Now – on the Packt website, the following paragraph is found:

This book is intended for web application developers working with Laravel who want to increase the efficiency of their web applications. It assumes that you have some experience with the Laravel PHP framework and are familiar with coding OOP methods.

That brings me to the second possibility I have mentioned: that the book is aimed at intermediate Laravel developers, but that the first few chapters were only fillers, being too elementary to teach intermediate Laravel developers anything.

So, while it was a good read for me up to the point where the book lost my attention, I would recommend that:

  • If you are an intermediate or advanced Laravel developer, PLEASE skip the first few chapters.
  • If you are a beginner Laravel developer, PLEASE read only the first few chapters, and tackle the last bunch of chapters only once you have gotten some more experience under the belt.

I have read several Packt books before, and I generally like them, but I have to say I am slightly disappointed by this one. That being said, it is purely my own opinion, and you might find the experience with this book a lot better than I did.

Happy reading!

Reveal.js – a marvelous toy

Recently, I have done a project called Renault Société. The technologies used here were:

  • iCMS, my own custom written CMS, based on CodeIgniter (a PHP-based framework).
  • jQuery, HTML5 and CSS3
  • Reveal.js (Github)

The result is exceeding my expectations by a million miles. Reveal.js is at the center of this magazine. While iCMS is managing the content management of the magazine, Reveal.js is in control of the presentation layer.

I have investigated the source code of Reveal.js, and found it clean, easy to understand, and it caters for extreme customization. How often do you see (or hear) that “all sites based on a certain framework” are all looking the same? I have heard several complaints of that ranging from Drupal to Joomla!, as well as WordPress and other popular open source systems. My opinion is that when this is the truth, the people designing the presentation layer thereof is simply too lazy to dig in and customize it properly.

The showcase for Reveal.js is largely a case in point. Many of the showcases have plainly followed the base template developed by Hakim El Hattab, the author of Reveal.js. Very few of the submissions are deviating from this base template, and I am proud to announce that our site is vastly different from most of those out there, and also better looking.

I would just like to use this opportunity to thank Hakim for his brilliant project. Thanks for all the hard work. It is an amazing project!

Feel free to browse around Hakim’s website. You will find lots and lots of interesting things…

Until next time!

PHP 5.4 released

After nearly three years since the release of PHP 5.3, PHP 5.4 is finally released. Although PHP 6.0 was meant to be the next PHP release, several issues, in particular issues with unicode, postponed the release. Instead, work on PHP 5.4 begun, and in this week, we have seen the culmination of that effort.

Some features available in PHP 5.4

  • Shortened array syntax, a’la JavaScript
  • Traits
  • Built-in web-server
  • Session uploads
  • Addition of binary number format
  • Function array dereferencing
  • Some reports exist in the wild of users getting over 25% speed increases
  • A new version of the Zend framwork
  • Default character set is now UTF-8

Built in web-server

Honestly, I am quite happy with Apache, but I still have to evaluate whether I will use the built in web-server. Although it is VERY early days, I am currently not finding any innovators or early adopters of this in commercial hosting setting, hence I am a bit skeptical to jump on the bandwagon regarding this feature, although I may explore this feature shortly. If so, I will post an article about it.

Shortened array syntax

For me, this means nothing, really. I wonder if this holds a performance penalty for using this syntax. Stay tuned for a little benchmark test on this in the near future.

Some pitfalls

As far as my initial investigation of PHP 5.4 indicates, most current PHP code should still work, but there are some possible pitfalls, such as:

  • New keywords that were added that may cause undesirable effects if used on older code
  • No more safemode, register_globals and magic quotes
  • PHP 5.4 is also the last PHP version that will support Windows XP and Windows  server 2003

Default character set UTF-8

I am particularly excited about this. Having a home language that often has problems with the ISO8859 character set (although mostly due to missing or wrong configuration implementations as well as different character set choices over various platforms and tools), this will help me a lot by knowing at least one of my components used for development will be correctly set and won’t need any additional checking.

Sources

Traits in PHP 5.4

Due to the fact that PHP has up to now used only a single inheritance model, reusing code  by reducing duplication of code was sometimes a bit of a problem.

To start explaining what traits in terms of PHP are, a good start is to look at how the PHP documentation defines it:

“Traits is a mechanism for code reuse in single inheritance languages such as PHP. A Trait is intended to reduce some limitations of single inheritance by enabling a developer to reuse sets of methods freely in several independent classes living in different class hierarchies.”

Buzz words

We have several of buzz words here. They are:

  • Code reuse
  • Single Inheritance
  • Class hierarchies

To fully understand what the definition of a trait means, it is important that you understand what these individual buzz words mean.

Code reuse

Code Reuse is the practice of reusing old code (or software) to create new code (or software). For example, as an overly simplistic example, you could write a single routine, let’s say, HelloWorld, that may do something as simple as write “Hello World” on the screen. You can then use it in other places within your case by simply calling the function HelloWorld which will then automatically put the phrase “Hello World” on the screen where you call this function or routine. An added benefit of code reuse is that should you need to change the message displayed to “Hello Jupiter” it is as simple as changing it in one place in your code. Of course, code reusing has a more prominent place in object oriented programming, but I suspect you are catching my drift.

Single Inheritance

In its most simplest definition, the term Single Inheritance refers to the fact that a class written in a language that utilizes it, can only inherit from one super class.

Class Hierarchies

Class Hierarchies refers to the classification of classes in relation to one another. There are various classes of hierarchies, which falls outside the scope of this article, but in short, some classes can be used as blueprints for others, or an instance of another.

Trait definition revisited

Now that we can distinguish the buzz words in the PHP documentation definition of a trait, the definition makes more sense. Loosely translated, based on the statements above, this can now be simplified as follows:

“A Trait is intended to reduce some limitations of single inheritance by enabling a developer to reuse functions or routines of code previously developed freely in several independent classes living in different classes that are not related to one another.”

Conclusion

This post is not intended as a “how-to” for using traits. If you’re interested in such a post, you should take a look at one of the following articles, which describes the process clearly: http://phpmaster.com/using-traits-in-php-5-4/ or http://simas.posterous.com/new-to-php-54-traits. Both of these articles explains HOW to use traits. The intention of this article is to explain what it is, define the concept and have a discussion after this. Feel free to post your comments by replying to this post.