Community forum

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

Notification

Icon
Error

Options
View
Go to last post Go to first unread
Offline MRomer  
#1 Posted : Wednesday, August 7, 2019 11:08:51 PM(UTC)
MRomer

Rank: Paid support

Joined: 11/27/2012(UTC)
Posts: 51
United States
Location: Memphis, TN

Thanks: 8 times
Was thanked: 8 time(s) in 8 post(s)
Using VisualCron Server 8.5.1.

I'm importing a tab-delimited text file to Excel using the Excel Create task. I've been unable to import a column of dates and have them work as dates when I open the file in Excel. If I have the dates in human-readable format in the text file, they come in as text. If I have the dates as numbers and specify any column format other than "#", they come in as numbers formatted as text.

Is there a way to import the date and have it (a) stored as a number and (b) formatted like "mm/dd/yyyy"?
Offline Support  
#2 Posted : Friday, August 9, 2019 12:22:54 PM(UTC)
Support

Rank: Official support

Joined: 12/1/2015(UTC)
Posts: 192

Thanks: 19 times
Was thanked: 10 time(s) in 9 post(s)
Originally Posted by: MRomer Go to Quoted Post
Using VisualCron Server 8.5.1.

I'm importing a tab-delimited text file to Excel using the Excel Create task. I've been unable to import a column of dates and have them work as dates when I open the file in Excel. If I have the dates in human-readable format in the text file, they come in as text. If I have the dates as numbers and specify any column format other than "#", they come in as numbers formatted as text.

Is there a way to import the date and have it (a) stored as a number and (b) formatted like "mm/dd/yyyy"?


I think I need to see it more clearly and test it myself, is there any sample file(s) you could send to support@visualcron.com and also show screenshot of your settings, and how you want it to look like in the excel?

Have you tried using the Column --> Add and then specified the format?
Michael
Support
http://www.visualcron.com

Please like VisualCron on facebook!
Offline MRomer  
#3 Posted : Monday, August 12, 2019 6:06:46 PM(UTC)
MRomer

Rank: Paid support

Joined: 11/27/2012(UTC)
Posts: 51
United States
Location: Memphis, TN

Thanks: 8 times
Was thanked: 8 time(s) in 8 post(s)
Originally Posted by: Support Go to Quoted Post

I think I need to see it more clearly and test it myself, is there any sample file(s) you could send to support@visualcron.com and also show screenshot of your settings, and how you want it to look like in the excel?

Have you tried using the Column --> Add and then specified the format?


I used the Columns tab under the Excel - Create tab to specify all the columns.

Excel Create Task Date Formats.zip (17kb) downloaded 5 time(s).

Here's a zip file containing the input text file and several cases of outputs. The column in question is "date_sent", which is saved in the text file as a double-precision decimal. All Excel files are created using the Excel - Create task with output as Excel 2007.

Case 1 - Column format set to "@" (default)
Case 2 - Column format set to "#.##"
Case 3 - Column format set to "M/d/yyyy"
Case 4 - Column format set to "yyyy'-'MM'-'dd'T'HH':'mm':'ss"

Only in Case 2 is the date_sent column data presented as numeric. In all other cases, it's number formatted as text, so the recipient of the file has to manually convert the data to numeric and apply the date format to it.
Offline Support  
#4 Posted : Tuesday, August 13, 2019 11:52:54 AM(UTC)
Support

Rank: Official support

Joined: 12/1/2015(UTC)
Posts: 192

Thanks: 19 times
Was thanked: 10 time(s) in 9 post(s)
Originally Posted by: MRomer Go to Quoted Post
Originally Posted by: Support Go to Quoted Post

I think I need to see it more clearly and test it myself, is there any sample file(s) you could send to support@visualcron.com and also show screenshot of your settings, and how you want it to look like in the excel?

Have you tried using the Column --> Add and then specified the format?


I used the Columns tab under the Excel - Create tab to specify all the columns.

Excel Create Task Date Formats.zip (17kb) downloaded 5 time(s).

Here's a zip file containing the input text file and several cases of outputs. The column in question is "date_sent", which is saved in the text file as a double-precision decimal. All Excel files are created using the Excel - Create task with output as Excel 2007.

Case 1 - Column format set to "@" (default)
Case 2 - Column format set to "#.##"
Case 3 - Column format set to "M/d/yyyy"
Case 4 - Column format set to "yyyy'-'MM'-'dd'T'HH':'mm':'ss"

Only in Case 2 is the date_sent column data presented as numeric. In all other cases, it's number formatted as text, so the recipient of the file has to manually convert the data to numeric and apply the date format to it.


