none
Excel to Infopath form....

    Question

  • Hi gurus,
    We currently have a MS Excel spreadsheet Form which is used by users to enter new values and then save it. Now we designed a Infopath2007 Form with all the columns similar to excel spreadsheet and published it to Sharepoint MOSS 2007. Now, how to move all those existing  data in Columns of MS Excel2007 to the new Infopath form and eventually save in Sharepoint.(P.S In the spreadsheet each row is considered a Form )

    I have tried:

    a) Using the Import wizard from within Infopath2007, but i want the Excel data to load into my already existing Infopath template and not a New template.

    Should i move all the spreadsheet data to MS Access and try to link with a Infopath form?

    or

    Should i consider moving all the Excel data directly to Sharepoint Content Database(A SQL Server DB)(but then how to create Individual Infopath forms off the data in SQL Server)

    I am totally confused about my options....

    Any Ideas please....

     

    Thanks,
    Kon

    Tuesday, November 13, 2007 7:09 PM

All replies

  • You could create a Utility program that can read in one of those Excel spreadsheets, run through the rows, and programmatically create an InfoPath form (for each row) that is based off the template you created and published to SharePoint, and then programmatically upload the forms to a SharePoint library. An example of programmatically creating a form and uploading it to SharePoint can be found here: http://enterprise-solutions.swits.net/infopath2007/programmatically-create-infopath-form-console-app.htm

     

    Unfortunately, I do not do any Excel programming, but I'm sure you can ask that part of your question in a suitable Excel newsgroup. BTW, the one way I can think of of reading in Excel spreadsheets is by using Excel automation.

    Thursday, November 15, 2007 11:55 PM
  • Remember that InfoPath is just an XML file mapped to an XSD template.  Here's what I'd do:

    1. Publish the template to a form library in SharePoint.
    2. Save Excel doc as a tab-delimited text file.  (A previous response suggested Excel automation, but this could be a bit hairy...)
    3. Create an XML document of the InfoPath form.
    4. Iterate through each row of the Excel text file.  For each field, insert the value into the proper InfoPath form's field via XPath.
    5. At the end of each row, save the XML document to the form library, and update the corresponding list item.

    Here's some pseudo code...

     

    //open sharepoint site

    SPWeb site = SPContext.Current.Web;

     

    //save excel to text file

    string excelData;

    using (System.IO.StreamReader sr = new System.IO.StreamReader(@"C:\pathwhereyousavedyourspreadsheet\spreadsheet.txt"))

    {

    excelData = sr.ReadToEnd();

    sr.Close();

    }

     

    foreach (string row in excelData.Split(new char[] { '\r' }))

    {

     

    //create your infopath form, load up a blank instance of it, save it to your hard drive 

    XmlDocument doc = new XmlDocument();

    doc.Load(@"C:\pathwhereyousavedyourform\formname.xml");

     

    foreach (string column in row.Split(new char[] { '\t' }))

    {

    doc.GetElementById("nameofinfopathfieldthatcorrespondstothiscolumn").InnerText = column;

    }

     

    string infopath = doc.OuterXml;

    SPFolder folder = site.Folders["NAMEOFORMLIBRARY"];

    folder.Files.Add("nameofinfopathform.xml", System.Text.ASCIIEncoding.ASCII.GetBytes(infopath));

    SPList list = site.Lists["NAMEOFORMLIBRARY"];

    SPListItem item = list.ItemCount[list.ItemCount - 1]; //this list item will be the last once since it was just created when we uploaded the form

     

    item["nameofsharepointfield1"] = doc.GetElementById("nameofinfopathfield1").InnerText;

    item["nameofsharepointfield2"] = doc.GetElementById("nameofinfopathfield2").InnerText;

    item["nameofsharepointfield3"] = doc.GetElementById("nameofinfopathfield3").InnerText;

     

    item.Update();

    list.Update();

    site.Update();

    }

    Saturday, November 17, 2007 3:48 AM
  • Hi Domino, Sorry for the late reply, I was on a vacation. First of all Thanks a Lot for your reply.

    Secondly, I could not quite understand what you were trying to tell:

    Here's what i did:

     

    Step1: Created a Form template and Published it to SharePoint.

    Step 2: Save the Excel doc which contains all the rows , as a tab-delimited text file.

    Step 3: Create an XML document of the InfoPath form.(Should i just Open the Form template and do File-->Save As .xml?)

    Step 4: Iterate through each row of the Excel text file. 

    For each field, insert the Excel Value into the proper InfoPath form's field via XPath.

    (Can you please elaborate on how to do this? Also, where do i include the Above pseudo code you gave me? Do i include it in a VBA Macro?)

     

    Step5: To Avoid some code, I was thinking after each iteration; i.e after population each .xml, I can manually Publish the .xml to a Sharepoint Server.

     

    Please Advise. Thanks very much for your help

     

    Thanks,

    Kon

    Tuesday, November 27, 2007 6:40 PM
  •  

    Hi konquistador,

           Were you able to find a solution for this? If so, can you please let me know what you did eventually?

     

    Thanks

     

    Wednesday, April 02, 2008 5:29 PM
  • cdomino. thanks so much. this was very helpful. solved my problem. regards.
    Thursday, August 14, 2008 9:05 PM
  • Step 4: Iterate through each row of the Excel text file. 

    For each field, insert the Excel Value into the proper InfoPath form's field via XPath.

    (Can you please elaborate on how to do this? Also, where do i include the Above pseudo code you gave me? Do i include it in a VBA Macro?)

    Hi,

    I have a similar problem and would like to know how to do Step 4. I have over 1000 rows to go through and this would save me A LOT of time!

    Thanks in advance,

    ZMoti

    Wednesday, March 07, 2012 4:41 PM