[Archivesspace_Users_Group] [External] Auto formatting with dates on Accession CSV import template

Fri May 5 10:12:43 EDT 2023

Hi,

Another trick for getting rid of UTF-8 errors is to use the CLEAN function
in Excel
<https://support.microsoft.com/en-us/office/clean-function-26f3d7c5-475f-4a9c-90e5-4b8ba987ba41>.
It takes a little extra work but is worth it when you can't find the one
character causing the problems.

Best,

Margaret

------------------------------

Margaret Turman Kidd

Access and Electronic Records Archivist, Special Collections & Archives

VCU Libraries | Health Sciences Library

Virginia Commonwealth University

509 N. 12th Street / Box 980582, Richmond, VA 23298-0582

(804) 828-3152

kiddm at vcu.edu

Pronouns: she/her

On Thu, May 4, 2023 at 4:40 PM Brian Harrington <
brian.harrington at lyrasis.org> wrote:

> Hi Sarah,
>
>
>
> I think that what you’re seeing is Excel reformatting the date when you
> reopen the CSV file.  CSV doesn’t store dates the same way that Excel
> does.  So if you set the format so that date shows as 2023-05-04 it will be
> saved that way in the CSV.  (You can open it in a text editor and check.)
> But the next time you open the CSV in Excel, Excel will cheerfully convert
> any dates that it finds (and sometimes other things) into Excel formatted
> dates.  So the key is to not reopen the file after you save as CSV.
>
>
>
> The UTF-8 errors you’re seeing are something completely different.  There
> are two ways to get around them.  The first is to save from Excel with the
> file type set to CSV UTF-8 rather than regular CSV.  The second is to look
> non-UTF-8 characters in your CSV and change them.  These are usually curly
> quotes and em-dashes.
>
>
>
> I hope this helps.
>
>
>
> Brian
>
>
>
> --
>
> Brian Harrington (he/him)
>
> Data Migration Specialist
>
> LYRASIS
>
> brian.harrington at lyrasis.org
>
>
>
>
>
> *From: *archivesspace_users_group-bounces at lyralists.lyrasis.org <
> archivesspace_users_group-bounces at lyralists.lyrasis.org> on behalf of
> Sarah Shepherd <SShepherd at srmml.org>
> *Date: *Thursday, May 4, 2023 at 3:19 PM
> *To: *Archivesspace Users Group <
> archivesspace_users_group at lyralists.lyrasis.org>
> *Subject: *Re: [Archivesspace_Users_Group] [External] Auto formatting
> with dates on Accession CSV import template
>
> Hi Bri,
>
>
>
> Thanks for the advice! I wish I had seen this video earlier.
> Unfortunately, I am passed that stage and understand the issues with the
> formatting and dates, the csv file is just not letting me reformat the
> dates. It keeps reverting automatically to the date format when I switch it
> to the text or general format. It’s a never-ending circle! I’ll keep
> fiddling with it.
>
> Best,
>
> Sarah
>
>
>
> Sarah H. Shepherd
>
> (she/her/hers)
> Archivist
> Scottish Rite Masonic Museum and Library
>
> 33 Marrett Road | Lexington, MA 02421
>
> Phone: 781-457-4129 | Email: sshepherd at srmml.org
>
>
>
>
>
> *From:* archivesspace_users_group-bounces at lyralists.lyrasis.org <
> archivesspace_users_group-bounces at lyralists.lyrasis.org> *On Behalf Of *McLaughlin,
> Brianna Jean
> *Sent:* Tuesday, May 2, 2023 11:18 AM
> *To:* Archivesspace Users Group <
> archivesspace_users_group at lyralists.lyrasis.org>
> *Subject:* Re: [Archivesspace_Users_Group] [External] Auto formatting
> with dates on Accession CSV import template
>
>
>
> * CAUTION:* This email originated outside of the organization. Do not
> click links or open attachments unless you recognize the sender and know
> the content is safe.
>
> Hi Sarah,
>
>
>
> I think you may be encountering similar issues that I ran into with the
> accession csv template. I don’t have the process I used written out in a
> way that’s decipherable by anyone but me, but my colleague, Jeremy, and I
> talked about the errors I ran into with dates during this year’s
> ArchivesSpace Member Forum. The Youtube video is here:
> https://www.youtube.com/watch?v=XRAUu58-Lgs&t=980s. The information
> between 11:46-18:23 is probably most relevant to you.
>
>
>
> It feels weird to direct you to my own presentation, but I hope it’s
> helpful! Feel free to follow up with me if you have questions.
>
>
>
> Bri McLaughlin, she/her/hers
>
> Digital Collections Librarian
>
> Indiana University
>
> 812-856-3321
>
>
>
> *From:* archivesspace_users_group-bounces at lyralists.lyrasis.org <
> archivesspace_users_group-bounces at lyralists.lyrasis.org> *On Behalf Of *Sarah
> Shepherd
> *Sent:* Tuesday, May 2, 2023 9:10 AM
> *To:* archivesspace_users_group at lyralists.lyrasis.org
> *Subject:* [External] [Archivesspace_Users_Group] Auto formatting with
> dates on Accession CSV import template
>
>
>
> You don't often get email from sshepherd at srmml.org. Learn why this is
> important <https://aka.ms/LearnAboutSenderIdentification>
>
> This message was sent from a non-IU address. Please exercise caution when
> clicking links or opening attachments from external sources.
>
>
>
> Hello,
>
>
>
> I’m having some difficulty with the Accession CSV template autoformatting
> my dates into the incorrect format for ArchivesSpace. It is only a problem
> with a single date. For example, I will put in 1920-04-29 which is the
> proper ArchivesSpace format for date_1_begin. The Accession CSV template
> then autocorrects it to be 4/29/1920 turning the cell format into a Date
> format instead of the standard General format. When I switch the cell
> format back to General it turns the number into 7425. When I put the
> correct date (1920-04-29) back in, it again auto formats the cell to a Date
> format, so it goes back to 4/29/1920. Bizarrely, it does not do the same
> for another record which has 1865-11-27 as date_1_begin.
>
>
>
> I tried to look up how to stop this auto formatting but have not found
> anything. I even tried setting the date format to English (South Africa)
> which has a date format of 1920-04-29, but when I save the document, it got
> rid of all the formatting and reverts to 4/29/1920. I have tried importing
> the template without saving so that it keeps the 1920-04-29 date format
> (set to the English (South Africa)) and it gives an error
> (#<ArgumentError: invalid byte sequence in UTF-8&gt) which I saw on a
> different post meant that there is incorrect formatting on the template. It
> goes without saying that when I tried to import the template with the date
> as 4/29/1920, it also did not work (though interestingly, returning the
> same error (#<ArgumentError: invalid byte sequence in UTF-8&gt) instead
> of an incorrect date error).
>
>
>
> In another weird twist, the template does not get the (#<ArgumentError:
> invalid byte sequence in UTF-8&gt) when accession_accession_date
> autoformats its cell to date and ArchivesSpace can also deal with the
> import just fine with the incorrect date format of 3/29/2023 turning it
> into 2023/03/29 in the record in ArchivesSpace. Why the difference?
>
>
>
> That was a lot of information, but as you can see, I’ve done some
> troubleshooting to try to figure out the issue and have only become more
> confused!
>
>
>
> Has anyone else run into this issue with single dates? Are there any work
> arounds besides giving up on single dates when importing?
>
>
>
> Grateful for any help,
>
> Sarah
>
>
>
> Sarah H. Shepherd
>
> (she/her/hers)
> Archivist
> Scottish Rite Masonic Museum and Library
>
> 33 Marrett Road | Lexington, MA 02421
>
> Phone: 781-457-4129 | Email: sshepherd at srmml.org
>
>
>
>
> _______________________________________________
> Archivesspace_Users_Group mailing list
> Archivesspace_Users_Group at lyralists.lyrasis.org
> http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20230505/7c825645/attachment.html>