Can you describe/show how you convert that data to Date format in excel without VisualCron? I tried but can't seem to convert it at all.
Michael
Support
http://www.visualcron.com

Please like VisualCron on facebook!
Offline MRomer  
#5 Posted : Tuesday, August 13, 2019 4:57:37 PM(UTC)
MRomer

Rank: Paid support

Joined: 11/27/2012(UTC)
Posts: 51
United States
Location: Memphis, TN

Thanks: 8 times
Was thanked: 8 time(s) in 8 post(s)
Originally Posted by: Support Go to Quoted Post
Can you describe/show how you convert that data to Date format in excel without VisualCron? I tried but can't seem to convert it at all.

In Windows, I right-click on the text file and open it with Excel. Then I format the date_sent column with a date/time format. That's the problem; no matter how I prepare the data before running the Excel - Create task, I still have to manually convert or re-format the dates after they're in the Excel file.

If I were going to send the text file instead of an Excel file, I would just write the date_sent data as a date string. Excel converts it automatically and gives it a date/time format.
Offline Support  
#6 Posted : Tuesday, August 13, 2019 5:23:03 PM(UTC)
Support

Rank: Official support

Joined: 12/1/2015(UTC)
Posts: 192

Thanks: 19 times
Was thanked: 10 time(s) in 9 post(s)
Originally Posted by: MRomer Go to Quoted Post
Originally Posted by: Support Go to Quoted Post
Can you describe/show how you convert that data to Date format in excel without VisualCron? I tried but can't seem to convert it at all.

In Windows, I right-click on the text file and open it with Excel. Then I format the date_sent column with a date/time format. That's the problem; no matter how I prepare the data before running the Excel - Create task, I still have to manually convert or re-format the dates after they're in the Excel file.

If I were going to send the text file instead of an Excel file, I would just write the date_sent data as a date string. Excel converts it automatically and gives it a date/time format.


How is the text file generated from start, via VC or something else?

Can you record the whole procedure of everything you're doing from start to finish with some free trial recording software tool, such as Snagit? I need to be able to see what you're doing in action to get a better understanding of how this whole process is happening from start to finish.
Michael
Support
http://www.visualcron.com

Please like VisualCron on facebook!
Offline MRomer  
#7 Posted : Tuesday, August 13, 2019 6:21:22 PM(UTC)
MRomer

Rank: Paid support

Joined: 11/27/2012(UTC)
Posts: 51
United States
Location: Memphis, TN

Thanks: 8 times
Was thanked: 8 time(s) in 8 post(s)
Originally Posted by: Support Go to Quoted Post
How is the text file generated from start, via VC or something else?

The text file is created by a PowerShell script using the ConvertTo-Csv and Out-File cmdlets.
Quote:
Can you record the whole procedure of everything you're doing from start to finish with some free trial recording software tool, such as Snagit? I need to be able to see what you're doing in action to get a better understanding of how this whole process is happening from start to finish.

I've given up and switched to using the ImportExcel PowerShell module to create the Excel file programmatically. I had hoped the VC solution would be as simple as "You just need to format the number like this in your text file and then use this column format in the Excel - Create task." Since that doesn't appear to be the case, I've made a workaround.

Offline Support  
#8 Posted : Tuesday, September 3, 2019 2:55:22 PM(UTC)
Support

Rank: Official support

Joined: 12/1/2015(UTC)
Posts: 192

Thanks: 19 times
Was thanked: 10 time(s) in 9 post(s)
Originally Posted by: MRomer Go to Quoted Post
Originally Posted by: Support Go to Quoted Post
How is the text file generated from start, via VC or something else?

The text file is created by a PowerShell script using the ConvertTo-Csv and Out-File cmdlets.
Quote:
Can you record the whole procedure of everything you're doing from start to finish with some free trial recording software tool, such as Snagit? I need to be able to see what you're doing in action to get a better understanding of how this whole process is happening from start to finish.

I've given up and switched to using the ImportExcel PowerShell module to create the Excel file programmatically. I had hoped the VC solution would be as simple as "You just need to format the number like this in your text file and then use this column format in the Excel - Create task." Since that doesn't appear to be the case, I've made a workaround.



I just wanted to notify you that we've fixed this issue now, and it should convert your numbers properly to a date (in Columns you have to set the data type as "Numbers")

The fix is currently implemented in our 8.5.6 beta, which you can find in the beta section here on the forums.
Michael
Support
http://www.visualcron.com

Please like VisualCron on facebook!
Users browsing this topic
Guest
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Scroll to Top