Toadstool
Well-known Member
- Joined
- Mar 5, 2018
- Messages
- 2,549
- Office Version
- 2016
- Platform
- Windows
I have a daily extract file which may have zero to 100 entries. I want to import into my Google Calendar as a CSV but it needs reformatting into the calendar import format so I did that on another tab using formulae like
=IF(ISBLANK(CSV!B2),"",CSV!B2&" - "&CSV!C2&" - "&CSV!D2)
The results look good with nothing appearing where the formulae results in null ""
<tbody>
</tbody>
...but when I Save As CSV it fails on the import as all the rows with null results are exported with the comma delimiters:
Subject,Start Date,Start Time,End Date,End Time,All Day Event,Description,Location,Private
4 - Wasters Anonymous - Save it session 1,3/4/2019,1:00:00 PM,3/7/2019,4:30:00 PM,FALSE,Monday Big 12 83 Y https://www.HoardersRUs.com ,Here 22 Acacia Avenue Nowhere OK 62020,FALSE
3 - Wasters Anonymous - Save it session 2,3/14/2019,1:00:00 PM,3/17/2019,4:30:00 PM,FALSE,Thursday Small 12 83 N https://www.HoardersRUs.com ,There 22 Acacia Avenue Nowhere OK 62020,FALSE
7 - Wasters Anonymous - Save it Refresher,3/24/2019,1:00:00 PM,3/27/2019,4:30:00 PM,FALSE,Sunday Medium 12 83 Y https://www.HoardersRUs.com ,Here 22 Acacia Avenue Nowhere OK 62020,FALSE
2 - Savers & Hoarders - Cleaning out Day,3/27/2019,1:00:00 PM,3/30/2019,4:30:00 PM,FALSE,Wednesday Medium 18 83 Y https://www.HoardersRUs.com ,Here 22 Acacia Avenue Nowhere OK 62020,FALSE
2 - Savers & Hoarders - Cleaning out Night,4/4/2019,,,,FALSE, Medium 18 83 N https://www.HoardersRUs.com ,There 22 Acacia Avenue Nowhere OK 62020,FALSE
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
How do I save as a CSV but omitting the rows where the result is null without manually deleting rows in Excel or Notepad?
=IF(ISBLANK(CSV!B2),"",CSV!B2&" - "&CSV!C2&" - "&CSV!D2)
The results look good with nothing appearing where the formulae results in null ""
Subject | Start Date | Start Time | End Date | End Time | All Day Event | Description | Location | Private |
4 - Wasters Anonymous - Save it session 1 | 3/4/2019 | 1:00:00 PM | 3/7/2019 | 4:30:00 PM | FALSE | Monday Big 12 83 Y https://www.HoardersRUs.com | Here 22 Acacia Avenue Nowhere OK 62020 | FALSE |
3 - Wasters Anonymous - Save it session 2 | 3/14/2019 | 1:00:00 PM | 3/17/2019 | 4:30:00 PM | FALSE | Thursday Small 12 83 N https://www.HoardersRUs.com | There 22 Acacia Avenue Nowhere OK 62020 | FALSE |
7 - Wasters Anonymous - Save it Refresher | 3/24/2019 | 1:00:00 PM | 3/27/2019 | 4:30:00 PM | FALSE | Sunday Medium 12 83 Y https://www.HoardersRUs.com | Here 22 Acacia Avenue Nowhere OK 62020 | FALSE |
2 - Savers & Hoarders - Cleaning out Day | 3/27/2019 | 1:00:00 PM | 3/30/2019 | 4:30:00 PM | FALSE | Wednesday Medium 18 83 Y https://www.HoardersRUs.com | Here 22 Acacia Avenue Nowhere OK 62020 | FALSE |
2 - Savers & Hoarders - Cleaning out Night | 4/4/2019 | FALSE | Medium 18 83 N https://www.HoardersRUs.com | There 22 Acacia Avenue Nowhere OK 62020 | FALSE | |||
<tbody>
</tbody>
...but when I Save As CSV it fails on the import as all the rows with null results are exported with the comma delimiters:
Subject,Start Date,Start Time,End Date,End Time,All Day Event,Description,Location,Private
4 - Wasters Anonymous - Save it session 1,3/4/2019,1:00:00 PM,3/7/2019,4:30:00 PM,FALSE,Monday Big 12 83 Y https://www.HoardersRUs.com ,Here 22 Acacia Avenue Nowhere OK 62020,FALSE
3 - Wasters Anonymous - Save it session 2,3/14/2019,1:00:00 PM,3/17/2019,4:30:00 PM,FALSE,Thursday Small 12 83 N https://www.HoardersRUs.com ,There 22 Acacia Avenue Nowhere OK 62020,FALSE
7 - Wasters Anonymous - Save it Refresher,3/24/2019,1:00:00 PM,3/27/2019,4:30:00 PM,FALSE,Sunday Medium 12 83 Y https://www.HoardersRUs.com ,Here 22 Acacia Avenue Nowhere OK 62020,FALSE
2 - Savers & Hoarders - Cleaning out Day,3/27/2019,1:00:00 PM,3/30/2019,4:30:00 PM,FALSE,Wednesday Medium 18 83 Y https://www.HoardersRUs.com ,Here 22 Acacia Avenue Nowhere OK 62020,FALSE
2 - Savers & Hoarders - Cleaning out Night,4/4/2019,,,,FALSE, Medium 18 83 N https://www.HoardersRUs.com ,There 22 Acacia Avenue Nowhere OK 62020,FALSE
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
,,,,,, , ,
How do I save as a CSV but omitting the rows where the result is null without manually deleting rows in Excel or Notepad?