Text Qualifier issue while importing CSV into a Excel Worksheet with Aspose.Cells Cloud API

Aspose.Cells Cloud Product Family
1

Dear Support Folks,

We are having an issue with Aspose Cloud not respecting text qualifiers when importing a csv into a worksheet. Is there q way around this issue?

Reference: [https://docs.aspose.cloud/cells/tr/import/csv-data/]

Thanks!

TrentSample.zip (7.0 KB)

2

@trent.bean

We checked your sample.txt and sample.xlsx file and found, sample.xlsx file is correct. Did you generate it via Aspose.Cells for Cloud or did you create it using Microsoft Excel?

Besides, sample.txt has “,” Comma as a Text Qualifier (Separator Character) which is a default separator character. So what problem you are getting? Are you getting problem because of Comma as separator or are you using some other Character as separator?

3

Dear Support Folks,

I generated the Excel File using Aspose.Cells for Cloud.

The comma [,] is a delimiter and newline [\r\n] are the default delimiters in a CSV file. A text qualifier is when you put double quotes ["] around a value to indicate that it should be treated as a string/text value and not a integer/float value.

In the example file above, I am putting double quotes around the “Carton Label” column because I need the string value not the numeric one to show up in the document. Something like “212400000000018222” should return as 212400000000018222 not 2.12400000000018E+017. In excel this is controlled by the setting the field type. Normally when importing CSV using excel, there is a setting for this called a text qualifier.

Capture.jpg (80.9 KB)

Thanks!

TrentCorrect Type.PNG (25.8 KB)
Wrong Type.PNG (27.1 KB)

4

@trent.bean

Please see the following sample code (i.e. XML). Here, you will find ConvertNumericData, set it false and it should fix your issue.

Please note, in the code (i.e. XML), I have already set it false.

C#

//Please upload your sample.csv file in the clould storage
//Please upload your sample.xlsx file in the cloud storage in which you want to import your CSV data

//The following uri will import CSV data from sample.csv into sample.xlsx first worksheet at 6th row and 1st column
string url = "http://api.aspose.com/v1.1/cells/sample.xlsx/importdata";
string signedURL = Sign(url, m_AppSID, m_AppKey);
string xml = @"
<ImportCSVDataOption>
	<DestinationWorksheet>Sheet1</DestinationWorksheet>
	<FirstRow>5</FirstRow>
	<FirstColumn>0</FirstColumn>
	<ConvertNumericData>false</ConvertNumericData>
	<SeparatorString>,</SeparatorString>
	<SourceFile>sample.csv</SourceFile>
</ImportCSVDataOption>";

//Call process command with the signed URL and give XML
ProcessCommand(signedURL, "POST", xml, "xml");

//Now download your sample.xlsx file, CSV data will be imported inside it
5

Dear support folks,

I implemented the change that you suggested, and while it worked as a temporary fix, I ran into a scenario in which it did not. On our reporting platform we handle a lot of reports that are also include charts. The issue is that when the forms are processed with the “ConvertNumericData” set to false that the charts do not work properly.

It seems that you do not currently have this functionality in your platform to use text delimiters during import. While I can manually set this for each report, I’m going to eventually run into an edge case that doesn’t work. Do you have plans to add csv text delimiter support in the future? If so, how long before it would make it into the production build?

Thanks!

Trent

6

@trent.bean

Please share an example (sample Excel file) where it does not work so that we could try the code at our end and log the feature request in our database to support it.