How can I get data from excel?

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.

 

@andeeee

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.

 

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.

Code (csharp):

1.  
2. string con = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=yourexcelfile.xls;";
3.  

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.

Code (csharp):

1.  
2. using System;
3. using System.Data;
4. using System.Data.Odbc;
5.  

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"

Code (csharp):

1.  
2. string yourQuery = "SELECT * FROM [Sheet1$]";
3.  

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.

Code (csharp):

1.  
2. // our odbc connector
3. OdbcConnection oCon = new OdbcConnection(con);
4. // our command object
5. OdbcCommand oCmd = new OdbcCommand(yourQuery, oCon);
6.  

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!

Code (csharp):

1.  
2. // table to hold the data
3. DataTable dtYourData = new DataTable("YourData");
4. // open the connection
5. oCon.Open();
6. // lets use a datareader to fill that table!
7. OdbcDataReader rData = oCmd.ExecuteReader();
8. // now lets blast that into the table by sheer man power!
9. dtYourData.Load(rData);
10. // close that reader!
11. rData.Close();
12. // close your connection to the spreadsheet!
13. oCon.Close();
14. // wow look at us go now! we are on a roll!!!!!
15. // lets now see if our table has the spreadsheet data in it, shall we?
16.  
17. if(dtYourData.Rows.Count > 0)
18. {
19. // do something with the data here
20. // but how do I do this you ask??? good question!
21.   for (int i = 0; i < dtYourData.Rows.Count; i++)
22.   {
23.      // for giggles, lets see the column name then the data for that column!
24.      Debug.Log(dtYourData.Columns[0].ColumnName + " : " + dtYourData.Rows[i][dtYourData.Columns[0].ColumnName].ToString());
25.   }
26. }
27.  

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

 

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

 

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.

Code (csharp):

1.  
2. 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());
3.  

 

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.

 

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.

 

thanks for your help...!!!



Regards
Arun

 

Hi,zumwalt
I have read the excel successfully in Unity,but when Build to exe file ,It won't work, and I can't find the reason, have you builded it successfully?

 

Sent you a PM, but for anyone else who has the same problem, any time you add DLL's to the asset folder for you to use during development, at the time you build your program, you have to have those same DLL's placed in your folder that the game binary is in, regardless if it is a Mac or a PC.

For instance, if you build your game into a folder called MyGame, and the binary is in that folder with sub folders, you have to put the DLL's in the same folder as the game binary, this has been an issue dating back to the old 1.6x days, carried through to 2.0 and will exist in 3.0

Edit:
Forgot to mention, your access file or excel file or whatever, has to be copied into your game folder also, a Unity build will not copy the file for you from your Assets file to the Game executable folder.

 

(sorry for poor english)hi i would like to use My SQL for my database store and retrive the data as like (for eg)id, name and password. what should have to do in order to store and retrive data..

Wheather i have to use any WWW class or server side scripting or to work in unty function itself..

Pls help me..!!!

Thanks,
Arun

 

i using the local server to pick up my data base value from it.. i using the below code . i cant able to load database values in gui

var url = "http://localhost/so/getvalue.php";
var cer : String;



function Start()
{
getval();

}

function getval()
{


//var form = new WWWForm();

var hs_get : WWW = new WWW(url);
yield hs_get;

if(hs_get.error)
{
ar4 =hs_get.error;
Debug.Log(" Error Cause :" +hs_get.error);
}
var cer = hs_get.data.ToString();

if(cer == "123456")
{
ar4 = "data retrived";
}

//var cer = hs_get.data;
//aa = cer;

}


pl help me to relive from this struck
am not getting any connection errors , problem is,
1.i don't know whether the value from database is retrived or not.? how to check this..??
2.i cant able to display the value in gui..?

 

You can check the data returned by the PHP script by entering the URL in a browser (you may need to view the page source to see the actual data). This will verify that the server side is working OK and the connection is being established. Then, work from Unity and print statements to check the data is being pulled in by the WWW object correctly. Finally, you can think about how to display the data with the GUI system. This is likely to be the least troublesome part of the process.

 

Thats greatly helpful,but it doesn't work in js files,check my topic "whats going wrong with my code about database"

 

