AnsweredAssumed Answered

Parsing excel files with existing SPS2010 list same as parsing CSV files?

Question asked by dustoffdoc on Jan 27, 2015

Hello everyone.  This is my first post.  I welcome any thoughts, comments, or recommendations.  Be advised that I am only self-taught when it comes to sharepoint development.  I am a site collection admin, but do not have "higher" server-side admin access.  Although I have access to Visual Studio and other development tools, any VS-based solutions will be over my head.  I am just learning workflows.  Fortunately, we have Nintex.

To illustrate my environment and specific requirements, I offer the following description:

1)  Existing SPS 2010 list that contains columns labeled 4, 5, 6, and 7 containing records D, E, F, G.  This list requires versioning and indexing (for searches).  There is a single common data point/column in the list and excel files (Column 5)

2)  "Alpha.xls" that contains columns 1, 2, 3, 4, 5 (4 & 5 matches item 1 above) and contains rows A, B, C, D, E (D & E matches item #1)

3)  "Bravo.xls" that contains columns 5, 6, 7, 8, 9 (5, 6 & 7 matches item 1) and rows F, G, H, I, J (F & G matches item #1)

4)  Both spreadsheets are generated by two totally independent systems with which I have no interface capability.  In other words, the spreadsheets will be the only mechanism that can provide updated data that needs to be reflected in the SPS list (item #1).

 

Endstate:

 

When "Alpha.xls" is uploaded to its' dedicated doc library, a workflow automatically starts and:
1.Updates the list with any common records (based on Column 5) from the xls that already exist in the list
2.Adds any records (based on Column 5) to the list from "Alpha.xls" that doesn't currently exist in the list
3.Deletes any records (based on Column 5) from the list that are not in  "Alpha.xls"
4.No columns are ever added or deleted from the list.

 

When "Bravo.xls" is uploaded to its' dedicated doc library, a workflow automatically starts and:
1.Updates the list with any common records (based on Column 5) from the xls that already exist in the list.
2.No columns are ever added or deleted from the list.

 

My research implies that there should be a way to manually upload each spreadsheet into a document library or two different doc libraries (dedicated for this purpose) and have a workflow extract the relevant information from each XLS and update the SPS list as referenced in the above endstate.    I have seen the Nintex-based CSV parse to SPS list solutions but it looks like my solution will be different.

\

Thanks in advance

 

Message was edited by: Mark Bailey  Just added a clarification about which column is the primary data point that is common throughout all lists/excel docs/etc

Outcomes