When exporting Report as CSV, Task Notes and other data shows up in wrong field

[Noopykat]

Hi there,

I am having problems manipulating data when Exporting Reports.

I have created a fixed cost Project which contains multiple tasks and some of those tasks have multiple time entries. In each Task, I have entered notes in the "Reference" section. I used a minus sign '-' to begin each point form comment and end each comment with a period. I have tried removing the period and there was no change in the behavior. When I generate a report so I can read through  my comments and transfer the details of the project, I am unable to.

What happens is that the first row which is reserved for "Type" will show some columns of "Time Entry" and other columns containing comments from the "reference" field. The "Date" column will show date entries and the "Start Time" for the "Time Entry". I thought it was my configuration that was the issue so I tried using "comma delimited" and "tab delimited" with the same results. Then I tried changing the "Add Quotes" to on and then to off with no change. Some of the comments have an equal sign '=' at the beginning of them even though I didn't type it. I should mention though that the HTML works fine. When I open the CSV in CSVed or UEStudio, the same problem with the file exists. I have also tried all possible delimiters and see the same results every time.

I have attached a Print Screen of what I am seeing and to clear up any confusion.


Let me know if you need any further information or what I can do to fix this.


Thanks,

Stephen

[Adam]

Spreadsheets can vary on how they handle CSV importing.  If you want to know more about the CSV format Wikipedia has a pretty good explanation.  http://en.wikipedia.org/wiki/Comma-separated_values

What you may have to do to make it happy is to turn on the "Strip CRLF" option (checkmark).  Usually it's the Carriage Return/Line Feeds that cause the columns to go out-of-whack.  For example Excel does not generally like them, however Numbers seems to be fine.  It may be possible to tell Excel explicitly that a certain column is text and it may handle it better, by using it's import wizard.

[Noopykat]

Adam,

Thanks for a quick response. I mentioned in my message that this issue occurs in Excel and in Apps designed to natively edit CSV files.

I will try again to enable and disable the "CR LF" option and see what happens but this seems to be an issue with the way Time Master handles exporting the Reference field when there are multiple lines of notes.

I'm running Windows 7 and I spend quite a while testing different "export configurations" before finally deciding to contact you. If you have a recommendation as to which settings work best, please let me know as I have already tried quote, comma, colon and tab delimiters with the same results. I believe I have tried the "CR LF" setting on and off and the same thing happened.

If I had to guess what was happening, I'd say that when using the reference field in a task for notes and one task has 4 lines of text and the others may have more or less (one task has 10), it looks like during the export process, the reference field is broken down into individual lines of text. If the numbers aren't equal then when trying to import the data, the fields appear to be shifted over. This is why when you look at the screen print, the "type" field has "lines" of text from the reference field and the times are split between multiple fields.

I look forward to your thoughts.

Stephen.

[Noopykat]

Adam,

This afternoon I was fiddling around with the settings for exporting the report data and I played around with the CR-LF, quotes and delimiter and nothing was working until I started experimenting with the order of the "Export Fields" in the CSV file. I think there may be a bug within the module because if I set the "Project Note" field to be the very last item in the Export Fields list, the notes are not split into multiple fields within the record.

More importantly though, when I open the file in Excel, CSVed or any other program capable of viewing/editing CSV files, the fields are transposed to the correct column, including the Headers.

Other than the possibility of a bug (I can't say as I didn't write the app), does this make sense to you?

Thanks,

Stephen

[Adam]

I really don't think there should be any issue with the Project Note field, but we will look into this.  I did try it on the Mac version of Excel and I know that on the Mac version, if I go thru the Import wizard, if I have CR/LF in the Reference Notes (which would be common if you hit the Return key on the iOS keyboard to separate paragraphs), it *will* wrap the text onto new lines.  I would recommend that you have both the "Add Quotes" ON and the "Strip CRLF" ON.  I did move the Project Note field to the 3rd position in the order and I did not have any issues with it doing anything strange.

Do you use the Project Notes?  If not, I would turn Off any fields in the Export Fields that you don't use.  That way you will have less columns to deal with in the spreadsheet.

Looking again at your picture, it is certainly the CRLF that is wrapping your text onto new lines.