MARC Records:

There a number of really good MARCedit tutorials on YouTube.  To move from spreadsheet to MARC, use the MARCedit delimited text translator. Relevant tutorials I've watched are:,,,



I developed a spreadsheet with a couple of concatenate formulas in MSExcel to generate folder lists.  I'll send it as an attachment directly to you (I think the list will reject attachments).


The formula without the spreadsheet won't be very helpful, but I'll paste it below to give you an idea of what one looks like.  This forumla contains a lot of "if" statements so that it can ignore empty cells.  This is the formula from line 3 of the spreadsheet. MSExcel allows you to "drag" a formula down a column and will automatically adjust all formula references to the correct row number.


Here's the formula: =IF(A3="","",(CONCATENATE("<c level='item'><did><unittitle>",A3,", </unittitle>",(IF(B3="","","<unitdate normal='")),CONCATENATE(IF(B3="","",B3),IF(B3="","","/"),IF(C3="",B3,C3)),(IF(B3="","<unitdate>","'>")),(IF(D3="",(IF(C3="",B3,CONCATENATE(B3,"-",C3))),D3)),"</unitdate>",IF(E3="","",CONCATENATE("<physdesc><extent> (",E3,") </extent></physdesc>")),"<container> Box ",F3,IF(G3="","",CONCATENATE(", ",G3," ", H3," </container>")),"</did>", IF(I3="","",CONCATENATE("<bioghist><p>",I3,"</p></bioghist>")), IF(J3="","",CONCATENATE("<scopecontent><p>",J3,"</p></scopecontent>")),IF(K3="", "",CONCATENATE("<accessrestrict><p>",K3,"</p></accessrestrict>")), IF(L3="","",CONCATENATE("<relatedmaterial><p>",L3,"</p></relatedmaterial>")), IF(M3="","",CONCATENATE("<acqinfo><p>",M3,"</p></acqinfo>")), IF(N3="","",CONCATENATE("<processinfo><p>",N3,"</p></processinfo>")), "</c>")))


Here's some data (this is line 3 of a spreadsheet)


Test folder title 2013 2013 approximately 2013 1 Folder 2 This is a biographical note. This is a scopecont note. Open for research use. This is a related materials note. This is acquisition information. This is a processing note.


Below is the result. 

    Caveat: our local EAD encoding has some practices that I'm not a big fan of, for example, you'll notice that the <container> encoding has "Box" and "Folder" inserted as content, rather than attributes.  You will probably want to change the formula. 

     Advice: 1) when editing the formula, use single quotation marks around attribute values, avoid using double quotation marks because MSExcel will interpret these as part of the formula. 2) I made this into one huge formula, but started by making each piece as an individual formula. When you experiment, don't try to do the whole thing at once, but instead just try a little piece, such as making the <container> tags and content.


<c level='item'><did><unittitle>Test folder title, </unittitle><unitdate normal='2013/2013'>approximately 2013</unitdate><container> Box 1, Folder 2 </container></did><bioghist><p>This is a biographical note.</p></bioghist><scopecontent><p>This is a scopecont note.</p></scopecontent><accessrestrict><p>Open for research use.</p></accessrestrict><relatedmaterial><p>This is a related materials note.</p></relatedmaterial><acqinfo><p>This is acquisition information.</p></acqinfo><processinfo><p>This is a processing note.</p></processinfo></c>



From: Encoded Archival Description List [[log in to unmask]] on behalf of Marsha Maguire [[log in to unmask]]
Sent: Friday, November 29, 2013 7:15 PM
To: [log in to unmask]
Subject: [EAD] Generating EAD and/or MARC from spreadsheets -- how?

Greetings, all. If I may, I wanted to revisit a thread from a couple of weeks ago (I was out of town when it first came up).

Kate Bowers said, "Starting with a spreadsheet, you can generate both MARC records and EAD components from the same source data."

But how, exactly (I ask as a non-programmer), is this done? Spreadsheet structure being flat and seemingly one-to-one (unless a lot of data is repeated in succeeding rows), I'm wondering how to indicate in the spreadsheet that a given title (say, of a book or a sound recording) described in a spreadsheet row has more than one creator or subject. In other words, how should a spreadsheet be set up to handle one-to-many relationships, so that its data can be exported and converted to EAD XML or MARC XML? May I ask anyone who is kind enough to consider answering this to please be specific or provide specific examples?

Apologies if this is obvious to many of you. Thanks so much.

Marsha Maguire
[log in to unmask]