Page tree
Skip to end of metadata
Go to start of metadata

This document describes the process to create Sirsi bib records for the slides transferred from the Design Library to bookbot. A&D receives a cube of slides from Design in every 2-3 weeks, along with a spreadsheet containing needed information via email ( 

Each cube contains five drawers, each drawer contains five rows, and each row has a certain number of slides. In Sirsi, one bib record is created for each drawer, each bib record includes five call numbers corresponding to each row, and each call number has a certain number of items corresponding to each slide.

Before proceeding with the steps below, save the spreadsheet received to Monographs google team drive:

Then download the saved google spreadsheet to your local computer as a Microsoft Excel file, because the VBA code only works with Microsoft Excel file. All Excel process happens in the locally downloaded Excel file.

Alternate process


Before proceeding with the following instructions, there is now an alternate workflow that will save some time if it works (and won't waste much if it doesn't).

  • Export the Excel data as a tab-delimited text file. Save the file (with the extension ".txt") in its own folder in G:\Acquisitions & Discovery\BibLoads\Design Slides
  • Move the file "processcube.bat" into the folder with the text file. This file will attempt to act on any file in the folder with it that has a .txt extension.
  • Double Click the "processcube.bat" file to activate it. It should generate a file with the extension .mrk
  • Use MARCEdit to make two changes to the .mrk file:
    • Find/Replace with Regular Expressions: Find: "(\$)($)"  Replace: "{dollar}$2"   This takes care of dollar signs in barcodes that might create problems when creating a MARC file.

    • Under Tools→Sort By... →Sort Fields Within the Record By...→Sort all fields.  (Ctrl+F11 should do the same thing)

  • Compile file into MARC and skip to Step 4 in the following directions

Step 1: Split the spreadsheet into multiple sheets

  • Before splitting the spreadsheet, replace the dollar sign ($) with something else for data from the Barcode column. The dollar sign will cause trouble when processing the marc record in MARCEdit. So they need to be replaced if there is any. Use the find/replace function in Excel to do this. 

  • Now, apply the VBA code below to break the sheet into multiple sheets, so that data for each drawer is saved as an individual spreadsheet. 

  • Export each sheet as tab delimited txt file and save them to G-drive:

    • G:\Acquisitions & Discovery\BibLoads\Design Slides

    • Name the file as cube[number]drawer[number], for example, cube3drawer1

Step 2: Translate the tab delimited txt file into mrk file

In MARCEdit, translate the tab delimited txt file into mrk file with Delimited Text translator, .

Apply template “text2marcTemplate” to map:

  • field4 to 949 $a

  • field1 to 999$i

Make sure you select the checkbox "Ignore Header Row". 

Step 3: Open the translated mrk file and process the marc records as following

3.1 Copy the two fields data: LDR and 008 and paste it after step 3.2. This is because all LDR and 008 feilds will be removed with step 3.2, and we only need one set of LDR and 008 in the marc record. 

3.2 Assign task “designslidesmarc”, which does the following:

  • Swap 999 i and 949 a, check on “add to existing”

  • Remove all LDR and 008 field

  • Edit 949 $a to add “Row” in front of the row number.

3.3 Add the following fields. Change the cube number and the drawer number to the corresponding number.

  • =001  cube1drawer4

  • =245  00$aCube1Drawer4

3.4 Add 500 fields

  • Copy and paste the following data to the mrk file after 245 field

=500  \\$aRow1$3

=500  \\$aRow2$3

=500  \\$aRow3$3

=500  \\$aRow4$3

=500  \\$aRow5$3

  • Copy and paste the converted comma separated accession number to the corresponding 500 field

    • First, break the numbers into the individual sheet for accession numbers in each row. Use the VBA code below to do the split.

      • set vcol = 5

      • change the sheet name as the corresponding sheet name: Set ws = Sheets("cube1drawer1")

    • Transform accession numbers in the column into the comma-separated list in Excel. For a live demo, see this screen capture.

      • In an empty cell in the separate column(use F2 here), type in

      =A2&”, ”

               This will add a comma and space (Remember to add the space after the comma) after the accession number.

      • Drag the cell down to fill the rest of the column

      • Then, in another empty cell, type in


      Here F80 should be the number of rows to be concatenated. Then select TRANSPOSE(F2:F80), then press F9. This will populate the values in the cells in that cell. Once you do that, the cell will look like this:

      concatenate({"82228, ","82229, ","82230, ","82231, ","82232, ","82233, "})

      Then remove “{“ and “}”. And press ENTER.

      All values in F column will be in the one cell. 

      Then copy it to the 500 filed. Do this for all 500 fields.

3.5 Save the mrk file and compile the file into mrc file.

Step 4. Load marc files into Sirsi

Use the report template "DesignSlides" to load the marc files into Sirsi.

Step 5. Update the loaded marc records to trigger data ingest into the bookbot software.

Per Dawn, the items loaded into Sirsi with marc import will not be ingested into the bookbot software. We need to manually make some updates in Workflow after the records are loaded.

  • Change the call numbers’ library to Bookbot and save the records. This update will generate a log that can trigger data ingest into the bookbot software. All call numbers for each bib records need to be updated this way.

Step 5.5. Give it a minute. Once the records have been changed to the BOOKBOT location, a  few minutes needs to pass to allow for those records to be moved into the BOOKBOT inventory system.  They will not transfer properly if the record is shadowed (next step) before the scripts have run. Make sure all changes have been saved, and then step away from this for a few minutes before proceeding on to the next step.

Step 6. Then shadow the bib records to hide them from the public view

Step 7. Notify Carl ( and cc’ing Yan ( and Barbara ( that the bib records have been loaded into Sirsi. Make sure to reference the Cube number when email them.


VBA code for splitting the spreadsheet to multiple sheets

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

3. Then press F5 key to run the code. All data in the active worksheet are split into multiple worksheets by the column value (see “vcol” in the code). Set vcol = 4 when split the sheet by Drawer Number, and set it to 5 for the split by Row Number. And the split worksheets are named with the split cell names

==============VBA code starts here======================

Sub parse_data()

Dim lr As Long

Dim ws As Worksheet

Dim vcol, i As Integer

Dim icol As Long

Dim myarr As Variant

Dim title As String

Dim titlerow As Integer

vcol = 4

Set ws = Sheets("Sheet1")

lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row

title = "A1:E1"

titlerow = ws.Range(title).Cells(1).Row

icol = ws.Columns.Count

ws.Cells(1, icol) = "Unique"

For i = 2 To lr

On Error Resume Next

If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then

ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)

End If


myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))


For i = 2 To UBound(myarr)

ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""

If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then

Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = myarr(i) & ""


Sheets(myarr(i) & "").Move after:=Worksheets(Worksheets.Count)

End If

ws.Range("A" & titlerow & ":A" & lr).EntireRow.Copy Sheets(myarr(i) & "").Range("A1")

Sheets(myarr(i) & "").Columns.AutoFit


ws.AutoFilterMode = False


End Sub

  • No labels