Can VC change the datatype of an Excel column? - VisualCron

Please note that VisualCron support is not actively monitoring this community forum. Please use our contact page for contacting the VisualCron support directly.

Welcome Guest! To enable all features please Login or Register.

Gary_W
Free support Topic Starter

2021-02-24T16:06:46Z

I'm having a hard time trying to use the Excel-Set cell task to convert a column datatype. Can it be used to change the datatype of a column from Custom to date?
I have a spreadsheet with a funky date format in a custom format. It fails in the conversion to csv and thus the load to the database. If I can first force the format change before the conversion to csv I will be golden.
If someone could point me to instructions I'll give you credit in my ticket when I close it! That's the best I can do lol

EDIT: I did come up with a specific powershell script which I'll include here but I'd still be interested if someone knows how to do it directly from VC.

$source_file = "{JOB(Active|Variable|JobWorkingDir)}\{TRIGGER(Active|LastTrigger|File.Result.Name)}"
Try
{
  # Start Excel
  $xl = New-Object -comobject Excel.Application

  # Don't make it visible or prompt the user to save, etc
  $xl.Visible = $false
  $xl.DisplayAlerts = $false

  # Open the file passed in and make the 1st sheet the current one 
  $wb = $xl.Workbooks.open($source_file)
  $sheet = $wb.Sheets.Item(1)
  
  $sheet.Columns("H").NumberFormat = "mm/dd/yyyy"
  $sheet.Columns("K").NumberFormat = "mm/dd/yyyy"
  
  $wb.Save()
  $wb.Close()
  $xl.Quit()
}
catch
{
  $wb.Close()
  $xl.Quit()
}

Edited by user 2021-02-24T17:34:38Z | Reason: Not specified

Sponsor

Forum information

Wanna join the discussion?! Login to your Forum forum account or Register a new forum account

thomas
Free support

2021-02-25T09:02:11Z

With the set cell task you can change the type of a single cell, but the trouble is that you need to overwrite the cell with a new value. I believe you want to keep the existing values and change the type. Anyway, adding a column like this changed the type of the cell from custom to date

Joey S
Paid support

2021-02-25T21:21:21Z

You would have to create a new Excel sheet with a macro that can convert the column. That part is pretty easy, it would be something like this:

Sub Macro1()

Columns("nameofcolumn").NumberFormat = "0.00"

End Sub

The number format is irrelevant. The macro existing is the point. Then you would have a VC task that can call this macro and run it.

Task - Office - Office Macro
Then point you path and Macro name to what you just created.

That part is all easy - what the macro needs to do though is accept a changeable variable that can point to wherever your existing data is. That is the part I don't know since I do not work with Macros too much.

If you can sort that it would work.

Your data would be in File1
your macro would be in File2
you would use VC to run the macro while passing it a parameter or variable that it would take as the path and column number of the data you want to convert. So you would use a macro you build that is run by VC to change data in a different file.

The alternative, like Thomas said would be to read that column of data so it is an output in VC then create a new column with your data in the format you want.

So that would be a Get Cell - Cell By Reference Range
Let's say you want the third column

After that runs you have the data. You may need to do more manipulation to format it correctly.

Then do a Set Cell Task

Support
Official support

2021-04-23T13:15:24Z

Thanks Joey for your suggestion and Gary for leaving your solution there for others to look at.

Michael
Support
http://www.visualcron.com

Please like VisualCron on facebook!