Exporting Worksheet columns to csv

[halfcoupler]

Worksheets can be exported by rows or selected rows.

 

But I want to export only the first two columns, with all rows.

Any hero here who knows hot to get this working ? 😀

[Pat Stanford]

I think the only way you are going to the able to export only the first two columns would be to make another worksheet that only has those two columns. 

 

It might be easier to export the entire worksheet to another program and then drop the unwanted data from there. 

 

Or a script could collect the data an write a CSV file, but the stock exports will not.

[halfcoupler]

Thank You Pat,

yes I am using two worksheets already.

Background to this is that I have (similar to BIM) indexed objects in my drawings that can be exported to my rental software.

In the end I am defining packing lists for jobs already in Vectorworks and export these directly to my rental software, instead of printing the list and retyping it to the rental software.

The export sheet consists only of integer values that can be read by my rental software, but I also need a general worksheet for reviewing what I am doing before exporting.

So in the end, when I want to export 10 packing listst I need to generate 20 Worksheets, or, I genrate one export worksheet and one reviewing worksheet and edit the ctitereia 20 times. Both ways work fine, but I have to repeat this several times per day.

Exporting to an intermediate software is also possible but this will not reduce the workflow steps.

 

A script seems to be the only way, but my scripting skills are too weak for this.

I saw that there are marionette worksheet nodes available, so maybe it is possible to build a marionette that changes the criteria of the export worksheet and the reviewing worksheet simultaniously. I'll carry on researching on this. 😉

 

Edited April 6, 2023 by halfcoupler

[Pat Stanford]

Here is a preliminary proof of concept. The following script will export the selected area of the worksheet to a user selected/created text file using commas to separate the column data and carriage returns to separate the rows. A standard CSV file.

 

It does not currently handle database subrows, but it probably could be modified to do so.

 

It also displays the data written to the file in a VW Message box so you can review it without having to open the file. If you don't like the message then just delete the Message line (or comment is out buy surrounding it in parentheses {}) near the bottom of the script.

 

See how the works for your needs and use it to help define exactly what you need and we can generate a custom version for your export needs.

 

Procedure CSVFromWorksheetSelection;

