| View previous topic :: View next topic |
| Author |
Message |
skysurfer
General User
Joined: 11 Jul 2012
Posts: 7
|
 Posted: Thu Jul 12, 2012 2:17 am Post subject: Create Datasource from CSV with VB.Net |
 |
|
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 |
|
 |
Axel Richter
General User
Joined: 14 Aug 2011
Posts: 5
|
| Posted: Sat Jul 14, 2012 9:02 pm Post subject: |
|
|
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 |
|
|
skysurfer
General User
Joined: 11 Jul 2012
Posts: 7
|
| Posted: Sun Jul 15, 2012 10:59 pm Post subject: |
|
|
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 |
|
|
|
