Connection strings for CSV

English
,
#1

Good Morning

As stated in a previous discussion I use LO portable version from libreoffice.org. I connect to many different data sources and have run into some difficulties trying to move data from multiple sources to multiple destinations. I work in an environment where pretty much every workstation is Windows 10 or above. Here are a couple of Functions that work for me when trying to connect to CSV files:

Function getOleDbConnection() As Object
Dim oParms() As New com.sun.star.beans.PropertyValue
    sURL="sdbc:ado:Provider=Microsoft.Jet.OLEDB.4.0;;Data Source=<full path folder where data files are stored>;Extended Properties=""Text;HDR=Yes;FORMAT=Delimited"""
    oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
    getOleDbConnection = oManager.getConnection(sURL)

End Function

Function getOdbcConnection() As Object
Dim oParms() As New com.sun.star.beans.PropertyValue
    sURL="sdbc:ado:Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=<full path folder where data files are stored>;Extensions=asc,csv,tab,txt;"
    oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
    getOdbcConnection = oManager.getConnection(sURL)
End Function

I have had more success using the Jet Oledb driver than the Microsoft Text ODBC driver. Testing is between making ResultSet vs RowSet.

Thank you

#2 
Function getSdbcFlatFileConnection() As Object
    sURL="sdbc:flat:e:\Documents\checkconn.csv"
        oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
    getSdbcFlatFileConnection= oManager.getConnection(sURL)
End Function

Function getSdbcOdsFileConnection() As Object
    sURL="sdbc:calc:e:\Documents\TEST_ODS.ods"
        oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
    getSdbcOdsFileConnection= oManager.getConnection(sURL)
End Function
1 Like
#3 
REM  *****  BASIC  *****

Sub Main
	con =getOleDbExcelConnection("e:\Documents\CHECK_CONN.xlsx")
	oStatement =con.CreateStatement()
	sSQL = "SELECT * FROM [TABLE$]"
	oResult = oStatement.executeQuery(sSQL)
	While oResult.Next
		print oResult.getString(2)
	wend
End Sub

Function DBQuoteName(sName As String, oCon) As String
Dim sQuote As String
	sQuote = oCon.getMetaData().getIdentifierQuoteString()
	DBQuoteName = sQuote & sName & sQuote
End Function


Public Function Is64bit() As Boolean
	GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
	SF_Platform = createscriptservice("Platform")
	Is64bit= SF_Platform.ArchiTecture<>"32bit"
	SF_Platform.Dispose
End Function

Function getOleDbTextConnection(folderLoc As String) As Object
	If  Is64bit() Then
		sURL="sdbc:ado:Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& folderLoc &";Extended Properties=""Text;HDR=Yes;FORMAT=Delimited"""
	Else
		sURL="sdbc:ado:Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& folderLoc &";Extended Properties=""Text;HDR=Yes;FORMAT=Delimited"""
	End if
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getOleDbTextConnection = oManager.getConnection(sURL)
End Function

Function getOleDbExcelConnection(folderLoc As String) As Object
	If  Is64bit() Then
		sURL="sdbc:ado:Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& folderLoc &";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""
	Else
		sURL="sdbc:ado:Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& folderLoc &";Extended Properties=""Excel 8.0;"""
	End if
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getOleDbExcelConnection = oManager.getConnection(sURL)
End Function

Function getSdbcFlatFileConnection(fileLoc As String) As Object
Dim oParms() As New com.sun.star.beans.PropertyValue
	AppendProperty(oParms(), "Extension", "csv")
	AppendProperty(oParms(), "HeaderLine", True)
	AppendProperty(oParms(), "FieldDelimiter", ",")
	AppendProperty(oParms(), "StringDelimiter", """")
	sURL="sdbc:flat:" & fileLoc
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getSdbcFlatFileConnection= oManager.getConnectionWithInfo(sURL, oParms)
End Function

Function getSdbcOdsFileConnection(fileLoc As String) As Object
	sURL="sdbc:calc:" & fileLoc
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getSdbcOdsFileConnection= oManager.getConnection(sURL)
End Function

Function getOdbcExcelConnection(fileLoc As String) As Object
	If  Is64bit() Then
		sURL="sdbc:ado:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & fileLoc
	Else
		sURL="sdbc:ado:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & fileLoc
	End if
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getOdbcExcelConnection = oManager.getConnection(sURL)
End Function


Function getOdbcTextConnection(folderLoc As String) As Object
	If  Is64bit() Then
		sURL="sdbc:ado:Driver={Microsoft Access Text Driver (*.txt, *.csv)};FIL=text;Extensions=txt,csv,tab,asc;DefaultDir="& folderLoc
	Else
		sURL="sdbc:ado:Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="& folderLoc &";Extensions=asc,csv,tab,txt;"
	End if
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getOdbcTextConnection = oManager.getConnection(sURL)
End Function