{©2023  Pat Stanford - pat@coviana.com}
{licensed under the Boost Software License 1.0}
{https://github.com/boostorg/boost/blob/master/LICENSE_1_0.txt}
{TL/DR Use as you want, attribution for source, No warranty}

{Preliminary version proof of concept}
{April 7, 2023}
{Does not handle Worksheet Database Subrows.}

{Exports the worksheet selection to a user specified text file}
{using commas as the delimiter between column data.}
{for users who need a different delimiter, change the value in }
{the DL definition in the const section of the code to the proper character.}

CONST	DL=',';
		CR=CHR(13);

VAR	H1					:Handle;
	CCellRow,CCellCol	:Integer;
	TRow,BRow,LCol,RCol	:Integer;
	TSubRow, BSubRow	:Integer;
	RowCount, ColCount	:Integer;
	S1, CellStr			:DynArray of Char;
	FName				:String;
	
BEGIN
	PutFile('Choose or create file for export','MyVWCSVExport',FName);
	S1:='';
	H1:=GetTopVisibleWS;
	GetWSSelection(H1,CCellRow,CCellCol,TRow,LCol,TSubrow,BRow,RCol, BSubRow);
	For RowCount:= TRow to BRow DO
		BEGIN
			For ColCount:=LCol to RCol DO
				BEGIN
					GetWSCellStringN(H1, RowCount, ColCount,CellStr);
					S1:=Concat(S1,CellStr);
					If ColCount<>RCol Then S1:=Concat(S1,DL);
				End;
			S1:=Concat(S1,CR);
		End;
	Message(S1);
	Open(FName);
	Write(S1);
	Close(FName);
End;

Run(CSVFromWorksheetSelection);

 

[halfcoupler]

Hi Pat,

 

thank you so much! Sadly I only have databased worksheets that evaluate record format data. So I only get the first line of the worksheet.

Maybe it is possible to replace the loop

 

For RowCount:= TRow to BRow DO
        BEGIN
            For ColCount:=LCol to RCol DO
                BEGIN
                    GetWSCellStringN(H1, RowCount, ColCount,CellStr);
                    S1:=Concat(S1,CellStr);
                    If ColCount<>RCol Then S1:=Concat(S1,DL);
                End;

with simply 100 rows ? 😉

 

Edited April 7, 2023 by halfcoupler

[Pat Stanford]

Is it a single database? Or do you have multiple databases in the same worksheet?

[halfcoupler]

I'ts mainly one record format with 5 fields attached to objects.

a typical criterium for these worksheets is:

 

=DATABASE(INSYMBOL & ((R IN ['MyRecordformat']) & (L='MyLayer')))

 

So, quite a simple worksheet.

[Pat Stanford]

OK.  Here is another version that might be closer to your needs.

 

It exports the data in columns A and B or the first Database in a worksheet to a CSV file.

 

Procedure CSVFromWorksheetFirstDB;

{©2023  Pat Stanford - pat@coviana.com}
{licensed under the Boost Software License 1.0}
{https://github.com/boostorg/boost/blob/master/LICENSE_1_0.txt}
{TL/DR Use as you want, attribution for source, No warranty}

{Preliminary version proof of concept}
{April 7, 2023}

{Exports the first two columns of the first database in a worksheet as}
{a CSV file.}

CONST	DL=',';
		CR=CHR(13);

VAR	H1					:Handle;
	CCellRow,CCellCol	:Integer;
	TRow,BRow,LCol,RCol	:Integer;
	TSubRow, SubRowNum	:Integer;
	RowCount, ColCount	:Integer;
	SRowCount			:Integer;
	NumRows, NumCols	:Integer;
	S1, CellStr1, CellStr2			:DynArray of Char;
	FName				:String;
	DBFound				:Boolean;
	
BEGIN
	PutFile('Choose or create file for export','MyVWCSVExport',FName);
	S1:='';
	DBFound:=False;
	H1:=GetTopVisibleWS;
{	GetWSSelection(H1,CCellRow,CCellCol,TRow,LCol,TSubrow,BRow,RCol, BSubRow);}
	GetWSRowColumnCount(H1,NumRows,NumCols);
	For RowCount:= 1 to NumRows DO
		BEGIN
			If ((IsWSDatabaseRow(H1,RowCount)) & (Not(DBFound))) THEN
				BEGIN
					DBFound:=True;
					GetWSSubrowCount(H1,RowCount,SubRowNum);
					
					For SRowCount:= 1 to SubRowNum DO
						BEGIN
							GetWSSubrowCellStrN(H1,RowCount,1,SRowCount, CellStr1);
							GetWSSubrowCellStrN(H1,RowCount,2,SRowCount, CellStr2);
							S1:=Concat(S1,CellStr1,DL,CellStr2,CR);
						End;
				End;
		End;
		
	Message(S1);
	Open(FName);
	Write(S1);
	Close(FName);
End;

Run(CSVFromWorksheetFirstDB);

 

[halfcoupler]

YESSS! 🙂 🙂 🙂

 

Works perfect ! Precisely what I need ! Thank You so much ! That will save me a lots of clicks in future !

One small question: Any chance you could show me the syntax for setting 'MyVWCSVExport' = Worksheet Name ? That would be perfect ! 🙏

[Pat Stanford]

The default file name is in the PutFile line immediately after the first BEGIN.

 

Just make sure to put single quotes around the name when you replace my default.

[halfcoupler]

Yes, thats hardcoding the Worksheet Name, but I want something like:

 

VAR: 

ExportFilename : String

 

BEGIN

ExportFilename := GetTopVisibleWSName ; 

PutFile('Choose or create file for export', ExportFileName, FName);

 

But I don't know the correct expression for "GetTopVisibleWSName"

 

 

[Pat Stanford]

Replace the first four lines after the Begin with:

 

	H1:=GetTopVisibleWS;
	PutFile('Choose or create file for export',GetName(H1),FName);
	S1:='';
	DBFound:=False;

 

[halfcoupler]

works perfect !

thank you for teaching me VS 🙂

 

[Pat Stanford]

Keep asking. Happy to help you put another power arrow in your quiver. 😉