I’m using the Write CSV to write Excel table to tab delimited file which seems to work ok, however the date field is incorrectly formatted. Those exported tab delimited files are further down the line ingested by another process and the formatting of the date field must just be so. Are there any ways to adjust how that field is formatted in the CSV? Thanks
You’ll have to adjust it while it’s in the datatable before writing to CSV. I’d recommend changing it from a datetime field to a string field if it isn’t already. Then just change it up so the format is exactly how you’ll need it further down the line.
EDIT: To get the datatype of the date field to see whether it’s already a string or if it’s a datetime field, then do the following: 1). Read excel and save as dt1. 2). Write line = dt1.columns(“datefieldColumnName”).DataType.ToString
If it’s of datatype system.datetime, then you’ll have to create a temp column, loop through dt1 and assign temp column to be row.item(“datefieldColumnName”).Tostring(“MM/dd/yyyy”) or whatever format you need the date to be. Then you can delete the original date column, rename the temp column to the original date column name, and set the ordinal of the column to whatever the original date columns ordinal was
Another way is to format it in Excel, then use SaveAs to convert it to a CSV, which will keep the formatting for the file. For example, you could use Excel Scope, Select Range to select column, format column to the date format you want, then SaveAs and change it to a CSV.
Note: XLS is a tab-delimitted text file by default.
However, I would check the values in the datatable, and if they are in wrong format, then you will need to Loop through each row and change the value to a different format, or like I said open in Excel and format it there then save to CSV in Excel.
Hi Dave - thanks for the advice. I’m getting an error “End of Expression expected” after row.item(“datefieldColumnName”).Tostring(“MM/dd/yyyy”). it’s as if the ToString doesn’t accept formatting options
Hi Clayton - is there a way to switch between tabs in Excel workbook and save each one to a different file? What if i don’t know how many sheets are there and what are their names? Thanks!
I would use an Excel Scope and store the sheets to an array of string. Then, you can simply use Read Range and Write Range to a new workbook inside a For Each loop.
So, logic would be this:
—Excel Scope: Output to workbook application variable, I’ll call wb
------Assign activity: sheetNames = wb.GetSheets
------For each sh In sheetNames
---------Read Range: use sh for sheet name
---------Write Range to different file using data table
If should loop through each sheet name which was stored by using
wb.GetSheets, Read the range, then Write the range, and repeat for each sheet.
Hopefully that was clear
but writing to file does not preserve tab delimited formatting.
sorry. Use Write CSV instead of Write Range if you would like that type of file. Logic to go through each sheet doesn’t change though.