I want to have an arrangement of parts that I can move and update by changing values in excel. The initial desire would be to change values in an excel table and the press an update button. Then the parts would update accordingly.
One way of doing this would be to export a csv or xml file from excel to a file located in the unity project. Then in Unity you can parse the file and with the information gathered, do whatever is needed to update the parts. Please note that it would also be possible to update the parts on a remote player by using the www class to read the exported file on a server.
Please let me know is you need help in doing this.
Can you give a bit more detail? It’s not really clear what you are trying to do here.
I’m visualizing a modular construction so basically I have a bunch of leggos (all shapes and sizes) on a floor. They’ll be picked up by a crane and put in place according to a prescribed schedule. The guy using this wants to be able to have the starting position of all the blocks on the floor editable in an excel spreadsheet.
Here’s the general workflow.
I have the leggo’s starting position (x,y,z) in an excel table.
I have an update button on the GUI that reads the cordinates and moves the corresponding leggos to the positions in the table.
I can make changes to the table, save it, and then press the update button again to rearrange the leggos.
I did this w/ 3dsmax but he didn’t have/want a copy of max so all I could do was make him some videos. So now I’m hoping to be able to do this w/ Unity but my main problem is reading in the values from excel. I hope this clears it up, if not let me know.
1 Like
Hmm, possible in theory, as long as an odbc driver is installed and you are using windows only. You don’t even need excel installed on the workstation for this to work. Come to think of it, this should be simple and work anyway.
I am still waiting on my U3B6 to import one of my largest gig projects so I can’t test this code yet or create an example project, but, I trust you are a solid coder so this will make sense to you.
First is the connection string you need to hook into an excel workbook, presume you are using Excel 2000 or higher, although might work on 97… C# of course
Can’t be higher than excel 2003, found that out with mono, so make to save as excel 2003 if you are using something newer.
string con = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=yourexcelfile.xls;";
Now with that one line, you have told your program that you want to hook into an excel file using the microsoft excel driver that should be in your system or user types, I think this is already on any machine known to man in the PC world.
Next, you need to use ODBC, so make sure you have a using statement at the top of your code block, probably 3 of them actually.
using System;
using System.Data;
using System.Data.Odbc;
As long as nothing pukes on you in Unity so far, you are golden. Next is the hook into the sheet! To do so, you need a sql type query, note that the first row in a spreadsheet when doing this is your column headers, always. That said, lets presume your first sheet is called “Sheet1”, ok great, so your query to select all rows in that sheet is as follows"
string yourQuery = "SELECT * FROM [Sheet1$]";
Simple enough, we just said to give us all data from Sheet1 tab in the workbook we are hooked into. Now off to the ODBC call itself.
// our odbc connector
OdbcConnection oCon = new OdbcConnection(con);
// our command object
OdbcCommand oCmd = new OdbcCommand(yourQuery, oCon);
Yup you guessed it, our connection and our command object to pull our data from the spreadsheet. Fun so far eh? Now what to do… what to do… our data… hmmm, lets slap it into a table!
// table to hold the data
DataTable dtYourData = new DataTable("YourData");
// open the connection
oCon.Open();
// lets use a datareader to fill that table!
OdbcDataReader rData = oCmd.ExecuteReader();
// now lets blast that into the table by sheer man power!
dtYourData.Load(rData);
// close that reader!
rData.Close();
// close your connection to the spreadsheet!
oCon.Close();
// wow look at us go now! we are on a roll!!!!!
// lets now see if our table has the spreadsheet data in it, shall we?
if(dtYourData.Rows.Count > 0)
{
// do something with the data here
// but how do I do this you ask??? good question!
for (int i = 0; i < dtYourData.Rows.Count; i++)
{
// for giggles, lets see the column name then the data for that column!
Debug.Log(dtYourData.Columns[0].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[0].ColumnName].ToString());
}
}
Now if I have not mistyped anything, you should have your spreadsheet open, read, closed, then the first column name shown with the data for that column displayed. If not then sorry, did all this in the editor here, not in visual studio or anything else, also untested for Unity. Should work though.
Am in Unity creating up a demo package in U3B6, ran into a problem that is a continual problem in U3BX… You have to copy System.Data.dll FROM
C:\Program Files\Unity\Editor\Data\Mono\lib\mono\unity
TO
C:\Program Files\Unity\Editor
—>>>>> AND INTO YOUR PROGRAM ASSETS FOLDER
Otherwise, Unity throws up saying:
Assets/EXCELREADER.cs(4,14): error CS0234: The type or namespace name Data' does not exist in the namespace System’. Are you missing an assembly reference?
Rather anoying I know, I got tired of bug reporting it and complaining about this continual problem with libraries from U3B2 I think, maybe do a bug report on it so that someone else is reporting this continual problem besides me. Will post if I find more DLL’s you need to include.
–>>>>Now to find that odbc driver dll file…
Scratch that, so far, nothing I can do will make Unity support ODBC, I will work on this for a while longer and see if I can get it resolved, if not, you will need to find a different solution.
----->>>> AHAHA HEADWAY <<<<-------
answer to be posted soon with step by step fixes to this post, you need 2 libraries, not just 1, will let you know which two soon
1 Like
Here, unzip this folder someplace and open it using Unity, run it, your debog log should show the values. Basically 2 files needed to be copied from the Mono folder under the Untiy Editor folder for this to work, they are in the asset folder of the project… man what a chore…
First you have to browse to:
C:\Program Files\Unity\Editor\Data\Mono\lib\mono\2.0
Then you have to find the two files of:
System.Data.dll
System.EnterpriseServices.dll
Then you have to copy them to your assets folder.
Now, using the code supplied in the project, it will work, I have already included those two files in the project for you to simplfy this, the thing is, Unity should already know about those files and use them, but it does not.
I have this project folder in my:
C:\Users\Public\Documents\Unity Projects
370846–12841–$exceldemo_199.zip (374 KB)
1 Like
Oh, and if you want to see all cell values for all columns in that spreadsheet, here is a debug log that will do that, I am to lame to create an iterator right now.
Debug.Log(dtYourData.Columns[0].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[0].ColumnName].ToString() + " | " + dtYourData.Columns[1].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[1].ColumnName].ToString() + " | " + dtYourData.Columns[2].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[2].ColumnName].ToString());
1 Like
Thanks, it’ll probably take a bit of time for me to go through everything you put here but I’ll let you know if it all comes out okay.
hi an thanks
i need to know about the same thing in java script and also in Ms Access pllsss help me
regards
Arun
hi help me
thanks
Arun
Access demo attached, however, I am not sure on converting this to unity script (aka their version of javascript), maybe someone else can help you with that, this is done in C#. See attached zipped project folder. I had this placed in my:
C:\Users\Public\Documents\Unity Projects
Image is of the results from reading the database in 2000-2003 format and 2007 format, database1.mdf is saved as 2000-2003 format and database2.accdb is saved as 2007 format. I am pretty sure you can take this example and redo it yourself in javascript.
374352–12946–$accessdemo_290.zip (445 KB)
Hi and thanks for your information
thanks
Arun
The ODBC driver hook should support the following:
(ODBCJT32.DLL hooks to)
text files
access
dBase
Excel
Paradox
There is a different in file formats and such for the driver definition, but the rest of the code other than the connection string would work identical, for the most part anyway.
hi i got error of
System.Data.Odbc.OdbcException : Error [IM002][Microsoft][ODBC Driver Manager] Data source name not found and no default driver at System.Data.Odbc.OdbcConnection.Open ()[0x00000]
Which simply means you do not have the Microsoft ODBC driver installed for Access. At least not the one I have shown or in use. Click on Start, go to your Control Panel, (windows only BTW), Adminitrative Tools, Data Sources (ODBC), Look at the User DSN list, tell me what is in that list.
hi i have got it …
But now i struggling with INSERT DATA into the database…(Ms Access)!!! it shows the error off:
System.Data.Odbc.OdbcException: ERROR [07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 3.atSystem.Data.Odbc.OdbcCommand
.ExecSQL (Command Behavior behavior, Boolean create Reader, System.String sql) [0x00000]
I showed you how to “read” from Ms Access, not how to “write” to it, however, the process to write into access requires a slight different command, you can’t use a “reader” object to “write”, you have to use a command execute query or command execute non query, the difference is that when you execute query, you will receive back an object which is typically a count or a result of the insert command, when you execute non query, you get nothing back, also, when inserting into a database, you have to abide by the table rules, meaning that if you have constraints, you have to make sure to follow those constraints.
Show me your code that you are using to try to insert into the database so I can see what you are doing and I will help you understand what you need to do to change the code to do an insert.
I using the code of JavaScript to use in it, i just modified the code which you send to me, accordingly to JavaScript.
and also delete the previous values in table(have to insert freshly in table and to retrieved back)
its
this my code…
function up_date()
{
var read= Application.dataPath + “/Database1.mdb”;
var con = “Driver={Microsoft Access Driver (*.mdb)}; DBQ=”+read;
Debug.Log(con);
var yourQuery = " INSERT INTO Players (ID, Name, Password) " + " VALUES ( p_id , p_name , p_pass );";
// our odbc connector
var oCon : OdbcConnection = new OdbcConnection(con);
// our command object
var oCmd : OdbcCommand = new OdbcCommand(yourQuery, oCon);
// table to hold the data
var dtYourData : DataTable = new DataTable(“YourData”);
try
{
// open the connection
oCon.Open();
// lets use a datareader to fill that table!
var rData : OdbcDataReader = oCmd.ExecuteReader();
// now lets blast that into the table by sheer man power!
dtYourData.Load(rData);
// close that reader!
rData.Close();
// close your connection to the spreadsheet!
oCon.Close();
// wow look at us go now! we are on a roll!!!
// lets now see if our table has the spreadsheet data in it, shall we?
}
catch (Ex )
{
Debug.Log(Ex);
}
finally
{
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
}
thanks
Arun[/b]
Well, I got good news, and I got bad news. Good news is that attached to this post is code that would delete a record and insert a record, bad news is that there is a Bug in Unity and the ODBC CommandBehavior object is broke, it has the same problem that SQLLite used to have that they fixed. The ExecuteNonQuery and the ExecuteScalar methods do not work in this beta.
So although this is the code to do the work for you, it won’t work with U3B6. I have submitted a bug report, although they are power housing to get U3 released quickly so I am not sure it will be fixed before they get out of beta.
376617–13039–$accessdemo_178.zip (457 KB)