Friday, September 12, 2014

Importing a csv file into a multi-sheet workbook with automation

There hasn't been a blog about programming for some time. This isn't because I haven't been programming but rather because I haven't come across any new techniques which are worth describing here. Today, of course, is different.

I was tasked with exporting data from the Occupational Psychologist's management program to Excel; I have done this many times previously. But the twist was that two sets of data were to be exported, preferably to separate sheets within one Excel workbook. My first solution was to use 100% automation - create the workbook, add a sheet, add the data, add another sheet, add the data, then finalise. Technically, this is fine but it's slow.

I then looked at my favourite solution for speeding up Excel automation: outputting the data into a csv file then importing this file into Excel. Whilst my standard code handles this without difficulty, I couldn't see how I could import data into the second sheet. The solution came after an interim version in which I imported both sets of data into the same sheet.

All I had to do was create a new sheet, cut the required data from one sheet then paste it into the new sheet. Voila - this way I could have my cake and eat it. I performed the task manually once whilst recording it into a macro so that later I could duplicate the macro code with automation.

The following code starts after the data has been stored in a tstringlist called 'csv'. During the export, I noted how many lines were needed for the first data set ('start') and how many for the second set ('stop').
 s:= mydirname + inttostr (gettickcount) + '.csv';
 with csv do
  begin
   savetofile (s);
   free
  end;

  xlApp:= CreateOleObject ('Excel.Application');
  xlApp.visible:= false;
  xlApp.workbooks.open (s);  // this imports the csv file into the spreadsheet

  xlApp.Range['A' + inttostr (start) + ':F' + inttostr (stop)].Select;  // this builds a string like A12:F46
  xlApp.Selection.Cut;                   // cut the data
  sheet:= XLApp.Sheets.Add;              // add a new sheet to the workbook  
  sheet.select;                          // select the new sheet
  xlApp.activesheet.paste;               // paste the data
 
  xlApp.sheets[2].select;                // go back to the first sheet 
  xlApp.activesheet.Range['A2'].Select;  // remove the selected area after the cut

  xlApp.visible:= true;
  sheet:= unassigned;
  XLApp:= unassigned;
All I had to do was lose the preconception that data from one query had to be imported into one sheet and data from the second into the second sheet. Once I had the interim solution of both data sets in the same sheet, the solution became clear.

Thursday, September 11, 2014

Last of the Luddites

My lack of blogging over the past few weeks has not been due to the problems which I had with my home computer, but rather a combination of deadly headaches, loss of motivation, over-heating and nothing much to write about.

On 19 May, 2011 - just over three years ago - I wrote about receiving a new mobile phone, a Nokia 3710 fold. This was hardly top of the line at the time, but now it's a fossil. There had been rumours that my company would be replacing the old, dumb, mobile phones but I didn't pay much attention. On Sunday, out of the blue, I was asked which model I wanted (i-Phone 5 was the immediate and joking reply), out of a choice of two: Samsung Galaxy Trend Plus or Nokia 802. After some inter-family consultation, I plumped for the Samsung.

Today I received it and this evening I will set it up (or rather, my daughter, who worked for a year for one of Israel's mobile carriers, will do so). Then I will try and learn how to use this new object. I've been looking at the user manual for the past few days and it actually doesn't seem to be too daunting. Of course, I'm not particularly interested in the advanced, 'smart', abilities of the phone; all I want, at least at the beginning, is to make and receive phone calls along with managing my contacts.

This review has been very helpful to me. I don't see any irony in the fact that I receive my first smartphone almost the same day as the i-Phone 6 is announced. I am a Luddite in terms of mobile telephony and have no need for all the whizz-bang functionality (nor the cost) of a top-line phone. One advantage of this model - as opposed to the Nokia - is that "The Samsung Galaxy Trend Plus does not take micro SIM cards as many smartphones do today. Instead, it works with the old-school standard-sized SIM cards, aka mini SIM cards." So I can take the SIM out of my old phone, place it in the new, and immediately start calling all my contacts, without the need of any data transfers.

"On the front of the device is located a VGA camera for selfies and video calls. Image quality is poor, but having a front-facing camera is better than not having one at all" - this, of course, is very important to me (not!), likewise "Technically, the Samsung Galaxy Trend Plus is capable of recording 720p video, but it isn't very good at it. Footage is grainy and low on detail, as if someone has stretched a 480p video to fit into a 720p frame. Indoor and low-light videos are even worse – barely usable, we'd say". It's like stereo separates: if I want to film video, then I'll use a video camera.

Apparently my mobile plan includes 50Mb data so I can be connected to Whatsup all day long. I doubt very much that I'll ever use the phone in order to surf the Internet as I have a computer at home, another computer at work and a third, mobile, computer, all of which are connected to the Internet. 

[MPP: 514; 0, 1, 6]