[Solved] Strange behavior with values in imported csv file

Java, C++, C#, Delphi... - Using the UNO bridges
Post Reply
java_bear
Posts: 4
Joined: Thu Dec 08, 2011 1:31 am

[Solved] Strange behavior with values in imported csv file

Post by java_bear »

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

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
thomasjk
Volunteer
Posts: 4451
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Strange behavior with certain values when importing csv

Post by thomasjk »

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 206 times
Tom K.
Windows 10 Home version 1803 17134.165
LibreOffice 5.4.7.2
java_bear
Posts: 4
Joined: Thu Dec 08, 2011 1:31 am

Re: Strange behavior with certain values when importing csv

Post by java_bear »

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

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

Post by java_bear »

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

Post by java_bear »

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
Post Reply