Adds T00:00:00+00:00 after date in YYYY-MM-DD format

[harvard-library]

Adds T00:00:00+00:00 after date in YYYY-MM-DD format

[bobbi-SMR]

Hi,

I'll take a look at this ASAP, and get back to you; the code may not be triggering the correct conversion algorithm.

[bobbi-SMR]

@amberjewel : With the very small test case I created
test_dates.xlsx
I was unable to reproduce your issue. Which version of ArchiveSpace are you working with? Would you be able to attach an example of your spreadsheet?

Thanks,
Bobbi

[amberjewel]

We're working with v.1.5.4 at the moment. I'm attaching our test spreadsheet that we used. Thank you! Amber

…

On Tue, Sep 19, 2017 at 10:46 AM, Bobbi Fox at Harvard LTS < ***@***.***> wrote: @amberjewel <https://github.com/amberjewel> : With the very small test case I created test_dates.xlsx <https://github.com/harvard-library/aspace-import-excel/files/1315257/test_dates.xlsx> I was unable to reproduce your issue. Which version of ArchiveSpace are you working with? Would you be able to attach an example of your spreadsheet? Thanks, Bobbi — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#9 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ANcrUBOYNRP2ms4PywlgXAM5-8oNLlc3ks5sj_4TgaJpZM4PcvER> .

[bobbi-SMR]

Hi, the attachment didn't come through :-(

(I tested on v1.5.3, btw, but that shouldn't have mattered...)

[amberjewel]

I've attached it again. Hopefully it will go through this time. It looks like it's successfully attached to the e-mail. I can try it another way if need be.

…

On Tue, Sep 19, 2017 at 11:48 AM, Bobbi Fox at Harvard LTS < ***@***.***> wrote: Hi, the attachment didn't come through :-( (I tested on v1.5.3, btw, but that shouldn't have mattered...) — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#9 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ANcrUOduEilU6_numvz6H12Wf4hJK7dZks5skAx-gaJpZM4PcvER> .

[amberjewel]

aspace_import_excel_test2.xlsx

[amberjewel]

There we go. I'm adjusting to GitHub as a user - I've only ever lurked before.

[bobbi-SMR]

Got it! I may not be able to get back to you before tomorrow.

Cheers,

[amberjewel]

Thank you. We're trying to test this every which way before we go live with the plugin, and I appreciate any assistance.

[bobbi-SMR]

Testing every which way is a good idea 😄
Here's what I see so far:
In my tiny test, the date cell is formatted as "general".

In your spreadsheet, the date cell is formatted as "Custom" "Type: yyy-mm-dd" I'm not an excel guru (she's out today 🙁 ), so I don't know what the customizing does, but perhaps behind the scenes those yyy-mm-dd becomes a DateTime object, resulting in that time information being included when my code comes to process it, whereas perhaps when it's general, it comes through to my code as a date simple string?

I see that when I try to add a row in your spreadsheet, it converts 1984-04-06 to a Date, no matter what I do about it! This doesn't happen with my spreadsheet; I don't know enough about Excel to know how to turn it off!

Hopefully tomorrow I can grab a small amount of our Excel guru's time, because if we can figure out how to turn that off, it will go faster for you than waiting for me to make a change in the ingest code, test it, put it up on GitHub, etc.

Til tomorrow,
Bobbi

[amberjewel]

Hi Bobbi, Thanks! We've been having trouble with the date fields for the exact same reason. Our Excel guru hasn't been able to turn off the automatic date feature, so I hope yours has some suggestions. Thank you again for looking into this! Amber

…

