Jump to content

Scripting Modified Worksheet Export to CSV


Recommended Posts

Hi Everyone,

 

I am trying to automate exporting and modifying worksheet data into a specific format to be imported to a label printing software. The end product needs to be a CSV, I have already created a python script that accomplishes this by modifying an excel sheet of the worksheet. The next step is getting this to run directly in VW and skip that middle step but I am not sure how to approach accessing the worksheet directly in vw. 

 

Here is what I have that runs externally, the end will hopefully be necessary since I will need less interface inside vw. Is this something that is possible?

 

Thanks!
Ian

Script Sample.txt

Link to comment

I don't think there is a way to automate the export of a worksheet to either a CSV or Excel file.

 

But accessing the data is really not that hard and you could then write your own CSV file exactly how you need it and avoid the post processing.

 

If you don't mind struggling through a little Vectorscript, the attached thread contains a script that reads the data (and the forumula) in every cell of a worksheet (actually 2 worksheets). This should give you what you need to extract the data. Then all you need is to use the File Handling functions to write that extracted data back out into a file.

 

Ask again if you need more help.

 

 

  • Like 1
Link to comment

So if I'm reading this correctly you want to be able to Run a command in VWX that will export a set of data as a CSV to then import this into your label printer software?

 

First question is where is the Data coming from? Is the data in an already existing worksheet or do you need to get it yourself? If the latter is the case you have a couple of options:

 

You can either build a worksheet that collects the data for you or you can get the data yourself using the ForEachObjectInLayer() command to cycle through all the relevant objects and retrieve the data you need. You would then write this data to a worksheet (have a look at these commands). 

 

You can then export the worksheet to CSV. Below is the code I use to achieve this. It will export the CSV to the specified directory.

 

def exportWStoCSV(ws, FilePath, **kwargs):
    '''
    subRowNo: specify sub row to extract data from
    HeaderRowNo: States which row the Column Headers are in
    :param ws: string
    :param FilePath: string
    :param kwargs:
    :return:
    '''
    import csv
    try:
        h = ws
        vs.TargetSprdSheet(h)  # Makes ws active
        vs.RecalculateWS(h)  # recalculates the worksheet
    except:
        return False
    if 'subRowNo' in kwargs:
        if 'subRowNo' in kwargs:
            subrowNo = kwargs['subRowNo']
            subRowCount = vs.GetWSSubrowCount(h, subrowNo)  # Counts the total Sub Rows in Row 2
            subRowCountRange = list(range(subRowCount + 1))  # Creates a itertable range for N.o of subRows
            zz = subRowCountRange.pop()  # Removes the last value from list to account for 0 index

    number_of_rows_and_cols = list(vs.GetWSRowColumnCount(h))  # Counts total Rows and Columns in Worksheet
    totalColumnsRange = list(range(number_of_rows_and_cols[1]))  # Creates a itertable range for N.o of columns

    totalRowRange = list(range(number_of_rows_and_cols[0]))  # Creates a itertable range for N.o of rows

    if 'HeaderRowNo' in kwargs:
        headerRow = kwargs['HeaderRowNo']  # States which row the Column Headers are in
    else:
        headerRow = 1  # States which row the Column Headers are in
    headersList = [vs.GetWSCellStringN(h, headerRow, eachColumn + 1) for eachColumn in totalColumnsRange]  # Creates a list with all the Column Headers
    if 'subRowNo' in kwargs:
        list_of_cells = [[vs.GetWSSubrowCellString(h, 2, eachColumn + 1, eachSubRow + 1) for eachColumn in totalColumnsRange]
                         for eachSubRow in subRowCountRange]  # Creates a List containing a list of each row e.g overalllist = [[Row1],[Row2],[Row3]]
    else:
        list_of_cells = [
            [vs.GetWSCellStringN(h, eachRow +1, eachColumn + 1) for eachColumn in totalColumnsRange]
            for eachRow in totalRowRange]  # Creates a List containing a list of each row e.g overalllist = [[Row1],[Row2],[Row3]]
        list_of_cells.pop(0)  # Removes header row
    try:
        with open(FilePath, 'w', newline='', encoding='utf-8') as csvfile:  # opens or Creates CSV Called Loom Data
            writer = csv.writer(csvfile)
            writer.writerow(headersList)  # Writes the Headers to the file.
            for eachList in list_of_cells:
                writer.writerow(eachList)  # Writes each Row to CSV
        return True
    except Exception as e:
        vs.AlrtDialog("Error Occured, CSV Not Written! Error Message to follow.")
        vs.AlrtDialog(str("Error Msg: {}".format(e)))

 

 

If you're collecting the data yourself don't necessarily need to write the data to a worksheet you could just build the data into the required format for csvwriter. However I find for a better user experience it's good to show them the data within VWX aswell.

 

Hope that's helpful!

  • 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...