Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Christian Stenfors 80 posts 124 karma points
    Apr 04, 2012 @ 10:28
    Christian Stenfors
    0

    help needed creating csv-data from contour records in razor

    Let me start by saying that I am NOT an experienced programmer. So my code and my understanding of some of these issues will surely suffer from that fact :)

    Here is my situation:

    I'm stuck in a project where my job is to provide contour records in CSV format for use in a windows software job-graduate program.

    I have come so far that I've written a razor script that is used when the client through an administration page, choose some form entries to export and click a button. The script captures the IDs of the records to be exported and then retrieve them using The Contour API.

    Then I generate some unique IDs for each record (used to sort the fields so they appear in the same order as they appear in the form). Then i (try to) set the output to be a file and write the new output to the client, who then can download and import the file into his job-candidate program.

    When I download a test export file and import its data into Microsoft Excel using the import data from textfile it works fine, but when the developer of the job candidate program use their import function to create an import feature in the program, they say to me that they can not import the data, and they believe that there are errors in the format.

    One of my problems is probably that I do not know enough about format and encoding requirements and problem areas relating to csv format.

    So it is my hope that one of you skilled developers can look at my code and help me with some advice.

  • Christian Stenfors 80 posts 124 karma points
    Apr 04, 2012 @ 10:30
    Christian Stenfors
    0

    And here is my code:



    @using Umbraco.Forms;
    @using Umbraco.Forms.Core;
    @using Umbraco.Forms.Data.Storage;

    @{
        Dictionary<string, Dictionary<string, List<string>>> fieldSortOrder = new Dictionary<string, Dictionary<string, List<string>>>();
        Dictionary<string, List<string>> fieldSortOrderBeta = new Dictionary<string, List<string>>();
        Dictionary<string, List<string>> columns = new Dictionary<string, List<string>>();
        List<string> values = new List<string>();

        Response.Clear();    
        Response.Buffer = true;
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        Response.ContentType = "application/ms-excel";

        @* IF WE HAVE RECORD ID DATA - START *@
        if(!string.IsNullOrEmpty(@Request["records"])){

            string[] records = @Request["records"].Split(new Char[]{','});

            var recordStorage2 = new RecordStorage();
            var formStorage2 = new FormStorage();
            Record rec2 = recordStorage2.GetRecord(new Guid(@records[0]));
            Form form2 = formStorage2.GetForm(rec2.Form);

            Response.AppendHeader("Content-Disposition","attachment;filename=" + form2.Name + " " + DateTime.Now.ToString("yyyyMMdd-HHmmss") + ".csv");

            @* FOREACH RECORD - START *@
            foreach(string record in @records){

                var recordStorage = new RecordStorage();

                Record rec = recordStorage.GetRecord(new Guid(@record));

                @* FOREACH RECORDFIELD IN RECORD - START *@
                foreach(RecordField field in rec.RecordFields.Values){

                    @* BUILDING A UNIQ ID TO USE FOR SORTING COLUMNS *@
                    string idBeta = @field.Field.PageIndex < 10 ? "0" + @field.Field.PageIndex.ToString() : @field.Field.PageIndex.ToString();
                    idBeta += @field.Field.FieldsetIndex < 10 ? "0" + @field.Field.FieldsetIndex.ToString() : @field.Field.FieldsetIndex.ToString();
                    idBeta += @field.Field.SortOrder < 10 ? "0" + @field.Field.SortOrder.ToString() : @field.Field.SortOrder.ToString();
                    idBeta += " " + @Html.Raw(@field.Field.Caption);

                    @* IF THE COLLECTION OF DATA ROWS ALREADY HAS A ROW WITH THE ID OF THE CURRENT ID *@
                    if(fieldSortOrderBeta.ContainsKey(idBeta) == true)
                    {
                        fieldSortOrderBeta[idBeta].Add(@field.ValuesAsString());
                    }

                    @* IF THE COLLECTION OF DATA ROWS DOES NOT HAVE A ROW WITH THE ID OF THE CURRENT ID *@
                    if(fieldSortOrderBeta.ContainsKey(idBeta) == false)
                    {
                        List<string> dic = new List<string>();
                        dic.Add(@field.ValuesAsString());

                        fieldSortOrderBeta.Add(idBeta, dic);
                    }

                }@* FOREACH RECORDFIELD IN RECORD - END *@

            }@* FOREACH RECORD - END *@

        }@* IF WE HAVE RECORD ID DATA - END*@
    }

    @* START PRINTING OUR DATA *@
    @{

    var list = @fieldSortOrderBeta.Keys.ToList();
    list.Sort();

    string line = "";
    string finalLine = "";
    string columnHeaders = "";
    string columnValues = "";

    foreach (var key in list)
    {
        int arrayDataCount = 0;   

        string trimmedKey = key.Replace(',',' ');
        trimmedKey = trimmedKey.Replace('\"',' ');
        trimmedKey = trimmedKey.Trim();
       
        line = "\"" + @trimmedKey + "\"" + ",";

        foreach (var value in @fieldSortOrderBeta[@key]){

            string trimmedValue = value.Replace(',',' ');
            trimmedValue = trimmedValue.Replace('\"',' ');
            trimmedValue = trimmedValue.Replace('\n',' ');
            trimmedValue = trimmedValue.Replace('\r',' ');

            try{
                bool bolValue = Convert.ToBoolean(value);

                trimmedValue = bolValue == true ? "Ja" : "Nej";            
            }
            catch{}

            if(!String.IsNullOrEmpty(trimmedValue)){

                line += "\"" + @trimmedValue + "\"" + ",";
            }

            if(String.IsNullOrEmpty(trimmedValue)){
                line += " ,";
            }
       
        }

        line = line.Remove(line.Length - 1);
        line += "\n";

        finalLine += @line;

    @*@Html.Raw(@line)*@
    }

    @Html.Raw(@finalLine)
    }

  • Comment author was deleted

    Apr 04, 2012 @ 13:46

    You do know that it's possible to export to csv format from within the records viewer right? Or isn't that an option in your case?

  • Christian Stenfors 80 posts 124 karma points
    Apr 06, 2012 @ 09:18
    Christian Stenfors
    0

    Hi Tim,

    yes, i know that it is possible to export to csv from the recordviewer. But because the two formulars have so many fields (about 200 for the one and about 300 for the other form ) the formviewer can't load entries into the recordviewer. And if i use the icon in the top of the interface, i can't select single records, but get all of them.

    But i would like to continue to use the extra admin-interface functions that i am creating, because it gives me the option to select single records, and also to delete records.

    Do you have any comments/suggestions to my posted code? And maybe you can spot any errors that i am making?

  • Amir Khan 1282 posts 2739 karma points
    Jul 24, 2012 @ 19:51
    Amir Khan
    0

    Hi Christian, were you able to resolve this? I'm dealing with a similar issue.

  • Christian Stenfors 80 posts 124 karma points
    Aug 14, 2012 @ 14:42
    Christian Stenfors
    0

    Hi Amir.

    No, i did not manage to use the contour backend in Umbraco to export data

Please Sign in or register to post replies

Write your reply to:

Draft