I am having an issue importing from a CSV file regarding time fields. Everything else in my file imports just fine include the date fields. However, I need my items to have valid time fields along with the date and no matter what I do I get some type of format miss match error on the import options and those columns don’t import unless I create a text field. But again, I need these items to have valid time fields so I can view them on a Calendar with both the correct date, start, and end times of these items as they are events. Any thoughts on how to best format a CSV file for time imports?
Hello. Can you please share the example of the date-time field that cannot be imported?
The best format should be ISO, which preserves the date, time, and timezone components.
sorry no file upload available in H&S board. - example below is a paste version but does copy and paste back to excel in correct formatting.
CSV import set attrib to date/time resulting sheet have atrrib set to date only and result with you go back to turn on the time field for the cell all time values have reverted to 00:00
We really need to get a time only attrib or a way to hide date like we can do with time
Driver Company Division Rule Date Driver Informed Number Plate NCR Number Book Number Page_number Start Time Break 1 Start Break 1 End Break 2 Start Break 2 End Break 3 Start Break 3 End Break 4 Start Break 4 End Break 5 Start Break 5 End End Time Total Worked Total Rest Short Term Long Term Status Date Closed Days Open Breach Time Breach Type Breach Level Breach Description
Ahern, Ricky PTY LTD Brisbane BFM 28/02/2023 N QBQ4921 81 28/02/2023 0:00 28/02/2023 2:30 28/02/2023 3:30 28/02/2023 8:15 28/02/2023 15:45 28/02/2023 21:15 28/02/2023 21:45 28/02/2023 23:59 15:00:00 9:00:00 28/02/2023 18:30 Warning Minor Work More Than 14 hours
Ahern, Ricky PTY LTD Brisbane BFM 28/02/2023 N QBQ4921 81 28/02/2023 0:00 28/02/2023 2:30 28/02/2023 3:30 28/02/2023 8:15 28/02/2023 15:45 28/02/2023 21:15 28/02/2023 21:45 28/02/2023 23:59 15:00:00 9:00:00 28/02/2023 22:30 Warning Substantial Work More Than 14 hours
Ahern, Ricky PTY LTD Brisbane BFM 1/03/2023 N QBQ4921 82 1/03/2023 0:00 1/03/2023 2:00 1/03/2023 5:00 1/03/2023 5:30 1/03/2023 19:15 1/03/2023 22:45 1/03/2023 23:15 1/03/2023 23:59 6:45:00 17:15:00 1/03/2023 1:30 Warning Minor Work More Than 14 hours
Ahern, Ricky PTY LTD Brisbane BFM 3/03/2023 N QBQ4921 84 3/03/2023 0:00 3/03/2023 2:30 3/03/2023 4:30 3/03/2023 5:45 3:45:00 20:15:00 3/03/2023 2:15 Warning Minor Work More Than 14 hours
Ahern, Ricky PTY LTD Brisbane BFM 7/03/2023 N QBQ4921 86 7/03/2023 0:00 7/03/2023 0:15 7/03/2023 0:45 7/03/2023 3:45 7/03/2023 4:45 7/03/2023 9:30 7/03/2023 16:30 7/03/2023 21:45 7/03/2023 22:15 7/03/2023 23:59 15:00:00 9:00:00 7/03/2023 19:15 Warning Minor Work More Than 14 hours
Ahern, Ricky PTY LTD Brisbane BFM 7/03/2023 N QBQ4921 86 7/03/2023 0:00 7/03/2023 0:15 7/03/2023 0:45 7/03/2023 3:45 7/03/2023 4:45 7/03/2023 9:30 7/03/2023 16:30 7/03/2023 21:45 7/03/2023 22:15 7/03/2023 23:59 15:00:00 9:00:00 7/03/2023 23:15 Warning Minor Work More Than 14 hours
Ahern, Ricky PTY LTD Brisbane BFM 8/03/2023 N QBQ4921 87 8/03/2023 0:00 8/03/2023 2:45 8/03/2023 4:45 8/03/2023 5:15 8/03/2023 19:30 8/03/2023 23:00 8/03/2023 23:45 8/03/2023 23:59 7:00:00 17:00:00 8/03/2023 0:00 Warning Severe Work More Than 14 hours
Ahern, Ricky PTY LTD Brisbane BFM 8/03/2023 N QBQ4921 87 8/03/2023 0:00 8/03/2023 2:45 8/03/2023 4:45 8/03/2023 5:15 8/03/2023 19:30 8/03/2023 23:00 8/03/2023 23:45 8/03/2023 23:59 7:00:00 17:00:00 8/03/2023 4:45 Warning Minor Work More Than 14 hours
This issue is usually to do with the original formatting of the data to CSV.
As you may notice the time format in the csv is not true to the format needed for import.
excel may show you yyyy/mm/dd hh:mm:ss but when saving to csv it reverts to your regional settings. which usually drops the leading hour and places year at the end of the date.
To capture time StarInfinity can only do it if in correct format of yyyy/mm/dd hh:mm:ss if the time is only h:mm:ss then it will disregard it.
go to your regional settings and change them to match desired output.
then go back to excel and save file as CSV.
This will correctly apply all the date time fields in the correct format to be read by StarInfinity(SI).
becareful if re editing csv in excel as it will always want to drop the :ss if they are :00 of the end of any data read from CSV
Second part to the issue is SI does not set default of date fields to show time so all imported data is lost. The work around here is import a very short basic table with all your elements first. wnsure any attrib being used as labels does not have any null(empty cells) as it does not pick this up well on creation.
after upload.
expand all the attrib needing date with time to show and in correct format.
import your full data version into new folder table it can then use the attrib you already setup correctly to align with your new data to not lose time and also handles blank cells in labels correctly as attrib already exists and has allow blanks set etc.
if handled in a 2 step process you will get your data to come in correctly
A lot of information here. First I did not know that Excel exports the date in the default localized format. That is good for you but not for other applications that want to import that data. I’m not too familiar with Excel, but can you export all the dates in ISO format? It is a standard and it preserves the date, time, and timezone value.
Second, we could add more “import” formats, but I guess the number of variations is huge. So the best option would be to export to some well-known formats, like ISO or YYYY-MM-DD HH:MM:SS