Как конвертировать искаженные даты в смешанные форматы?

Question

Я работаю над проектом по очистке данных моей компании. Один особенно ужасный файл - это лист Excel с приблизительно 30000 строк.

Столбцы дат на этом листе заполнены датами в разных форматах, многие из которых не являются допустимыми форматами дат в Excel.

Вот некоторые из форматов:

8/26/2017
20170925
2017-08-10
43026
43040.46597

Мне нужно как-то получить все это в одном стандартном формате без отметки времени.

Из-за большого количества разных форматов я попытался проверить LEN() , чтобы сгруппировать форматы (хотя некоторые разные форматы также имеют одинаковую длину).

Я потратил буквальные часы, изучая несколько методов преобразования дат, и он прекрасно работает, чтобы получить правильный формат в новом столбце, но я не могу скопировать / вставить этот исправленный формат обратно в исходный столбец из-за фильтрации, которую мне нужно использовать ,

Выбор только видимых строк работает хорошо, но при вставке я все равно теряю много данных в скрытых строках, независимо от того, что я делаю.

Есть ли способ исправить это без необходимости проходить все 30 000 строк по одной за раз и копировать / вставлять или вручную вводить правильный формат даты?

Answer 1

Ваша настоящая проблема заключается в том, что вы можете переместить исправленные данные из одного столбца обратно в исходный столбец.

Один из способов обойти эту проблему копирования / вставки в отфильтрованных строках - это найти столбец с исправленными данными рядом с левым столбцом исходных данных.

Затем выберите диапазон значений в правильном столбце и выполните Ctrl+Shift+ стрелка вправо, чтобы выбрать соответствующие ячейки в исходном столбце, а затем Ctrl+ R.

Это скопирует данные из самых левых ячеек вашего выбора в остальные ячейки вашего выбора, не касаясь данных в невидимых строках.

Надеюсь это поможет!

Answer 2

Решение out -excel, использующее dateconv из dateutils:

$ dateconv -i %F -i %m/%d/%Y -i %Y%m%d <<EOF
8/26/2017
20170925
2017-08-10
EOF
=>
  2017-08-26
  2017-09-25
  2017-08-10

Идея состоит в том, чтобы указать все возможные форматы ввода, используя несколько флагов -i . Формат вывода по умолчанию: %F но его можно изменить с помощью -f .

Я не знаю, как преобразовать последние два числа, это не стандартная вещь дня (например, дни Матлаба с 0000-01-01 или номер дня в Юлиане или номер дня в Лилиане).