[Solved] Strange behavior with values in imported csv file

Java, C++, C#, Delphi, ??? - Using the UNO bridges

[Solved] Strange behavior with values in imported csv file

Postby java_bear » Thu Dec 08, 2011 2:02 am

I've come across something really strange when opening a csv file and saving it as an ods file. It appears that certain strings are converted to dates.
The csv is a semi colon separated file. It looks like this:
Code: Select all   Expand viewCollapse view
711;3333;XDK2929101;7696;3604;;;0494 Text Nov;50;509.12
721;3333;XDN5700210;7696;3604;;;0489 Text Nov;50;1049.19
721;2222;XDN5700210;7696;3604;;;0490 Text Nov;50;591.61
721;1111;XDN1111111;7696;3604;;;0092 Text Nov;50;25.79

The problem is the value 'XDN5700210' (third column). In the output ods file it becomes: '10.02.570' (formatted as date).
This is a string value and should of course not be changed like this.

I've tried with different values (other than 'XDN5700210') and this also occurs for the values: XDK2221222, XDK2221221, XDK2221111. I haven't found any pattern here and are pretty confused about this. :?

I use remote soffice (running on the same computer) and generally it seems to work fine. If I try this manually, opening the csv file in the OO gui desktop and saving it as an ods file, the error does not occur.
If there are anyone in this forum that has the same (automatic) conversion (CSV -> ODS) set up, could you then try with the values mentioned to see if this can be reproduced?
Any other suggestings for debugging this are very welcome.

Thanks,
Java_bear
Last edited by java_bear on Thu Dec 08, 2011 4:07 pm, edited 1 time in total.
OpenOffice 3.3 on Windows
java_bear
 
Posts: 4
Joined: Thu Dec 08, 2011 1:31 am

Re: Strange behavior with certain values when importing csv

Postby thomasjk » Thu Dec 08, 2011 6:44 am

I just pasted your data into Calc Used Data-->Text To Columns and set the semicolon as the separator and everything was as expected. No date formats.
Attachments
test.ods
(9.45 KiB) Downloaded 124 times
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
thomasjk
Volunteer
 
Posts: 4426
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Strange behavior with certain values when importing csv

Postby java_bear » Thu Dec 08, 2011 2:59 pm

This is strange. Now I even tried it with bootstrap context and the same thing happens: The values are presented as dates.
I'm unable to add attachments to the post, so below is the class I used to reproduce this (sorry for the lengthy post). Is there anything wrong with it?

Code: Select all   Expand viewCollapse view
import java.io.File;
import com.sun.star.beans.PropertyValue;
import com.sun.star.comp.helper.Bootstrap;
import com.sun.star.frame.XComponentLoader;
import com.sun.star.frame.XDesktop;
import com.sun.star.frame.XStorable;
import com.sun.star.io.IOException;
import com.sun.star.lang.XComponent;
import com.sun.star.lang.XMultiComponentFactory;
import com.sun.star.sheet.XSpreadsheetDocument;
import com.sun.star.sheet.XSpreadsheets;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XComponentContext;


public class CSV2ODS {
   XComponentContext xRemoteContext;
   private XMultiComponentFactory xRemoteServiceManager;

   public static void main(String[] args) {
      CSV2ODS co = new CSV2ODS();
      co.convertFile("c:\\temp\\BK-2.csv", "c:\\temp\\BK-2.ods");
   }

