Aspose Cells for Java: get original CSV row

Aspose.Cells Product Family
1

I am using Aspose to read a CSV file.

I do not beforehand know the number of cells for each row of the file, but I will need to know it for further processing.

Unfortunately, I see no way to find out the number of cells in a CSV row.

Imagine the following row in the CSV file. It contains 7 cells, 4 of which are empty:

1,2,,4,,,

Using

row.iterator();

Aspose will only return 3 cells, as it ignores all empty cells.

As an alternative, I now do the following:

    Cell lastCell = row.getLastCell();

</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">int</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> count </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="lit" style="margin: 0px; padding: 0px; border: 0px; color: rgb(125, 39, 39);">0</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">
</span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">do</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">{</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">
    cell </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> row</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">.</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">getCellOrNull</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">count</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">);</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">
    </span><span class="typ" style="margin: 0px; padding: 0px; border: 0px; color: rgb(43, 145, 175);">String</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> cellValue </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">=</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> cell </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">==</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">null</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">?</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="str" style="margin: 0px; padding: 0px; border: 0px; color: rgb(125, 39, 39);">""</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">:</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> cell</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">.</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">getStringValueWithoutFormat</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">();</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">

    </span><span class="com" style="margin: 0px; padding: 0px; border: 0px; color: rgb(133, 140, 147);">//do something with the cell value...</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">

    count</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">++;</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">
</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">}</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">while</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">cell </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">==</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="kwd" style="margin: 0px; padding: 0px; border: 0px; color: rgb(16, 16, 148);">null</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">||</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> </span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">!</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);"> lastCell</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">.</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">equals</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">(</span><span class="pln" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">cell</span><span class="pun" style="margin: 0px; padding: 0px; border: 0px; color: rgb(48, 51, 54);">));</span></code></pre><p style="margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 15px; clear: both; color: rgb(36, 39, 41); font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; line-height: 19.5px; background-color: rgb(255, 255, 255);">This works better, as it returns the first 4 cells. However, it still ignores the last 3 cells .</p><p style="margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 15px; clear: both; color: rgb(36, 39, 41); font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; line-height: 19.5px; background-color: rgb(255, 255, 255);">Is there any way to get information about the missing cells? (It would be sufficient for me if Aspose could return the original Row as a String - I could then count the number of commas and find out the number of cells this way)</p><p style="margin: 0px 0px 1em; padding: 0px; border: 0px; font-size: 15px; clear: both; color: rgb(36, 39, 41); font-family: Arial, 'Helvetica Neue', Helvetica, sans-serif; line-height: 19.5px; background-color: rgb(255, 255, 255);">Note: I also posted this on</p><p style="margin: 0px 0px 1em; padding: 0px; border: 0px; clear: both; background-color: rgb(255, 255, 255);"><font color="#242729" face="Arial, Helvetica Neue, Helvetica, sans-serif"><span style="font-size: 15px; line-height: 19.5px;"><a rel="nofollow" href="http://stackoverflow.com/questions/36888987/aspose-cells-for-java-get-original-csv-row/36895201?noredirect=1#comment61464203_36895201">http://stackoverflow.com/questions/36888987/aspose-cells-for-java-get-original-csv-row/36895201?noredirect=1#comment61464203_36895201</a></span></font></p><p style="margin: 0px 0px 1em; padding: 0px; border: 0px; clear: both; background-color: rgb(255, 255, 255);"><font color="#242729" face="Arial, Helvetica Neue, Helvetica, sans-serif"><span style="font-size: 15px; line-height: 19.5px;">and was advised to post it here.</span></font></p>
2

Hi Stefan,


Thank you for contacting Aspose support.

Please note that the cell iterator will ignore any uninitialized/null cells while traversing the collection therefore you have to loop over the cell collection to read all cells including the uninitialized ones. You can use the same logic to count the number of cells in a row.

However, this approach has its own drawbacks. For instance, the APIs such as MaxRow, MaxDataRow, MaxColumn, MaxDataColumn & MaxDisplayRange require extra time to gather the corresponding statistics. Secondly, accessing a cell in a loop as Cells.get(row, column) will cause all cell objects in a range to be instantiated, which may eventually cause OutOfMemoryException for large matrix.

Java

TxtLoadOptions options = new TxtLoadOptions();
options.setSeparator(’,’);
Workbook book = new Workbook(dir + “sample.csv”, options);
Worksheet sheet = book.getWorksheets().get(0);
Cells cells = sheet.getCells();
for (int r = 0; r < cells.getMaxDataRow()+1; r++)
{
for (int c = 0; c < cells.getMaxDataColumn()+1; c++)
{
Cell cell = cells.get(r, c);
System.out.println(cell.getName() + " " + cell.getStringValue());
}
}
3

Hi again,


Adding more to my previous response, if your goal is to count the number of values (regardless of null) in a CSV row, that can be accomplished without using the Aspose.Cells for Java API. Please check the following piece of code for elaboration.

Java

String csv = dir + “sample.csv”;
BufferedReader bReader = new BufferedReader(new FileReader(csv));
String line = “”;
try {
while((line = bReader.readLine()) != null)
{
System.out.println(line);
String[] cells = line.split(",", -1);
System.out.println(cells.length);
}
} catch (IOException e) {
e.printStackTrace();
}
bReader.close();
4

Hi, thanks for this solution, it works but it’s not very elegant. It would be nice if the Aspose API supported that directly.


best regards,
Stefan

5

Hi,

Thanks for your posting and using Aspose.Cells.

We have logged your requirement in our database as a New Feature request. We will look into it and implement this feature if possible. Once there is some fix or other news for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41833 - Get original CSV row

6

Hi,


We have evaluated your issue logged earlier as “CELLSJAVA-41833”. I am afraid, we do not plan to support this feature for now. The most popular feature for importing csv is to read cells data (with formatting) and then user can process this data with cells model. The more important thing for users when using this feature is the performance, if we initialize all empty cells when importing one CSV file, more memory will be used, so it will give no benefit for most users. For your special purpose, we think you should check and count the empty cells at the tail of one row data in the CSV by yourself, it should be the most efficient way for you. We might not support this feature on performance grounds.

Thanks for your understanding!