Thats greatly helpful,but it doesn't work in js files,check my topic "whats going wrong with my code about database"

 

Great info in your posts, have everything working in U3.3 while in the editor and when built in a standalone exe, but is it possible to make a web build for this as well?

I tried building for the web and copying the xls and dll files into the same folder as the output .html and .unity3d files, but it doesn't work. Anything that might work or is this not a possibility for web deployment?

 

Hi everyone,

We're using the scipt to import excel files into Unity.
It works great in the editor, but when compiling we get the following messages:

ArgumentException: The Assembly System.Configuration is referenced by System.Data. But the dll is not allowed to be included or could not be found.

Error building Player: Extracting referenced dlls failed.

We've tried putting the two DLL files into the target folder for the exe, unfortunately that doesn't make a difference.

Any ideas on how to tackle this problem would be greatly appreciated.

 

Hello .. I download the code that imports the data from excel to unity ,, and when I put this data into arrays it must be an object type ,, the problem that I want to cast it to float to be able to multiply it with any other value but this error appears "Cannot cast from source type to destination type" can you help me please ??

 

I am trying to figure this out too. I have tried the same things and havent been able to get it to work from the web. I also tried putting all the excel sheets into the resources folder. I really don't want to go the route of putting it on a server and reading from that. Does anyone have any other ideas?

 

good job!

I want to save data into excel in unity, how can I do that. Does mono provides com interface?

 

@moghazy, have you found out how to do this yet i am trying to use this as a weapon list where i can access the stats of each weapon and calculate with them but its not working very well since i cant use the numbers outside of a string.

Any more help with this and using data as Integers or Booleans would be greatly appreciated.


P.S. Sorry for the resurrection lol

 

I am in generating exe, in the form of GUI shows the inside of the database with inside, in the unity3d can read the display, but export when they can't access database contains information!
ThankYou！

 

changge Player Setting >>Api Compatibility Level === .Net 2.0.You Can Try!

 

changge Player Setting >>Api Compatibility Level === .Net 2.0.You Can Try!

 

Hey everybody.

I have read the excel successfully in Unity,but when Build to exe file ,It won't work, and don't Build the .EXE , have you builded it successfully?

Errors:

ArgumentException: The Assembly System.Configuration is referenced by System.Data. But the dll is not allowed to be included or could not be found.

Error building Player: Extracting referenced dlls failed.

Exception: Error building Player: Extracting referenced dlls failed. :-|

 

can you record data from the unity3d to excel?

 

Umm... why dont you just save the excel file as .txt and read it like any other text asset?

 

hey

Thanks a lot sir. your post on MS Access really saved my college project.

Though i am facing an error i am unable to solve.

I am able to open the connection.

But when i run ExecuteReader it goes out of the try and i get the following error.

System.InvalidOperationException: No data available.
at System.Data.Odbc.OdbcDataReader.GetValue (Int32 i) [0x00000] in <filename unknown>:0
at (wrapper remoting-invoke-with-check) System.Data.Odbc.OdbcDataReader:GetValue (int)
at System.Data.Odbc.OdbcDataReader.get_Item (Int32 i) [0x00000] in <filename unknown>:0
at (wrapper remoting-invoke-with-check) System.Data.Odbc.OdbcDataReader:get_Item (int)
at System.Data.Odbc.OdbcDataReader.get_Item (System.String value) [0x00000] in <filename unknown>:0
at (wrapper remoting-invoke-with-check) System.Data.Odbc.OdbcDataReader:get_Item (string)
at DBreader.readMDB (System.String filetoread) [0x00041] in C:\Users\karthik rao\Documents\Basic\Assets\Scripts\DBreader.cs:40
UnityEngine.Debug:Log(Object)
DBreader:readMDB(String) (at Assets/Scripts/DBreader.cs:52)
DBreader:Start() (at Assets/Scripts/DBreader.cs:14)

Thanks

 

Thanks zumwalt
I gotta a question !!
Is there any way to call dtYourData values in update function?

 

hi zumwalt! i really like your answer about read excel file. but i want to write it more.how to do it?

 

Use it
https://github.com/tsubaki/Unity-Excel-Importer-Maker

 

I would like to use this script but my .xls change so, the data is not the same.

