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>
[TD="class: xl65"]Subject[/TD]
[TD="class: xl65, width: 85"]Start Date[/TD]
[TD="class: xl65, width: 105"]Start Time
[/TD]
[TD="class: xl65, width: 69"]End Date[/TD]
[TD="class: xl65, width: 91"]End Time[/TD]
[TD="class: xl65, width: 85"]All Day Event[/TD]
[TD="class: xl65, width: 268"]Description[/TD]
[TD="class: xl65, width: 161"]Location[/TD]
[TD="class: xl65, width: 48"]Private[/TD]
[TD="class: xl65"]4 - Wasters Anonymous - Save it session 1[/TD]
[TD="class: xl66, align: right"]3/4/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/7/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Monday Big 12 83 Y https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]Here 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]3 - Wasters Anonymous - Save it session 2[/TD]
[TD="class: xl66, align: right"]3/14/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/17/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Thursday Small 12 83 N https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]There 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]7 - Wasters Anonymous - Save it Refresher[/TD]
[TD="class: xl66, align: right"]3/24/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/27/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Sunday Medium 12 83 Y https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]Here 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]2 - Savers & Hoarders - Cleaning out Day[/TD]
[TD="class: xl66, align: right"]3/27/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/30/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Wednesday Medium 18 83 Y https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]Here 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]2 - Savers & Hoarders - Cleaning out Night[/TD]
[TD="class: xl66, align: right"]4/4/2019[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"] Medium 18 83 N https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]There 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
</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 ""
<tbody>
[TD="class: xl65"]Subject[/TD]
[TD="class: xl65, width: 85"]Start Date[/TD]
[TD="class: xl65, width: 105"]Start Time
[/TD]
[TD="class: xl65, width: 69"]End Date[/TD]
[TD="class: xl65, width: 91"]End Time[/TD]
[TD="class: xl65, width: 85"]All Day Event[/TD]
[TD="class: xl65, width: 268"]Description[/TD]
[TD="class: xl65, width: 161"]Location[/TD]
[TD="class: xl65, width: 48"]Private[/TD]
[TD="class: xl65"]4 - Wasters Anonymous - Save it session 1[/TD]
[TD="class: xl66, align: right"]3/4/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/7/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Monday Big 12 83 Y https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]Here 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]3 - Wasters Anonymous - Save it session 2[/TD]
[TD="class: xl66, align: right"]3/14/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/17/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Thursday Small 12 83 N https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]There 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]7 - Wasters Anonymous - Save it Refresher[/TD]
[TD="class: xl66, align: right"]3/24/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/27/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Sunday Medium 12 83 Y https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]Here 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]2 - Savers & Hoarders - Cleaning out Day[/TD]
[TD="class: xl66, align: right"]3/27/2019[/TD]
[TD="class: xl67, align: right"]1:00:00 PM[/TD]
[TD="class: xl66, align: right"]3/30/2019[/TD]
[TD="class: xl67, align: right"]4:30:00 PM[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]Wednesday Medium 18 83 Y https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]Here 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"]2 - Savers & Hoarders - Cleaning out Night[/TD]
[TD="class: xl66, align: right"]4/4/2019[/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"] Medium 18 83 N https://www.HoardersRUs.com [/TD]
[TD="class: xl65"]There 22 Acacia Avenue Nowhere OK 62020[/TD]
[TD="class: xl65, align: center"]FALSE[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
</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?