Can I merge several CSV files with Applescript & Excel?

Hi all,

I have an interesting (well I think interesting) little problem. I have a bunch of files (~30) from an experiment saved in a pseudo-CSV format containing two columns of data and a bit of junk at the start and end. What I need to do is compile it all into a single Excel worksheet with the columns side by side for graphing & analysis. Here’s a snippet of a file:

Thu Oct 16 16:00:08 2003
Wavelength (nm)
284.577,	-0.00281326
285.394,	-0.00337415
286.212,	-0.00186142
287.029,	-0.00303439

Yes, the hard-returns at the start are in the files too. Everything up to ‘510’ is unnecessary. It ends with a bit of junk too, but I can probably take that out by hand. Actually the stuff at the start I can take out too, the hard part is getting it all side by side.

So, anyone have any ideas? In another thread Rob managed to get them all into a single text file quite easily, but they were one after the other, does that help at all?

Many thanks in advance,


There are sevral possible ways to do this. Don’t get me wrong I love applescript :stuck_out_tongue: , but I’ve been doing some VBA scripting (Excel macro) lately and I’ve already done what you need. This might be an easier solution for you particular problem. Insert this into a macro in excel.

This will allow you to select several excel files at once and put your data all into one spreadsheet.

You will need to edit the code a little so that it selects your data correctly.

Sub test_Macro()

'       Load data files

Dim varFilenames As Variant
Dim strActiveBook As String
Dim strSourceDataFile As String
Dim strTest As String
Dim wSht As Worksheet
Dim allwShts As Sheets

' Get the files, may select more than one file
varFilenames = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select the data files.  Hold the Ctrl- key to select more than one file.", , True)

            'Create workbook
            ActiveSheet.Name = "Data"
            strActiveBook = ActiveWorkbook.Name
            On Error GoTo 0

            'Loop through the workbooks to open
            counter = 1
          ' ubound determines how many items in the array
          Application.ScreenUpdating = False
          While counter <= UBound(varFilenames)
             'Opens the selected files
            Application.StatusBar = "Opening " & varFilenames(counter)
            Workbooks.Open varFilenames(counter), , ReadOnly
            strSourceDataFile = ActiveWorkbook.Name
             'set range in Excel format -"A1"
             'This calculates the destination
             myRange = GetExcelColumn((counter - 1) * 2 + 1) & "1"
             Workbooks(strSourceDataFile).Sheets(1).Range("A1:B30").Copy _
            ' Close the data workbooks
            Application.DisplayAlerts = False
            Application.DisplayAlerts = True
             'increment counter
            counter = counter + 1
        Application.StatusBar = False

Application.ScreenUpdating = True

End Sub

Private Function GetExcelColumn(ByVal iCellNo As Integer) As String
    Dim iBeg, iEnd As Integer

    ' If 1-26, then this is an easy conversion
    If iCellNo < 27 Then
        GetExcelColumn = Chr$(iCellNo + 64)
    ' Now we have to account for AA-ZZ
        iBeg = iCellNo  26     ' Get the first letter
        iEnd = iCellNo Mod 26   ' Get the second letter
        If iEnd = 0 Then
           ' If iEnd is 0, then it is Z, which should be 26
            iEnd = 26
        '** you need to subtract 1 from the initial letter otherwise your lettering will be the next letter in the alphabet

        iBeg = iBeg - 1
        End If
        GetExcelColumn = Chr$(iBeg + 64) & Chr$(iEnd + 64)
    End If
End Function

Thanks scott997. I’ve tried making this into a macro but to be honest, I haven’t got the faintest idea how Excel macros actually work. I saved it in my ‘Personal Macros Workbook’ but when I try to run it (with the relevant files open) I just get a compile error : Syntax error. When the debugger loads it highlights the very first line! :frowning:

Not doing very well am I. It’s probably something simple, sorry to bother you with it. I’ll keep trying…


Update: I managed to fix that by taking out all the tabs and so forth, it seems to like it better that way. Now when I run it I get the error:

Run-time error '1004':

Method 'GetOpenFilename' of object '_Application' failed

Any ideas? Like I said I have no idea how all this stuff works, sorry to bother you with most probably trivial stuff.

It is trivial. Microsoft sucks.:evil: I’ve been running the macro at work on a Windows machine. :oops: I figured it should work without any problems on Excel regardless of the platform.:evil: I was wrong. There was a syntax error that I needed to fix. But it still will not do what you need because the GetOpenFilename has a Microsoft bug that will not allow you to select more than one file. This cascades into a larger problem because the filenames are no longer in an array…

If you can bring yourself to use Windows to do it, this should work. Otherwise it should be easy to use applescript to tell excel to open each file one at a time, copy the data and paste it into your desired spreadsheet . I know this is not as helpful as providing code. Sorry. If you are still having problems, post here and I’ll see what I can do. I’m off for vacation for the next couple of days though.



I was thinking about this and I was wondering what output you were trying to get. Is the desired result something like this:

284.577, -0.00281326
285.394, -0.00337415
286.212, -0.00186142
287.029, -0.00303439
284.577, -0.00281326
285.394, -0.00337415
286.212, -0.00186142
287.029, -0.00303439
284.577, -0.00281326
285.394, -0.00337415
286.212, -0.00186142
287.029, -0.00303439

With all 30 pairs of data in two columns. Or are you looking for this:

284.577, -0.00281326, 284.577, -0.00281326 ……
285.394, -0.00337415, 284.577, -0.00281326 ……
286.212, -0.00186142, 284.577, -0.00281326 ……
287.029, -0.00303439, 284.577, -0.00281326 ……

With 60 columns of data. Are the original files in text format?


Thanks again Scott, MS does indeed suck. As you suggested ended up writing an Applescript to copy and paste it all. Andy; I’m looking for a modified version of the second situation. Modified since I don’t actually need the first column from all the files; it remains constant.

Anyway, what I did was to use Rob’s text compiling script from another thread to get all the data in one file then open it in Excel. Then I use a second script to select the relevant data and paste it all side by side. It took me a while to write out all the values for 35+ data sets, but now that it’s done I don’t need to do it again.

Thanks again for your help.