Converting multiple local excels to JSON

Question

User1059168712 posted

Hello everyone,

I'm trying to convert excel data to json. It works fine with one excel file, parsing everything, all sheets to one file.

But what if I want to take all excel files in my folder and convert them to one json file. Is it possible?

       string inFilePath = "/Users/falcon/Documents/Projects/";
            string outFilePath = "/Users/falcon/Documents/Projects/jason.json";
            foreach (string filename in Directory.GetFiles(inFilePath))
            {
                if (Path.GetExtension(filename) != ".xlsx") { }
                else
                {
                    using (var inFile = System.IO.File.Open(filename, FileMode.Open, FileAccess.Read))
                    using (var outFile = System.IO.File.CreateText(outFilePath))
                    {
                        using (var reader = ExcelReaderFactory.CreateReader(inFile, new ExcelReaderConfiguration()
                        { FallbackEncoding = Encoding.GetEncoding(1252) }))
                        {
                            var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                            {
                                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                                {
                                    UseHeaderRow = true
                                }
                            });
                            var table = ds.Tables[0];
                            var json = JsonConvert.SerializeObject(table, Formatting.Indented);
                            outFile.Write(json);
                        }
                    }
                }
            }

In this case, it reads files but writes data only from the first file. Thanks everyone who participates the thread!

Answer 1

User-1545767719 posted

                            var table = ds.Tables[0];
                            var json = JsonConvert.SerializeObject(table, Formatting.Indented);
                            outFile.Write(json);

Are you using something special? 

I understand that the JsonConvert.SerializeObject method of Newtonsoft.Json can not serialize a DataTable and FileStream.Write method ("outFile.Write(json);" in your code) does not accept string value.

Answer 2

User1059168712 posted

Hello, I'm not using something special, however it converts and writes all data from one file to jason.json. It works fine with one file.  

Answer 3

User-1545767719 posted

misfowl

Hello, I'm not using something special, however it converts and writes all data from one file to jason.json. It works fine with one file.

I am sorry it was my misunderstanding.

Please see the following Remarks in the Microsoft document File.CreateText(String) Method:

"This method is equivalent to the StreamWriter(String, Boolean) constructor overload with the append parameter set to false. If the file specified by path does not exist, it is created. If the file does exist, its contents are overwritten."

Your code include the "File.CreateText(outFilePath)" method in foreach loop. It means that the jason.json file is overwritten by the "outFile.Write(json)" method and the resultant jason.json file will include only the last json string serialized in the foreach loop.

Try to change the code like below: Below is your code. Try to replace the position of "foreach ( ... )" and "using ( ... )" in it:

 

foreach (string filename in Directory.GetFiles(inFilePath))
{
    using (var outFile = System.IO.File.CreateText(outFilePath))
    {
        // DataSet "ds" created from .xlsx of filename

        var table = ds.Tables[0];
        var json = JsonConvert.SerializeObject(table, Formatting.Indented);
        outFile.Write(json);
    }
}

String "json" will be appended to the jason.json file.