On Tue, Sep 19, 2017 at 12:59 PM, Bobbi Fox at Harvard LTS < ***@***.***> wrote: Testing every which way is a good idea 😄 Here's what I see so far: In *my* tiny test, the date cell is formatted as "general". In *your* spreadsheet, the date cell is formatted as "Custom" "Type: *yyy-mm-dd*" I'm not an excel guru (she's out today 🙁 ), so I don't know what the customizing does, but perhaps behind the scenes those yyy-mm-dd becomes a DateTime object, resulting in that time information being included when my code comes to process it, whereas perhaps when it's general, it comes through to my code as a date simple string? I see that when *I* try to add a row in your spreadsheet, it converts 1984-04-06 to a Date, no matter what I do about it! This doesn't happen with my spreadsheet; I don't know enough about Excel to know how to turn it off! Hopefully tomorrow I can grab a small amount of our Excel guru's time, because if we can figure out how to turn that off, it will go faster for you than waiting for me to make a change in the ingest code, test it, put it up on GitHub, etc. Til tomorrow, Bobbi — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#9 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ANcrUNWucEqy36lDEpmzgSzBa74uEpxdks5skB0agaJpZM4PcvER> .

[bobbi-SMR]

Hi, Amber,

Just as I was getting ready to go home for the night, a different Excel guru cruised by.

She suggested that the automatic date stuff might be caused by you using a different version of Excel than the one we used to create the template. Some investigation showed that the begin and end date columns are defined in our template as having the format "General"; if the columns are set to "Text", the automatic date stuff doesn't happen!

It will take me a little while tomorrow to get the enhanced template up on github (there is a vetting process, like doing some testing, you know 😃 ). Be warned that if (at least in the version of Excel I'm using) you change the formatting of those columns with cells of the YYYY-MM-DD format already entered, you'll get some VERY strange results.

[amberjewel]

Hi Bobbi, Thanks! I was starting suspect a version issue might be at the heart of the situation. I was playing around with those dates and also got the very strange results when I tried to change them to general with the dates already inside. Excel has some crazy quirks. By the way, I just tested the plugin with an almost 12,000 row spreadsheet, and I have to say that this plugin is fantastic. I appreciate all of the work that went into it because it's making the work at our small shop infinitely easier for a huge finding aid on a tight deadline.

…

On Tue, Sep 19, 2017 at 1:25 PM, Bobbi Fox at Harvard LTS < ***@***.***> wrote: Hi, Amber, Just as I was getting ready to go home for the night, a different Excel guru cruised by. She suggested that the automatic date stuff might be caused by you using a different version of Excel than the one we used to create the template. Some investigation showed that the begin and end date columns are defined in our template as having the format "General"; if the columns are set to "Text", the automatic date stuff doesn't happen! It will take me a little while tomorrow to get the enhanced template up on github (there is a vetting process, like doing some testing, you know 😃 ). Be warned that if (at least in the version of Excel I'm using) you change the formatting of those columns with cells of the YYYY-MM-DD format already entered, you'll get some *VERY* strange results. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#9 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ANcrUCcRrlkGYcAlfVlEEeT9TUCjoMsgks5skCNNgaJpZM4PcvER> .

[bobbi-SMR]

Hi, Amber,

In one of those "D'OH!" moments, it occurred to me that I could upload the revised template here, so you could try it out. Here you go! You Get It First 😃
aspace_import_excel_template.xlsx

And thanks for your kind words; it's so rewarding to be working on something people actually use and need.

[amberjewel]

Thank you! I might have a chance to try it out this afternoon.

…

On Wed, Sep 20, 2017 at 11:26 AM, Bobbi Fox at Harvard LTS < ***@***.***> wrote: Hi, Amber, In one of those "D'OH!" moments, it occurred to me that I could upload the revised template here, so you could try it out. Here you go! You Get It First 😃 aspace_import_excel_template.xlsx <https://github.com/harvard-library/aspace-import-excel/files/1318791/aspace_import_excel_template.xlsx> *And thanks for your kind words; it's so rewarding to be working on something people actually use and need.* — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#9 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ANcrUG-5glFu_zVdFCuMN9lRkGrJoApyks5skVjlgaJpZM4PcvER> .

[amberjewel]

I wanted to let you know that we did get a chance to check out the new version of the spreadsheet, and our versions of Excel did not override the date columns this time around, so using Text instead of General was a definite success. I haven't had a chance to get a spreadsheet into our test instance of AS, though, because systems is testing the upgrade to 2.0 at the moment. Out of curiosity, do you have any idea if the plugin will work with 2.0 or above? On Wed, Sep 20, 2017 at 11:56 AM, Amber D'Ambrosio < [email protected]> wrote:

…

Thank you! I might have a chance to try it out this afternoon. On Wed, Sep 20, 2017 at 11:26 AM, Bobbi Fox at Harvard LTS < ***@***.***> wrote: > Hi, Amber, > > In one of those "D'OH!" moments, it occurred to me that I could upload > the revised template here, so you could try it out. Here you go! You Get It > First 😃 > aspace_import_excel_template.xlsx > <https://github.com/harvard-library/aspace-import-excel/files/1318791/aspace_import_excel_template.xlsx> > > *And thanks for your kind words; it's so rewarding to be working on > something people actually use and need.* > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub > <#9 (comment)>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/ANcrUG-5glFu_zVdFCuMN9lRkGrJoApyks5skVjlgaJpZM4PcvER> > . >

[bobbi-SMR]

Hi, Amber,

The plugin should work with 2.x (at least I coded it to do so, and tested it locally!).

Cheers,
Bobbi

[amberjewel]

Hi Bobbi, That's great to hear. Once again, you have my deepest appreciation for what you have done here with this plugin. Cheers! Amber

…

On Mon, Sep 25, 2017 at 8:17 AM, Bobbi Fox at Harvard LTS < ***@***.***> wrote: Hi, Amber, The plugin should work with 2.x (at least I coded it to do so, and tested it locally!). Cheers, Bobbi — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#9 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ANcrUOlbp6Dmbp6J5aYq_D7jjeb-yQCFks5sl8QWgaJpZM4PcvER> .

[amberjewel]

Hi Bobbi, I have a follow-up question about the plugin. We were able to load a spreadsheet of almost 12,000 rows once successfully but since then have kept bumping into what seems to be a time-out error of some sort as we test further. This doesn't happen with spreadsheets of 5000 rows or fewer. Are there any settings in any of the code or in AS that we might be able to manipulate to avoid that? We can break the spreadsheet into smaller chunks if need be, but I thought I'd check first since it did work once. When it worked our test instance was still on v1.5.4 but now is on v2.0. Systems is going to upgrade us to v2.1.2 soon to see if that has any impact. I appreciate any insight you might have. Amber On Mon, Sep 25, 2017 at 8:28 AM, Amber D'Ambrosio < [email protected]> wrote:

…

Hi Bobbi, That's great to hear. Once again, you have my deepest appreciation for what you have done here with this plugin. Cheers! Amber On Mon, Sep 25, 2017 at 8:17 AM, Bobbi Fox at Harvard LTS < ***@***.***> wrote: > Hi, Amber, > > The plugin should work with 2.x (at least I coded it to do so, and tested > it locally!). > > Cheers, > Bobbi > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub > <#9 (comment)>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/ANcrUOlbp6Dmbp6J5aYq_D7jjeb-yQCFks5sl8QWgaJpZM4PcvER> > . >

[pobocks]

Hi Amber,

I'm Dave, a co-worker of Bobbi's. Would you be able to provide us with the spreadsheet?

I think what's happening is that you're hitting the time-out just because something in 2.x got slower or similar, and just fell past the boundary. I'd like to verify this; it certainly might be something else, after all. If that is the problem, chunking it up is probably the right move; I don't think there's really a meaningful way to stream-process Excel files, afaik.

[amberjewel]

Hi Dave, I'm attaching the latest version of the spreadsheet. Thank you for looking into this.

…

On Wed, Sep 27, 2017 at 11:16 AM, pobocks ***@***.***> wrote: Hi Amber, I'm Dave, a co-worker of Bobbi's. Would you be able to provide us with the spreadsheet? I think what's happening is that you're hitting the time-out just because something in 2.x got slower or similar, and just fell past the boundary. I'd like to verify this; it certainly might be something else, after all. If that *is* the problem, chunking it up is probably the right move; I don't think there's really a meaningful way to stream-process Excel files, afaik. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#9 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ANcrUJum9lNSQ6mYh7MSR324R1K1UHSaks5smpD-gaJpZM4PcvER> .

[pobocks]

Hmm, if it's attached here, I think github swallowed it - if you want to email it to dave UNDERSCORE mayo AT harvard DOT com, I'll poke at it (maybe tomorrow!)

[randystern]

At harvard DOT edu, that is! From: pobocks <[email protected]> Reply-To: harvard-library/aspace-import-excel <[email protected]> Date: Wednesday, September 27, 2017 at 3:09 PM To: harvard-library/aspace-import-excel <[email protected]> Cc: Subscribed <[email protected]> Subject: Re: [harvard-library/aspace-import-excel] Adds T00:00:00+00:00 after date in YYYY-MM-DD format (#9) Hmm, if it's attached here, I think github swallowed it - if you want to email it to dave UNDERSCORE mayo AT harvard DOT com, I'll poke at it (maybe tomorrow!) — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub<https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_harvard-2Dlibrary_aspace-2Dimport-2Dexcel_issues_9-23issuecomment-2D332625050&d=DwMFaQ&c=WO-RGvefibhHBZq3fL85hQ&r=4JGa5VtTknXYK4u1_QXigeqoIeLZKZx44Vc_775eJ9Q&m=iR-58104wF7tR0ed0Z6q4LpxuN5acbf7INZVYWCiERo&s=9GOJl9tIDgTjyDzF2OcgkN2OJlkGtyZNSqMGzZwp_aE&e=>, or mute the thread<https://urldefense.proofpoint.com/v2/url?u=https-3A__github.com_notifications_unsubscribe-2Dauth_ADhpb5I-5FMOLDxuSfRjM0Cy6cH4uKVJ0zks5smp1wgaJpZM4PcvER&d=DwMFaQ&c=WO-RGvefibhHBZq3fL85hQ&r=4JGa5VtTknXYK4u1_QXigeqoIeLZKZx44Vc_775eJ9Q&m=iR-58104wF7tR0ed0Z6q4LpxuN5acbf7INZVYWCiERo&s=HlCDCCRPm69rup2IAoB8EGQO-9e9XyGIekX82XVa1UM&e=>.

[bobbi-SMR]

I've committed the template spreadsheet; now at v1.4.