Solved

OLEDB Connection String for Excel integration .NET Component Bizagi 11.1

Does anyone have an OLEDB connection string that works
successfully in a .NET component for connecting to Excel in Bizagi 11.1?


I have built a .NET Class Library DLL component following
guidelines in:


http://help.bizagi.com/bpm-suite/en/read_and_write_from_excel.htm


and


http://help.bizagi.com/bpm-suite/en/enterprise__net_example.htm


for component logic and registration, respectively.


From a separate .NET console application, I can invoke my
class library to return the desired XML string representing the contents of an
Excel workbook. I am successful using
different OLEDB connection strings. However,
when I register the class library in Bizagi I am receiving different errors
depending on the connection string I try to use.


Using the following, I have traced the execution to the connExcel.Open()
statement where Bizagi hangs. In the
work portal, I just see the spinning cube that goes on forever. This is being tested with a .XLSX Excel
workbook.

  1. string connString = "Provider=Microsoft.ACE.OLEDB.12.0;" +


    "Data Source=" + this.ExcelPath + ";" +


    "Extended Properties='Excel 12.0 Xml;HDR=YES;'";


    connExcel = new OleDbConnection(connString);


    connExcel.Open();

Has anyone had any success with the Microsoft.Jet.OleDb.4.0
provider? If so, could you share your
connection string?


Thanks in advance for anyone who can help me with this.

Comments (5)

photo
1

Dear Jeffrey,

Regarding this issue, we were analysing it and this topic may be resolved in a .NET forum. The issue is related to linked connection. Please note that this forum is for dealing specifically with the Bizagi BPM Suite and its features - including questions and/or bugs, and client’s suggested ideas or positive comments.

Regards

photo
2

We found a solution to this issue in case anyone else runs across a similar problem in the future. On the Bizagi server where you want to run this component, you must ensure that only the 64-bit ADO driver is installed. It cannot have the 32 bit drivers also installed alongside. Additionally, your DLL must be compiled for 64 bit only (x64). We used Visual Studio. In other words, the 'Any CPU' compilation option will not produce a DLL component that can work. But once we did these things, our registered component worked just fine using the connection string shown.

photo
1

Thank you!

it works

public DataSet ExcelToDataSet(String sqlquery)

{

OSHelper.LogWrite("I am in the method ExcelToDataSet");

OSHelper.LogWrite("CSV file to load = " + strCsvPath);

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + Path.GetDirectoryName(strCsvPath) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");

OSHelper.LogWrite("Directory name = " + Path.GetDirectoryName(strCsvPath));

OSHelper.LogWrite("Filename = " + Path.GetFileName(strCsvPath));

conn.Open();

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + Path.GetFileName(strCsvPath), conn);

DataSet ds = new DataSet("Temp");

adapter.Fill(ds);

conn.Close();

OSHelper.LogWrite("I am leaving the method ExcelToDataSet");

return ds;

}

photo
photo
2

Dear Jeffrey,

Thank you for your comment. This kind of comments work very well to this community.

We are glad that it had been resolved.

Regards

photo
1

Hi,

I have written a console program as follow to read a csv file into a Dataset. I have used the Microsoft.jet.oledb.4.0 and it works great

namespace ImportExcelTest

{

class Program

{

static void Main(string[] args)

{

string FileName = "C:\\work\\temp\\test.csv";

OleDbConnection conn = new OleDbConnection ("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + Path.GetDirectoryName(FileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");

conn.Open();

OleDbDataAdapter adapter = new OleDbDataAdapter ("SELECT * FROM " + Path.GetFileName(FileName), conn);

DataSet ds = new DataSet("Temp");

adapter.Fill(ds);

conn.Close();

}

}

}

When I put the code into a dll project, I get the error that Microsoft jet oledb4.0 or 12.0 is not registered.

I have tried both 32 and 64 bit version of the oledb4.0 software

public DataSet ExcelToDataSet(String sqlquery)

{

OSHelper.LogWrite("I am in the method ExcelToDataSet");

OSHelper.LogWrite("CSV file to load = " + strCsvPath);

//OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + Path.GetDirectoryName(strCsvPath) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");

OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = " + Path.GetDirectoryName(strCsvPath) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");

OSHelper.LogWrite("Directory name = " + Path.GetDirectoryName(strCsvPath));

OSHelper.LogWrite("Filename = " + Path.GetFileName(strCsvPath));

conn.Open();

OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + Path.GetFileName(strCsvPath), conn);

DataSet ds = new DataSet("Temp");

adapter.Fill(ds);

conn.Close();

OSHelper.LogWrite("I am leaving the method ExcelToDataSet");

return ds;

}

Can some help me with which driver to use?

Thanks,

Daniel