Save File CSV - empty last row

I use a Report to save a CSV. This is my txt template:

<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2
<<End>>

This is working perfectly. But: I always get an empty last row:

3X_6_a_6a403d18502aa9f8ebd3d71621508ae2ebc19f8b.png

How can I get rid of that empty row?
We import this CSV file in our system and itโ€™s getting confused because of this empty row

Solved Solved
0 11 327
1 ACCEPTED SOLUTION

@Bahbus Thank you very much for that hint. I found a workaround to get rid of the empty row.

<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: TOP(ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In]),
COUNT(SELECT(TimeTracking[Key] , [Date] = TODAY()-1))-1)>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2
<<End>>
<<Start: LIST(INDEX(ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In]),
COUNT(SELECT(TimeTracking[Key] , [Date] = TODAY()-1))))>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2<<End>>

The middle part produces all rows, except the last row.

The last part produces only the last row.
By putting the <<End>> tag directly to the end, it will not produce a new line.

@Steve should I report this to AppSheet support so they could fix this โ€œadding a new lineโ€ bug?

View solution in original post

11 REPLIES 11

Steve
Participant V

Try:

<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2<<End>>

I.e., put the <<End>> tag on the last data line rather than on a new line.

Thank you @Steve. With this I will not get an empty new line at the end.
But the side effect is, that all entries will be in the same row.
But I need one row per entry.

3X_a_3_a3985be15fb797468a659d0f5d31195632ef6c90.png

How about a newline after the <<End>> tag?

Nothing changes.

<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2
<<End>>
(Newline here)

will produce this:

<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2<<End>>
(Newline here)

Will produce this:
3X_1_9_197d5d2761dbc58bf962bf9156aea455b3ed92c3.png

Bahbus
Participant V

Is it possible to have an <<END>> tag within an IF statement without AppSheet balking?

It would be combersome but if AppSheet lets you, you could try some sort of IF statement to detect if its the last entry. Have a return then END tag while false, and no return before the END tag when true.

@Bahbus Thank you very much for that hint. I found a workaround to get rid of the empty row.

<<Start: ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In])>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[In]>>;3;2
<<End>>
<<Start: TOP(ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In]),
COUNT(SELECT(TimeTracking[Key] , [Date] = TODAY()-1))-1)>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2
<<End>>
<<Start: LIST(INDEX(ORDERBY(SELECT(TimeTracking[Key] , [Date] = TODAY()-1) , [Employee_In] , FALSE , [In]),
COUNT(SELECT(TimeTracking[Key] , [Date] = TODAY()-1))))>>
<<[Employee_In]>>;1;<<TEXT([Date],"dd.mm.yyyy")&" "&[Out]>>;3;2<<End>>

The middle part produces all rows, except the last row.

The last part produces only the last row.
By putting the <<End>> tag directly to the end, it will not produce a new line.

@Steve should I report this to AppSheet support so they could fix this โ€œadding a new lineโ€ bug?

Ah, yes, I used a similar trick before I learned about how the whole auto-comma added thing for JSON templates worked (I think because of you). I was going to suggest this if tried my first idea and it didnโ€™t work.

You could, but I suspect this wouldnโ€™t be โ€œfixedโ€.

Why do you think so?

I myself wouldnโ€™t consider it a bug; a newline at the end of a CSV file shouldnโ€™t be a problem to anything consuming the file.

Can you elaborate on this?

The system interpret the new line as an entry.

Top Labels in this Space