none
How to deserialize from text file to Datatable RRS feed

  • Question

  • I have the file text with format like thies:

    {"AVRData":"$PTNL,AVR,034918.50,+89.5427,Yaw,+3.1977,Tilt,,,4.605,3,1.6,26*0C","GGAData":"$GNGGA,034918.50,0345.41179076,S,10345.90528547,E,4,12,0.9,45.336,M,10.534,M,0.5,0001*76","HDTData":"$GNHDT,89.543,T*18","ZDAData":"$GNZDA,034918.53,15,10,2019,00,00*76","east":"362855.9806182861","gpsTime":"1571111555237","height":"48.1584101314","id":"1814","north":"9584651.5334628057}{"AVRData":"$PTNL,AVR,034919.50,+89.5429,Yaw,+3.1527,Tilt,,,4.610,3,1.5,27*0C","GGAData":"$GNGGA,034919.50,0345.41179806,S,10345.90528697,E,4,13,0.8,45.346,M,10.534,M,0.5,0001*71","HDTData":"$GNHDT,89.543,T*18","ZDAData":"$GNZDA,034919.53,15,10,2019,00,00*77","east":"362856.5912247258","gpsTime":"1571111556244","height":"48.5828441475","id":"1814","north":"9584651.5619625691"} ....

    ....

    ....

    I want to deserialize the data to be table/csv format, with the heading

    AVRData GGAData HDTData ZDAData east gpsTime height id north

    i have tried to use json serializer, but it's not json format completely, so how to achieve that with c# ?

    Thank you so much for the help

    Friday, October 25, 2019 7:18 AM

All replies

  • The first question is where does the data comes from? Then if the source is available e.g. some company ask them for advice to complete this task. 

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, October 25, 2019 10:09 AM
    Moderator
  • Yes deserialize the JSON. Start with that. But yes, some of the data appears to be comma-delimited. So you can use the String.Split Method to split that data using the comma as the delimiter.


    Sam Hobbs
    SimpleSamples.Info

    Friday, October 25, 2019 4:29 PM
  • Hi Ikhsan,

    Thank you for posting here.

    For your question, you want to deserialize from text file to Datatable.

    I split it with string.split and serialize it into an object then store it in an excel file.

    Here is my code:

            static void Main(string[] args)
            {
                string dataPath = @"d:\test\Data.txt";
                string filePath = @"d:\test\test.xlsx";
    
                using (StreamReader reader = new StreamReader(dataPath))
                {
                    String data = reader.ReadToEnd();
                    String[] datas = data.Split(new char[] { '}' }, StringSplitOptions.RemoveEmptyEntries);
    
                    if (!File.Exists(filePath))
                    {
                        CreateXlsxFile(filePath);
                    }
    
                    foreach (var item in datas)
                    {
                        Rootobject rootobject = JsonConvert.DeserializeObject<Rootobject>(item + "}");
                        AppendToXlsx(rootobject, filePath);
                    }
    
                    Console.WriteLine("Press any key to continue...");
                    Console.ReadKey();
                }
            }
            static void CreateXlsxFile(String filePath)
            {
    
                Application xlsx = new Application();
                Workbook wb = xlsx.Workbooks.Add(XlSheetType.xlWorksheet);
                Worksheet xlWorkSheet = (Worksheet)xlsx.ActiveSheet;
                xlsx.Visible = true;
                xlWorkSheet.Cells[1, 1] = "AVRData";
                xlWorkSheet.Cells[1, 2] = "GGAData";
                xlWorkSheet.Cells[1, 3] = "HDTData";
                xlWorkSheet.Cells[1, 4] = "ZDAData";
                xlWorkSheet.Cells[1, 5] = "east";
                xlWorkSheet.Cells[1, 6] = "gpsTime";
                xlWorkSheet.Cells[1, 7] = "height";
                xlWorkSheet.Cells[1, 8] = "id";
                xlWorkSheet.Cells[1, 9] = "north";
    
                wb.SaveAs(filePath);
                xlsx.Quit();
            }
            static void AppendToXlsx(Rootobject rootobject, String filePath)
            {
                Microsoft.Office.Interop.Excel.Application xlApp;
                Workbook xlWorkBook;
                Worksheet xlWorkSheet;
                object missingVal = System.Reflection.Missing.Value;
    
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(
                Filename: filePath,
                   ReadOnly: false,
    
                   Origin: XlPlatform.xlWindows,
                   Editable: true,
                Notify: false,
                   AddToMru: true,
                   Local: true,
                 CorruptLoad: missingVal
                   );
    
                xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1);
                Range range = xlWorkSheet.UsedRange;
                int usedRowCount = range.Rows.Count;
    
    
                int curColumnIdx = 1;
                int curRrowIdx = usedRowCount;
    
                curRrowIdx = curRrowIdx + 1;
                xlWorkSheet.Cells[curRrowIdx, curColumnIdx] = rootobject.AVRData;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = rootobject.GGAData;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = rootobject.HDTData;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = rootobject.ZDAData;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = rootobject.east;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = rootobject.gpsTime;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = rootobject.height;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = rootobject.id;
                xlWorkSheet.Cells[curRrowIdx, ++curColumnIdx] = rootobject.north;
                xlWorkBook.Close(SaveChanges: true);
                Marshal.ReleaseComObject(xlWorkSheet);
                Marshal.ReleaseComObject(xlWorkBook);
                Marshal.ReleaseComObject(xlApp); 
            }
    

    Hope my solution could be helpful.

    Best regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 28, 2019 7:22 AM