   public void convertFile(String csvFilename, String odsFilename) {
      File file = new File(csvFilename);
      csvFilename = file.toURI().toString();
      file = new File(odsFilename);
      odsFilename = file.toURI().toString();
      try {
         // get the remote office component context
         xRemoteContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
         System.out.println("Connected to a running office ...");
         xRemoteServiceManager = xRemoteContext.getServiceManager();
      } catch( Exception e) {
         e.printStackTrace();
         System.exit(1);
      }
      try {
         // get the Desktop, we need its XComponentLoader interface to load a new document
         Object desktop = xRemoteServiceManager.createInstanceWithContext("com.sun.star.frame.Desktop", xRemoteContext);
         XDesktop xDesktop = (XDesktop)UnoRuntime.queryInterface(XDesktop.class, desktop);

         // query the XComponentLoader interface from the desktop
         XComponentLoader xComponentLoader = (XComponentLoader)UnoRuntime.queryInterface(
               XComponentLoader.class, desktop);
         // create empty array of PropertyValue structs, needed for loadComponentFromURL
         PropertyValue[] loadProps = new PropertyValue[4];
         loadProps[0] = new PropertyValue();
         loadProps[0].Name = "Hidden";
         loadProps[0].Value = Boolean.TRUE;
         loadProps[1] = new PropertyValue();
         loadProps[1].Name = "Headless";
         loadProps[1].Value = Boolean.TRUE;
         loadProps[2] = new PropertyValue();
         loadProps[2].Name = new String("FilterName");
         loadProps[2].Value = new String("Text - txt - csv (StarCalc)");
         loadProps[3] = new PropertyValue();
         loadProps[3].Name = "FilterOptions";
         loadProps[3].Value = "59,34,0,1,1/1/2/1/3/5";
         System.out.println(csvFilename.replaceFirst("file:", "file://"));
         // load new calc file
         XComponent xSpreadsheetComponent = xComponentLoader.loadComponentFromURL(
               csvFilename.replaceFirst("file:", "file://"), "_blank", 0, loadProps);
         // query its XSpreadsheetDocument interface, we want to use getSheets()
         XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface(
               XSpreadsheetDocument.class, xSpreadsheetComponent);
         // use getSheets to get spreadsheets container
         XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
           XStorable storable = (XStorable) UnoRuntime.queryInterface(
                   XStorable.class, xSpreadsheetComponent);

           PropertyValue[] properties = new PropertyValue[2];

           System.out.println("storable = " + storable);
           properties[0] = new PropertyValue();
           properties[0].Name = "Overwrite";
           properties[0].Value = new Boolean(true);
           properties[1] = new PropertyValue();
           properties[1].Name = "FilterName";
           properties[1].Value = "MS Excel 97";
          
           storable.storeAsURL(odsFilename, properties);          
           System.out.println("stored: " + odsFilename);
           xSpreadsheetComponent.dispose();
           storable = null;
           System.out.println("Finished");
           xDesktop.terminate();

      } catch (IOException e) {
         e.printStackTrace();
      } catch (IllegalArgumentException e) {
         e.printStackTrace();
      } catch (Exception e) {
         e.printStackTrace();
      }
      return;
   }    

}

OpenOffice 3.3 on Windows
java_bear
 
Posts: 4
Joined: Thu Dec 08, 2011 1:31 am

Re: Strange behavior with certain values when importing csv

Postby java_bear » Thu Dec 08, 2011 3:11 pm

To add to the confusion: I tried to copy the failing value (XDN5700210) into other rows and columns and that seems to work - the value (string) is not converted into date... Only column no. 3 (C) seems to be failing... :crazy:
OpenOffice 3.3 on Windows
java_bear
 
Posts: 4
Joined: Thu Dec 08, 2011 1:31 am

Re: Strange behavior with certain values when importing csv

Postby java_bear » Thu Dec 08, 2011 3:24 pm

I think I see the problem, it's the FilterOptions setting for opening the csv that makes column 3 into a date value. The last entry specifies "1/1/2/1/3/5" - column 3 has value 5 and that is this format "YY/MM/DD". What I can't fully understand is how OO is able to convert some of these values because the all have the same "look" (three characters + seven digits). Anyway, case closed. :bravo:
OpenOffice 3.3 on Windows
java_bear
 
Posts: 4
Joined: Thu Dec 08, 2011 1:31 am


Return to External Programs

Who is online

Users browsing this forum: No registered users and 2 guests