Function CreateProperty(sName$, oValue) As com.sun.star.beans.PropertyValue
Dim oProperty As New com.sun.star.beans.PropertyValue
	oProperty.Name = sName
	oProperty.Value = oValue
	CreateProperty() = oProperty
End Function

Sub AppendProperty(oProperties(), sName As String, ByVal oValue)
	AppendToArray(oProperties(), CreateProperty(sName, oValue))
End Sub

Sub AppendToArray(oData(), ByVal x)
Dim iUB As Integer 'The upper bound of the array.
Dim iLB As Integer 'The lower bound of the array.
	iUB = UBound(oData()) + 1
	iLB = LBound(oData())
	ReDim Preserve oData(iLB To iUB)
	oData(iUB) = x
End Sub
1 Like
#4

Here is an updated version

REM  *****  BASIC  *****

Sub Main
	GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
	con =getOdbcExcelConnection("e:\Documents\CHECK_CONN.xlsx")
	oStatement =con.CreateStatement()
	sSQL = "SELECT * FROM [TABLE$]"
	oResult = oStatement.executeQuery(sSQL)
	While oResult.Next
		print oResult.getString(2)
	wend
End Sub

Function DBQuoteName(sName As String, oCon) As String
Dim sQuote As String
	sQuote = oCon.getMetaData().getIdentifierQuoteString()
	DBQuoteName = sQuote & sName & sQuote
End Function


Public Function Is64bit() As Boolean
	Is64bit= SF_Platform.ArchiTecture="64bit"
	SF_Platform.Dispose
End Function

Function getOleDbTextConnection(folderLoc As String) As Object
	If  Is64bit() Then
		sURL="sdbc:ado:Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& folderLoc &";Extended Properties=""Text;HDR=Yes;FORMAT=Delimited"""
	Else
		sURL="sdbc:ado:Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& folderLoc &";Extended Properties=""Text;HDR=Yes;FORMAT=Delimited"""
	End if
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getOleDbTextConnection = oManager.getConnection(sURL)
End Function

Function getOleDbExcelConnection(folderLoc As String) As Object
	If  Is64bit() Then
		sURL="sdbc:ado:Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& folderLoc &";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""
	Else
		sURL="sdbc:ado:Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& folderLoc &";Extended Properties=""Excel 8.0;"""
	End if
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getOleDbExcelConnection = oManager.getConnection(sURL)
End Function

Function getSdbcFlatFileConnection(fileLoc As String) As Object
Dim oParms() As New com.sun.star.beans.PropertyValue
	AppendProperty(oParms(), "Extension", "csv")
	AppendProperty(oParms(), "HeaderLine", True)
	AppendProperty(oParms(), "FieldDelimiter", ",")
	AppendProperty(oParms(), "StringDelimiter", """")
	sURL="sdbc:flat:" & fileLoc
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getSdbcFlatFileConnection= oManager.getConnectionWithInfo(sURL, oParms)
End Function

Function getSdbcOdsFileConnection(fileLoc As String) As Object
	sURL="sdbc:calc:" & fileLoc
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getSdbcOdsFileConnection= oManager.getConnection(sURL)
End Function

Function getOdbcExcelConnection(fileLoc As String) As Object
	If  Is64bit() Then
		sURL="sdbc:ado:Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & fileLoc
	Else
		sURL="sdbc:ado:Driver={Microsoft Excel Driver (*.xls)};DBQ=" & fileLoc
	End if
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getOdbcExcelConnection = oManager.getConnection(sURL)
End Function


Function getOdbcTextConnection(folderLoc As String) As Object
	If  Is64bit() Then
		sURL="sdbc:ado:Driver={Microsoft Access Text Driver (*.txt, *.csv)};FIL=text;Extensions=txt,csv,tab,asc;DefaultDir="& folderLoc
	Else
		sURL="sdbc:ado:Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="& folderLoc &";Extensions=asc,csv,tab,txt;"
	End if
	oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
	getOdbcTextConnection = oManager.getConnection(sURL)
End Function

Function CreateProperty(sName$, oValue) As com.sun.star.beans.PropertyValue
Dim oProperty As New com.sun.star.beans.PropertyValue
	oProperty.Name = sName
	oProperty.Value = oValue
	CreateProperty() = oProperty
End Function

Sub AppendProperty(oProperties(), sName As String, ByVal oValue)
	oProperties=SF_Array.Append(oProperties(), CreateProperty(sName, oValue))
End Sub
#5

This function can be used for connecting to XLSX or XLS files however data can not be changed. Attempt to modify data using updates script will not return any error, however no update will occur. To modify data, please use the getOdbcExcelConnection or getOleDbExcelConnection function.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.