Excel to SQL - is not working


I am currently writing my Bachelor Thesis. In this context I want to integrate data from my Excel spreadsheet into the SQL database. Behind it is a website with PHP access. In this way I use Xampp as server and use the MYSQL Workbench. I encountered the following errors with the connection of the database and the Execute non query as For each:

Connect Database: The format of the initialization string does not match the specification, which begins at index ‘58’.

Execute non query: The object reference was not set to an object instance.

I append the input values for the respective inputs in the appendix.

Welcome to the community!
must be those quotes as the password… or missing the semicolon at password end?

First you need to make sure you are able to connect into the database. Since you are having an error there you need to fix it before doing anything else (such as debugging the non query). For mysql i believe you must connect using ODBC which requires special software to be installed on the client side (make sure it is 32bit)

Here is a very helpful thread on how to connect to mysql with uipath: Is it possible to connect to mysql database using uipath? If yes? How to connect to MySQL Database?

No, this was not the error.

The Connection now works. But now I have the Problem with the Execute non Query bug, which I write before.

This error ocurs when you try to reference a variable/argument/etc, but you never initialized it. What variables are you using in your non-query? Check each variable one-by-one to make sure it is not null immediately before your non-query

Error ERROR Validation error When processing the expression ““INSERT INTO customer data(customer ID, password, salutation, first name, last name, date of birth, street, postal code, city, employment, email, phone) VALUES(”’+row(0).ToString+”’,’"+row(1).ToString+"’,’“row(2).ToString+”’, ‘"+row(3). ToString+"’,’"+row(4).ToString+"’,’"+row(5).ToString+"’,’"+row(6).ToString+"’,’"+row(7).ToString+"’,’"+row(8).ToString+"’,’"+row(9). ToString+"’,’"+row(10).ToString+"’,’"+row(11).ToString+"’,’"+row(12).ToString+"’)"" at least one compiler error occurred.
End of expression expected.

Where is here the Error?

For sure will be some syntax error in all those quotes, but we cant really help if you dont format well in here, please use Control+Shift+C with the exact string you have for the command. So we can see it better.

Seems you dont have a plus sign before row(2) and you could replace all those values with something like this:
String.Join(",", row.ItemArray.Select(Function(i) If(TypeOf i Is String, "'" + i + "'", i.ToString())))

But how i put it in my code with the string?

“INSERT INTO kundendaten(KundenID, Passwort, Anrede, Vorname, Nachname, Geburtsdatum, Strasse, PLZ, Ort, Beschaeftigung, EMail, Telefon) VALUES(’”+row(0).ToString+"’,’"+row(1).ToString+"’,’"+row(2).ToString+"’, ‘"+row(3).ToString+"’,’"+row(4).ToString+"’,’"+row(5).ToString+"’,’"+row(6).ToString+"’,’"+row(7).ToString+"’,’"+row(8).ToString+"’,’"+row(9).ToString+"’,’"+row(11).ToString+"’)"

will be like this:
INSERT INTO customer data(customer ID, password, salutation, first name, last name, date of birth, street, postal code, city, employment, email, phone) VALUES("+String.Join(",", row.ItemArray.Select(Function(i) If(TypeOf i Is String, "'" + i + "'", i.ToString())))+")

Error ERROR Validation error When processing the expression ““INSERT INTO customer data(CustomerID, Password, Salutation, First Name, Last Name, Date of Birth, Street, Post Code, City, Employment, Email, Phone) VALUES(”+String.Join(”,", row.ItemArray.Select(Function(i) If(TypeOf i Is String, “’” + i + “’”, i.ToString()))+")" at least one compiler error has occurred.
“Option Strict On” does not allow operands of the type “Object” for the ±operator.

My Output is empty. But I don’t know what i must write there.

I’d like to mention that there is a beautiful activity for that:

Insert: Parameter value could not be converted from Double to Int32

This was now my Error.

You don’t need to have anything in the output, it’s optional. If you hover over the AffectedRecords portion, it will tell you a brief description of what it does along with the variable type & direction. In this case, I believe it returns a collection of datarows, but I don’t remember for sure.

As for the ‘Insert’ activity, I personally avoid using it. As you have already seen, it is very easy to run into type issues unless the schema in your datatable matches the table in the database exactly. If you can get it to work it’s great - but that can take a lot of datatable manipulation and troubleshooting. It’s much easier in my opinion to use the ‘execute non query’ like you were using previously and just insert everything one row at a time