Import data from a multi row csv file into a CCK multigroup in Drupal?

In Maverick (our Procurement to Pay solution) it is possible to support the process of invoices, but also the process of expenses and for example flight tickets. For a client of us, each flight ticket needs to be approved by a manager. After approval, the flight ticket needs to be matched and linked to the original invoice (from the credit card company).


This post describes our initial setup using existing Drupal modules. In our Maverick solution we have implemented this as part of a custom module that fully automates the process for our clients.


 

Every month, the credit card company sends an CSV (or Excel) overview with all tickets of that month (see example below).

Statement Date Invoice number Invoice date Traveller surname General Ledger Amount
30-04-2012 002100924 02-04-2012 COOL 230 3084
30-04-2012 002100925 02-04-2012 MEMBERS 490 3084
30-04-2012 002101131 02-04-2012 STREAM 210 4172
30-04-2012 002102502 03-04-2012 TEAM 230 627

We need to import this data in the invoice node from the Credit Card company. We use CCK fields in a Content Multigroup to show this data in the invoice node.

DetailTickets.png

Getting the data right

To get this job done I use the Feeds and Feeds Tamper module in combination with a VBA script.

First of all, to import the data of the CSV file into the correct node we need to specify to which node the data should be imported. So we add a column to the CSV file with the corresponding NID.

NID Statement Date Invoice number Invoice date Traveller surname General Ledger Amount
4982 30-04-2012 002100924 02-04-2012 COOL 230 3084
4982 30-04-2012 002100925 02-04-2012 MEMBERS 490 3084
4982 30-04-2012 002101131 02-04-2012 STREAM 210 4172
4982 30-04-2012 002102502 03-04-2012 TEAM 230 627

With the Feeds module, it is not possible to import a CSV file like this. Drupal will only save the last row in the node (it overwrites the rows before). So we need to change the data in something like this:

NID;Statement Date;Invoice number;Invoice date;Traveller surname;General Ledger;Amount 4982;30/04/2012,30/04/2012,30/04/2012,30/04/2012;002100924,002100925,002101131,002102502;02/04/2012,02/04/2012,02/04/2012,03/04/2012;COOL,MEMBERS,STREAM,TEAM;230,490,210,230;3084.97,3084.97,4172.22,627.02


We need to get one row where the columns are separated by a ; (or , whatever you want) and the multi values for each field separated by a , (or any other character if you use a , as a separator).

To do this, I have written a small VBA script which takes the rows and combine them into one row.
 


This is the first setup of the VBA code, please feel free to post any improvements in the comments of this post!


 

Sub MergeRows()
    Dim rng As Range
    Dim vSrc As Variant
    Dim vDst() As Variant
    Dim i As Long, j As Long

    ' Assumes data starts at cell A2 and extends down with no empty cells
    Set rng = Range([A2], [A2].End(xlDown))
    LastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

    ' Count unique values in column A
    j = Application.Evaluate("SUM(IF(FREQUENCY(" _
        & rng.Address & "," & rng.Address & ")>0,1))")
    ReDim vDst(1 To j, 1 To LastCol)
    j = 1
    
    ' Get original data into an array
    vSrc = rng.Resize(, LastCol)

    ' Create new array, one row for each unique value in column A
    vDst(1, 1) = vSrc(1, 1)
    
    'vDst(1, 2) = "'" & vSrc(1, 2)
    For d = 2 To LastCol
      vDst(1, d) = vSrc(1, d)
      Next d
    
    
    For i = 2 To UBound(vSrc, 1)
      For c = 2 To LastCol
      vDst(j, c) = vDst(j, c) & "," & vSrc(i, c)
      Next c
    Next i

    ' Remove old data
    rng.EntireRow.Delete

    ' Put new data in sheet
    Set rng = [A2].Resize(j, c - 1)
    rng = vDst

End Sub

So now we have the data file ready. Now we go on to the Drupal Part.

Drupal part

Install the Feeds and Feeds Tamper module. Go to the Feeds module and make a new CSV Feeds importer.

Configure the mapping as shown below:
MappingNodeProcessor.png

Click on the 'Configure Feeds Tamper' link and add the 'Explode' plugin to   every mapping except the 'NID -> Node ID'.

TamperExplode.png

Now everything is setup to import the CSV file. Go to 'index.php?q=import' and select the feed importer you just created. Import the CSV file and the data will be imported into the CCK multigroup.

DetailsTicketResult.png