Learning to create simple CSV files manually

by
on May 25, 2009
(1 minute read)

CSV is one of the best solutions when implementing any kind of data into an Excel-like spreadsheet. It’s hyper simplicity and its broad compatibility makes this format ideal for any kind of project.

I’ve got invites for a free bank transfer!

Let’s have a look at a simple CSV file:

a1,b1,c1,d1
a2,b2,c2,d2
a3,b3,,d3

This is a 4 column x 3 row table with letters and numbers. For your reference, we have used column and row reference into every cell. Since we haven’t included any text in c3 column, it shows comma comma without anything in it.

So:

  • to change to the next cell, add a comma.
  • to change to the next row, add a line break.

But what if we want to insert a comma inside a cell? Pretty simple, just quote it:
We have replaced c1 with c,1 and this is how it looks:

a1,b1,"c,1",d1
a2,b2,c2,d2
a3,b3,,d3

But what if we want to insert a quote inside a cell? Simple too, quote it:
We have replaced c,1 with "c1" and this is how it looks:

a1,b1,"""c1""",d1
a2,b2,c2,d2
a3,b3,,d3

Things I’m buying on Amazon this week

That’s it! Now you can convert any kind of data into a spreadsheet using simple RegEx to get lighter AJAX calls than XML or whatever use you want…

CSV encoding function in PHP

And here you are, a PHP function that converts text into valid CSV cells by escaping when necessary (apply to each cell):

function encodecsv ($string) {
    if(strpos($string, ',') !== false || strpos($string, '"') !== false || strpos($string, "\n") !== false) {
        $string = '"' . str_replace('"', '""', $string) . '"';
    }
    return $string;
}

Looking for cool gift ideas? TrendLiker.com lists hundreds of nice items and lets you know when they go on sale on Amazon.

Did you enjoy this article?

Grab me a coffee ☕

3 comments

  • Jordi says:

    Hi!!

    Im trying it as is nice, but there is one thing that i can not do: write a string made of numbers, with zeros at the left side. Excel thinks it’s a number and I loose the zeros :(

    Any one can comment about this? It is useful to me, as I can export database product codes made this way into excel without too much hassle.

    Signed: sausage eater :D

  • LuckyShot Author says:

    Jordi! :D

    To avoid loosing the leading zeros use the Data Import Wizard in Excel. At the last step you can set the format of the columns to text/string.

    Que tal tot? Com va per TremSpain? Jo dexamens i a punt de graduarme. Quan acabi em passo pel server!

    Un saludote i records a tots!

  • Jordi says:

    Thanks!, i’ll take a look at it!

    M’alegre de veure’t acabant.

    Si et passes molta gent s’alegrarà.

    Ens vem !

    (=

Follow me for cool new products and interesting findings on graphic design, web development, marketing, startups, life and humor.


/*Twitter*/ !function(d,s,id){var js,fjs=d.getElementsByTagName(s)[0];if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src="//platform.twitter.com/widgets.js";fjs.parentNode.insertBefore(js,fjs);}}(document,"script","twitter-wjs"); /*Facebook (function(d, s, id) {var js, fjs = d.getElementsByTagName(s)[0];if (d.getElementById(id)) {return;}js = d.createElement(s); js.id = id;js.src = "//connect.facebook.net/en_GB/all.js#xfbml=1&appId=28624667607";fjs.parentNode.insertBefore(js, fjs);}(document, 'script', 'facebook-jssdk'));*/ /*Google+*/ window.___gcfg = {lang: 'en-GB'};(function() {var po = document.createElement('script'); po.type = 'text/javascript'; po.async = true;po.src = 'https://apis.google.com/js/plusone.js';var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s);})();
Airbnb 25€ discount