Jump to content
Developer Wiki and Function Reference Links Read more... ×
Matster

Updating Record from Excel Spreadsheet

Recommended Posts

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.

Share this post


Link to post

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.

  • Like 2

Share this post


Link to post

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.

Share this post


Link to post

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!

Share this post


Link to post
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

Share this post


Link to post

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.

Share this post


Link to post
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 by twk

Share this post


Link to post

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);

 

  • Like 1

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×