Updating Record from Excel Spreadsheet

[Matster]

Is it possible to update an object or symbols record from an Excel document?

 

I was thinking, I could name the object and it would update the record with the reliant information in the spreadsheet.

[Marissa Farrell]

If the excel file is saved as a .csv, you can use the nodes in the File I/O category to pull information from it.

I don't have any examples handy, but I'll make a note to try to get something simple together.

[Matster]

cool, that sounds very useful!

[Matster]

I might be able to put the information into a vectorworks worksheet, is it easier to reference a worksheet embedded into the drawing file? although, I'm not clear how to do that either.

[Marissa Farrell]

There currently aren't nodes for native worksheets in the default content, though I've also written a handful of those. Either way, we can get information in, so it's whatever you think will work best for you!

[mgries]

On 4/11/2019 at 6:59 AM, Marissa Farrell said:

If the excel file is saved as a .csv, you can use the nodes in the File I/O category to pull information from it.

I don't have any examples handy, but I'll make a note to try to get something simple together.

A tool that lets you easily link (and update) records to spreadsheets, especially something on the cloud like google sheets, would be a huge win for vectorworks! But I would settle linking to a .csv on our company server as a good starting point. @Marissa Farrell, please pursue this if you can!

 

Thanks,
Matt

[Miguel Barrera]

You can link an excel file to vectorworks with ODBC. I use the connection to fill-in fields with vectorscript in a form based on a user selection. However, the primary function of the ODBC connection is to link the database (excel file in this case) to Vectorwork objects with a common key field and it seems that is exactly what you are trying to do.

[twk]

21 minutes ago, Miguel Barrera said:

You can link an excel file to vectorworks with ODBC. I use the connection to fill-in fields with vectorscript in a form based on a user selection. However, the primary function of the ODBC connection is to link the database (excel file in this case) to Vectorwork objects with a common key field and it seems that is exactly what you are trying to do.

I've never been able to get this to work, do you have a short step-by-step description of how you've gotthen the ODBC to work?

 

-- edit --

BTW, I use to the openpyxl python module to talk between vectorworks and excel. If only there were better documentation on the external databases connection process for VW

 

Cheers,

Tui

Edited May 20, 2019 by twk

[Miguel Barrera]

First you have to make the ODBC connection in your system with an odbc administrator. I have windows so the following step will be different in a Mac.

1. Open odbcad32.exe located in C:\WINDOWS\syswow64.

2. Create a User DSN or System DSN and click Add

3. Select the driver for the database and click finish. There are a select number of MS drivers including excel files that ship with windows.

4. The following dialog will vary depending on the driver chosen. For databases, it will have connection options such as user name and password but for excel files, you just need to select the spreadsheet. Enter the Data Source Name, which is the database name that VW will see, and click OK.

 

In VW

1. Click on the menu Tools->Database-> Manage Databases...

2. In the next dialog, click on Connect

3. Under Use Data Source Name, the system ODBC that was created above should appear in the drop down list. Select the DSN and click OK.

 

The following is a vectorscript snippet that shows how I get the connection. It looks for a connection in VW first and if not available, it will connect directly to the system ODBC. 

 

	dataName:= kDB_DATANAME;
	tablName:= kDB_TABLNAME;
	InitODBC(dataName,tablName,hdrName);

	shtData.sqlQry:= '';
	shtData.sqlQry:= Concat(shtData.sqlQry,'SELECT * FROM ',tablName);
	shtData.sqlQry:= Concat(shtData.sqlQry,' WHERE ');
	shtData.sqlQry:= Concat(shtData.sqlQry,hdrName.primarySt,' LIKE ''%',shtData.st1Lt,'%''');
	shtData.sqlQry:= Concat(shtData.sqlQry,' OR ');
	shtData.sqlQry:= Concat(shtData.sqlQry,hdrName.secondStr,' LIKE ''%',shtData.st1Lt,'%''');
	WriteLn(shtData.sqlQry);

	foundDB:= FALSE;
	IF DBDocGetDB(dbConnList) THEN
		BEGIN
		IF DBSQLExecute(dataName,shtData.sqlQry,colTot,resSetInst) THEN
			BEGIN
			ProcessRecs;
			foundDB:= TRUE;
			END
		ELSE IF DBSQLExecuteError(errMsg,errState,errCode,intDesc) THEN
				WriteLn(errMsg,', Code = ',errCode);
		END
	ELSE IF DBSQLExecuteDSN(dataName,kDB_USERNAME,kDB_PASSWORD,shtData.sqlQry,colTot,resSetInst) THEN
		BEGIN
		ProcessRecs;
		foundDB:= TRUE;
		END
	ELSE IF DBSQLExecuteError(errMsg,errState,errCode,intDesc) THEN
			WriteLn(errMsg,', Code = ',errCode);