Jump to content
Developer Wiki and Function Reference Links ×

Updating Record from Excel Spreadsheet


Matster

Recommended Posts

  • 1 month later...
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

  • Like 1
Link to comment

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.

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

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 2
Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...