How could I make this dinamically in a way that:

1. the iterator runs through every .xls filled cell every column, and for every column each row.
2. doesn't go out of range


Thank you!

 

haw to write into an xsl file with untiy

 

how I cant wirte into xsl file
i used 3 différent méthode and for each methode i have an error
i used the xsl file that you used
void writeXLS1(string FileToWrite)
{
string con = "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; Dbq=" + FileToWrite + ";";
Debug.Log(con);
// this will delete ALL records that match the column of uName value we us in our parameter
string yourQuery = "INSERT INTO [Sheet1$] (x,y,z) VALUES (x,y,z)";
// our odbc connector
OdbcConnection oCon = new OdbcConnection(con);
// our command object
OdbcCommand oCmd = new OdbcCommand(yourQuery, oCon);
// lets add a record and give some test data for our 3 items
int x = 12;
int y = 123;
int z = 321;
// we shall see what the database says about our command execution
object results = null;
try
{
// open the connection
oCon.Open();
// our command object needs a friend, this friend is a parameter and tells our command
// what on earth @uName is!! parameters are your friend, they help to ensure valid data is passed
oCmd.Parameters.Add(new OdbcParameter("@x", x));
oCmd.Parameters.Add(new OdbcParameter("@y", y));
oCmd.Parameters.Add(new OdbcParameter("@z", z));
// time to execute our delete command.... also, lets just see for giggles what comes back at us
// when the command executes!
oCmd.CommandType = CommandType.Text;
Debug.Log(oCmd.Parameters[0].Value);
// execute the command
results = oCmd.ExecuteNonQuery();
// close your connection to the database query!
oCon.Close();
}
catch (Exception ex)
{
Debug.Log(ex.ToString());
}
finally
{
if (oCon.State != ConnectionState.Closed)
oCon.Close();
oCon.Dispose();
}

}
the erreur for funcction 1 : Trop peu de paramètres. 3 attendu.
at System.Data.Odbc.OdbcCommand.ExecSQL (CommandBehavior behavior, Boolean createReader, System.String sql) [0x00000] in <filename unknown>:0

void writeXLS2(string FileToWrite)
{
string con = "Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; Dbq=" + FileToWrite + ";";
Debug.Log(con);
OdbcConnection oCon = new OdbcConnection(con);

try
{
DataSet myDataSet = new DataSet();
DataTable dtYourData = new DataTable("Sheet1");
//DataTable dtYourData = myDataSet.Tables["Sheet1$"];
Debug.Log(dtYourData.Columns.Count);
DataRow newRow = dtYourData.NewRow();
newRow["x"] = "12";
newRow["y"] = "12";
newRow["z"] = "12";
dtYourData.Rows.Add(newRow);
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);
mySqlDataAdapter.InsertCommand = mySqlCommandBuilder.GetInsertCommand();
// open the connection
oCon.Open();
mySqlDataAdapter.Update(myDataSet, "Sheet1");
oCon.Close();
}
catch (Exception ex)
{
Debug.Log(ex.ToString());
}
}
the error for function 2 : The column 'x' does not belong to the table : Sheet1

void writeXLS(string FileToWrite)
{
try
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
string sql = null;
MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Application.dataPath + "/Book1.xls;Extended Properties=Excel 8.0;");
MyConnection.Open();
myCommand.Connection = MyConnection;
sql = "Insert into [Sheet1$] (x,y,z) values(1,2,3)";
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
MyConnection.Close();
}
catch (Exception ex)
{
Debug.Log(ex.ToString());
}


}
the erreur for function 3 : System.DllNotFoundException: gda-2

 

Hi Everyone,

Its pretty old post , But it really helped me to read and write to Excel file. But
when ever I'm trying to read the file, My Game is lagging. Lag is very much Visible at this line

Code (CSharp):

1. rData = oCmd.ExecuteReader();

I really want to eliminate it. Build is for desktop only.
Much Appreciate your time and Help..

Thanks

 

I know this is an old post. I will try running this in a mac... But you say, it won´t run, right?

 

you can create, Read And Edit your Excel files In unity With Uni-Excel

https://www.assetstore.unity3d.com/en/#!/content/47540

By thanks