Hi Everyone,
I would like to know is there any Aspose product that converts Pipe-delimited / CSV to Json and vice versa.
Please let me know.
Thanks,
sxs
1 Like
@sxs,
Aspose.Cells for .NET API supports JSON to CSV conversion as explained under.
Files are also attached here SampleFiles.zip (729 Bytes)
For CSV to JSON conversion, we have logged the issue in our database for investigations. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSNET-47113 - Pipe-delimited / CSV to JSON conversion
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
// Read File
string jsonInput = File.ReadAllText("Sample.json");
// Set Styles
CellsFactory factory = new CellsFactory();
Style style = factory.CreateStyle();
style.HorizontalAlignment = TextAlignmentType.Center;
style.Font.Color = System.Drawing.Color.BlueViolet;
style.Font.IsBold = true;
// Set JsonLayoutOptions
JsonLayoutOptions options = new JsonLayoutOptions();
options.TitleStyle = style;
options.ArrayAsTable = true;
// Import JSON Data
JsonUtility.ImportData(jsonInput, worksheet.Cells, 0, 0, options);
TxtSaveOptions opts = new TxtSaveOptions();
opts.Separator = '|';
// Save Excel file
workbook.Save("ImportingFromJson.txt",opts)Thank you Ahsani.
We are looking forward to see updates on “CELLSNET-47113”.
Thanks,
sxs
@sxs,
This new feature request is logged too recently and is still in the queue for analysis. It may take couple of weeks or so to implement this new feature however exact ETA cannot be provided right now. We will write back here as soon as some feedback is ready to share.
@sxs,
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.
@sxs,
Please try our latest version/fix: Aspose.Cells for .NET v20.1.7 (attached)
See the following sample code for your refernece:
e.g
Sample code:
public void CellsNet47113()
{
Workbook workbook = new Workbook();
string str = File.ReadAllText(Constants.sourcePath + "CellsNet47113.json");
Cells cells = workbook.Worksheets[0].Cells;
JsonLayoutOptions importOptions = new JsonLayoutOptions();
importOptions.ConvertNumericOrDate = true;
importOptions.ArrayAsTable = true;
JsonUtility.ImportData(str, cells, 0, 0, importOptions);
workbook.Save(Constants.destPath + "CellsNet47113.csv");
Range range = cells.MaxDisplayRange;
ExportRangeToJsonOptions exportOptions = new ExportRangeToJsonOptions();
exportOptions.ExportAsString = true;
string ext = JsonUtility.ExportRangeToJson(range, exportOptions);
Assert.AreEqual(str, ext);
TxtLoadOptions textLoadOptions = new TxtLoadOptions();
textLoadOptions.Separator = ',';
workbook = new Workbook(Constants.sourcePath + "CellsNet47113.csv", textLoadOptions);
cells = workbook.Worksheets[0].Cells;
range = cells.MaxDisplayRange;
exportOptions = new ExportRangeToJsonOptions();
exportOptions.ExportAsString = true;
ext = JsonUtility.ExportRangeToJson(range, exportOptions);
Assert.AreEqual(str, ext);
}
Let us know your feedback.
Aspose.Cells20.1.7 For .Net2_AuthenticodeSigned.Zip (5.0 MB)
Aspose.Cells20.1.7 For .Net4.0.Zip (5.0 MB)
Thank you Amjad.
I will check this solution and will let you know.
Thanks,
sxs
this is my Json input
{
“book”: [
{
“id”: “01”,
“language”: “Java”,
“edition”: “third”,
“author”: “Herbert Schildt”,
“address”: {
“streetAddress”: “126”,
“city”: “San Jone”,
“state”: “CA”,
“postalCode”: “394221”
}
},
{
“id”: “02”,
“language”: “C++”,
“edition”: “second”,
“author”: “EAAAA”,
“address”: {
“streetAddress”: “126”,
“city”: “San Jone”,
“state”: “CA”,
“postalCode”: “394221”
}
},
{
“id”: “03”,
“language”: “.Net”,
“edition”: “second”,
“author”: “E.Balagurusamy”,
“address”: {
“streetAddress”: “126”,
“city”: “San Jone”,
“state”: “CA”,
“postalCode”: “394221”
}
},
CSV Output
id,language,edition,author,streetAddress,city,state,postalCode
01,Java,third,Herbert Schildt,126,San Jone,CA,394221
id,language,edition,author,streetAddress,city,state,postalCode
02,C++,second,EAAAA,126,San Jone,CA,394221
id,language,edition,author,streetAddress,city,state,postalCode
03,.Net,second,E.Balagurusamy,126,San Jone,CA,394221
id,language,edition,author,streetAddress,city,state,postalCode
04,C++,second,CCCCC,126,San Jone,CA,394221
the header is repeat in csv, do you know how to resolve this ?
Mycode:
string str = File.ReadAllText(@"C:\Users\xxxx\ASPOSE\SampleFiles\Sample.json");
Cells cells = workbook.Worksheets[0].Cells;
JsonLayoutOptions importOptions = new JsonLayoutOptions();
importOptions.ConvertNumericOrDate = true;
importOptions.ArrayAsTable = true;
importOptions.IgnoreArrayTitle = true;
importOptions.IgnoreObjectTitle = true;
JsonUtility.ImportData(str, cells, 0, 0, importOptions);
workbook.Save(@"C:\" + "CellsNet47113.csv");@sxs,
Thank you for the feedback. We have observed this output and will share our feedback after analyzing it.
Thank you Ahsani.
My only question is, can this Aspose.Cell support multi level (Array type) JSON format. if so please share the details.
@sxs,
We have understood the requirement and analyzing it for the same array type format. Please spare us little time to assist you further.
@sxs,
We only support importing very simple JSON string as attached Sample3.json. We do not know how to import object or array embedded in object now. For Example: which value should be input address.streetAddress or streetAddress for Cell “E1”.
Sample3.zip (315 Bytes)
Could you share an excepted file?
@sxs,
Please try our latest version/fix: Aspose.Cells for .NET v20.1.8 (attached)
Your issue should be fixed in it.
Let us know your feedback.
Aspose.Cells20.1.8 For .Net2_AuthenticodeSigned.Zip (5.0 MB)
Aspose.Cells20.1.8 For .Net4.0.Zip (5.0 MB)
Thank you Amjad, will test this fix and get back to you.
Thanks,
sxs
It’s fixed the issue. From Json - Csv works good.
Output
id|language|edition|author|streetAddress|city|state|postalCode|type|size
01|Java|third|Herbert Schildt|126|San Jone|CA|394221|digital|3MB
02|C++|second||126|San Jone|CA|394221||
03|.Net|second|E.Balagurusamy|126|San Jone|CA|394221||
04|C++|second|CCCCC|126|San Jone|CA|394221||
05|C++|second|PPeter|126|San Jone|CA|394221|print|500 pages
**Our second requirement “Csv - Json” **
do you have sample code ?
Thanks,
sxs
@sxs,
Thank you for the feedback and give a try to the following sample code to convert the CSV file to JSON string.
LoadOptions loadOptions = new LoadOptions(LoadFormat.CSV);
Workbook workbook = new Workbook(path + "book1.csv", loadOptions);
Cell lastCell = workbook.Worksheets[0].Cells.LastCell;
ExportRangeToJsonOptions options = new ExportRangeToJsonOptions();
Range range = workbook.Worksheets[0].Cells.CreateRange(0, 0, lastCell.Row + 1, lastCell.Column + 1);
string data = JsonUtility.ExportRangeToJson(range, options);
Console.WriteLine(data);
Book1.csv.zip (675 Bytes)
output.zip (898 Bytes)
The issues you have found earlier (filed as CELLSNET-47113) have been fixed in Aspose.Cells for .NET v20.2. This message was posted using Bugs notification tool by Amjad_Sahi
how can i get
access to this file