Posts Tagged ‘csv’

Solving excel troubles by thinking out of the box

Saturday, July 2nd, 2011

A friend of mine just pinged me, wanting to know if I knew a way to programmatically add a range of number sequences to an excel worksheet.

I know nothing about excel scripting (or VBA-stuff or that type of things). But that didn’t stop me from getting an idea.

So I questioned him relentlessly until I was sure I knew what the expected output should be.

What he wanted was 16560 rows  in a worksheet. Rows 1 through 60 should contain the number 1. Rows 61 to 120 should contain the number 2, etc…

The final 60 rows should contain the number 276.

Why would he want this? I don’t know, some school work I presume. I didn’t particularly care, I assume his teacher wanted that format and was to lazy to provide a template…

I did however care that I found the problem interesting. How could I help, without any knowledge in VBA-scripting, and no desire to learn it within the next two hours?

The solution was rather neat:

$ touch file.csv
$ for i in `seq 1 276`;
do 
    for a in `seq 1 60`;
    do
        echo "$i" >> file.csv
    done
done
$ unix2dos file.csv

After having created the file, ensured it would work on his windows system, and transferred it to him (and he verified that it worked to import it and copy the results over to where he needed to work with it) I timed the execution of the loop.

2.33 seconds, against several hours if doing it manually, or $DEITY knows how long if it involved learning how to create a VBA script to do it for you…

He was pleased, I had fun, all in all 25 well spent minutes. Win-win.

And it was definitely easier, for me, to create a csv-file for him to import, than to learn me sum VBA.

:wq

Putting technologies to use in peculiar ways

Wednesday, March 4th, 2009

I just read a daily WTF and I can’t be sure why, possibly because they were generating invoices, an activity which my mind for some reason has been linked to PDFs, I had a flashback to term 5 at ITU, where our project group collected a bunch of data through a web-based questionnaire, and stored in a database.

Then there was the question about retrieving the information and presenting it in our document (a PDF, generated by LaTeX), which, if I remember correct, was done by me by ugly-hacking together a PHP-script which, depending on what script you called from the webserver, either presented you with a csv file, or a LaTeX formatted file. To be completely honest I guess stream would be the better description, which the browser interpreted as a file and rendered.

In any case, I have a little suspicion that this wasn’t one of the intended domains for PHP, but it did the job well nonetheless.