C# Parse CSV to JSON Wrong Format

Question

Take a look and let me know what the hell i'm derping on ;)

[HttpPost]
public ActionResult Upload(HttpPostedFileBase File)
{

    HttpPostedFileBase csvFile = Request.Files["adGroupCSV"];

    byte[] buffer = new byte[csvFile.ContentLength];
    csvFile.InputStream.Read(buffer, 0, csvFile.ContentLength);

    string csvString = System.Text.Encoding.UTF8.GetString(buffer);

    string[] lines = Regex.Split(csvString, "\r");
    List<string[]> csv = new List<string[]>();

    foreach (string line in lines)
    {
        csv.Add(line.Split(','));
    }

    string json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(csv);

        ViewData["CSV"] = json;

        return View(ViewData);
}

This is how it is coming across:

json = "[[\"Col1\",\"Col2\",\"Col3\",\"Col4\",\"Col5\",\"Col6\"],[\"test\",\"test\",\"test\",\"test\",\"http://www.test.com/\",\"test/\"],[\"test\",\"test\",\"test\",\"test\",\"http://www.test.com...

This is how I want it:

{"Col1":"test","Col2":"test","Col3":"test","Col4":"test","Col5":"http://www.test.com/","Col6":"test/"}

Here is an example of the CSV

Col1,Col2,Col3,Col4,Col5,Col6
Test1,Test1,Test1,test1,test.test,test/
Test2,Test2,Test2,test2,test.test,test/
Test3,Test3,Test3,test3,test.test,test/
Test4,Test4,Test4,test4,test.test,test/

+1 for "derping". Never heard that one before. I loved that! — JMarsch
– JMarsch, Commented Mar 18, 2013 at 18:22

I4V · Accepted Answer · 2013-03-18 19:28:16Z

2

You need a dictionary. Just replace

List<string[]> csv = new List<string[]>();

foreach (string line in lines)
{
    csv.Add(line.Split(','));
}

with

var csv = lines.Select(l => l.Split(',')
                            .Select((s,i)=>new {s,i})
                            .ToDictionary(x=>"Col" + (x.i+1), x=>x.s));

It should work...

EDIT

var lines = csvString.Split(new char[] { '\n', '\r' }, StringSplitOptions.RemoveEmptyEntries);
var cols = lines[0].Split(',');
var csv = lines.Skip(1)
               .Select(l => l.Split(',')
                             .Select((s, i) => new {s,i})
                             .ToDictionary(x=>cols[x.i],x=>x.s));

var json = new JavaScriptSerializer().Serialize(csv);

edited Mar 18, 2013 at 19:28

answered Mar 18, 2013 at 18:37

I4V

35.4k6 gold badges71 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brenton Pierce Over a year ago

So close, But instead of "Col" + (x.i+1) I want to use what column names are provided in CSV

Brenton Pierce Over a year ago

any ideas how to get it like this?

{{"total":2,"page":1,"records":2,"rows":[{"id":"1","cell":["test","test","test","test","test"]},{"id":"2","cell":["test","test","test","test","test"]}]}

Brenton Pierce Over a year ago

any thoughts on how to get it here. I updated the code above.

Andrew Walters · Accepted Answer · 2013-03-18 19:20:32Z

2

It looks like you have a an array of string arrays when you just want one object with all your columns as properties on it.

Instead of building up your

List<string[]> csv = new List<string[]>();

Can you make a new object from your JSON, like this:

public class UploadedFileObject
{
    public string Col1 { get; set; }
    public string Col2 { get; set; }
    public string Col3 { get; set; }
    public string Col4 { get; set; }
    public string Col5 { get; set; }
    public string Col6 { get; set; }
}





[HttpPost] public ActionResult Upload(HttpPostedFileBase File)
{

HttpPostedFileBase csvFile = Request.Files["adGroupCSV"];

byte[] buffer = new byte[csvFile.ContentLength];
csvFile.InputStream.Read(buffer, 0, csvFile.ContentLength);

string csvString = System.Text.Encoding.UTF8.GetString(buffer);

string[] lines = Regex.Split(csvString, "\r");
List<UploadedFileObject> returnObject = new List<UploadedFileObject>();

foreach (string line in lines)
{
    String[] lineParts = line.Split(',');
    UploadedFileObject lineObject = new UploadedFileObject();
    lineObject.Col1 = lineParts[0];
    lineObject.Col2 = lineParts[1];
    lineObject.Col3 = lineParts[2];
    lineObject.Col4 = lineParts[3];
    lineObject.Col5 = lineParts[4];
    lineObject.Col6 = lineParts[5];

    returnObject.add(lineObject);
}

string json = new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(returnObject);

    ViewData["CSV"] = json;

    return View(ViewData);
}

edited Mar 18, 2013 at 19:20

answered Mar 18, 2013 at 18:26

Andrew Walters

4,8036 gold badges37 silver badges49 bronze badges

3 Comments

theMayer Over a year ago

In line with this answer, perhaps you could use ServiceStack to deserialize the CSV into an array of objects, then re-serialize it using ServiceStack's JSON serializer? servicestack.net/docs/text-serializers/json-csv-jsv-serializers

Brenton Pierce Over a year ago

I'll give the first answer a shot, don't want to use a 3rd party.

Brenton Pierce Over a year ago

the line csv.add(line.split(',')); doesnt like that

theMayer · Accepted Answer · 2013-03-18 18:29:10Z

1

In line with the previous answer, perhaps you could use ServiceStack to deserialize the CSV into an array of objects, then re-serialize it using ServiceStack's JSON serializer?

http://www.servicestack.net/docs/text-serializers/json-csv-jsv-serializers

answered Mar 18, 2013 at 18:29

theMayer

16.3k8 gold badges65 silver badges93 bronze badges

Comments

Guvante · Accepted Answer · 2013-03-18 18:29:05Z

You need to ensure you are using the format that the JavaScriptSerializer expects you to.

Since you are giving it a List<string[]> it is formatting it as:

"[[list1Item1,list1Item2...],[list2Item1,list2Item2...]]"

Which would correlate in your file as row1 -> list1 etc.

However what you want is the first item from the first list, lining up with the first item in the second list and so on.

I don't know about the exact workings of JavaScriptSerializer, but you could try giving it a Dictionary instead of a List<string[]>, assuming you only had one line of data (two lines total).

This would involve caching the first two lines and doing the following:

for (int i = 0; i < first.Length; i++)
{
    dict.Add(first[i],second[i]);
}

Collectives™ on Stack Overflow

C# Parse CSV to JSON Wrong Format

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related