OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Create Datasource from CSV with VB.Net

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
skysurfer
General User
General User


Joined: 11 Jul 2012
Posts: 7

PostPosted: Thu Jul 12, 2012 2:17 am    Post subject: Create Datasource from CSV with VB.Net Reply with quote

Hello.
I found some code to create a database linked to a text file to be used as a data source for mail merge.
But I cannot set the connection properties to specify the desired parameters.
My CSV file are separated by TAB.

Thank you for your help.

Here is my VB.Net code:

Code:

Private Sub CreateDB()
        Dim cURL As String
        Dim oSM, oDesk, oBaseContext, oDoc, oDataSource
        Dim arg(-1)
        oSM = CreateObject("com.sun.star.ServiceManager")
        oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
        oBaseContext = oSM.createInstance("com.sun.star.sdb.DatabaseContext")

        'Create a New Blank OpenOffice Database file
        oDoc = oDesk.loadComponentFromURL("private:factory/sdatabase", "_blank", 0, arg)

        'Below is an example of creating a Flat File database connection to a DIRECTORY of .TXT and .CSV files
        oDoc.Datasource.URL = "sdbc:flat:" + ConvertToURL("N:\Archi7\Test stampa unione OO\Dati")

        cURL = "file:///c:/temp/newDB.odb"
        oDoc.storeAsURL(cURL, arg)


        Dim flatProp(7)
        flatProp(0) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
        flatProp(0).Name = "Extension"
        flatProp(0).Value = "csv"   ' string
        flatProp(1) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
        flatProp(1).Name = "CharSet"
        flatProp(1).Value = 0 ' long
        flatProp(2) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
        flatProp(2).Name = "FixedLength"
        flatProp(2).Value = False   ' boolean
        flatProp(3) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
        flatProp(3).Name = "HeaderLine"
        flatProp(3).Value = True   ' boolean
        flatProp(4) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
        flatProp(4).Name = "FieldDelimiter"
        flatProp(4).Value = ";"   ' string
        flatProp(5) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
        flatProp(5).Name = "StringDelimiter"
        flatProp(5).Value = "" ' string
        flatProp(6) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
        flatProp(6).Name = "DecimalDelimiter"
        flatProp(6).Value = ","   ' string
        flatProp(7) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
        flatProp(7).Name = "ThousandDelimiter"
        flatProp(7).Value = "."   ' string

        oDataSource = oBaseContext.getByName(cURL)
        oDataSource.Info = flatProp
        oDoc.store()

        oBaseContext.registerObject("NewDB", oDataSource)

        oDoc.CurrentController.Frame.Close(True)
        oDoc.dispose()

        oDataSource = Nothing
        oBaseContext = Nothing
        oDoc = Nothing
        oDesk = Nothing
        oSM = Nothing
    End Sub
Back to top
View user's profile Send private message
Axel Richter
General User
General User


Joined: 14 Aug 2011
Posts: 5

PostPosted: Sat Jul 14, 2012 9:02 pm    Post subject: Reply with quote

Hello,

Quote:
My CSV file are separated by TAB.


The String representation of TAB is chr(9). So if your code is a working example for CSV files with semicolon as field delimiter, then it should be:

Code:

...
flatProp(4).Name = "FieldDelimiter"
flatProp(4).Value = chr(9) ' ";"   ' string
...


greetings

Axel
Back to top
View user's profile Send private message
skysurfer
General User
General User


Joined: 11 Jul 2012
Posts: 7

PostPosted: Sun Jul 15, 2012 10:59 pm    Post subject: Reply with quote

Thank you for your answer.
I have found the problem in my code.
The problem was in this property:
flatProp(1) = oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
flatProp(1).Name = "CharSet"
flatProp(1).Value = 0 ' long

If I don't set the property CharSet or i set it to string (e.g. "0") all works fine. In the code I used an integer so no property at all was assigned.

Thank you.

Greetings
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group