Даты, которые при копировании в Excel из файла csv, форматируются как текст

@shavka · Регистрация: 27.11.2013

Author24 — интернет-сервис помощи студентам

Проблема такая. В папке есть энное кол-во файлов долбаного формата csv. Они открываются циклом, потому как я не знаю их кол-ва и имени. Код такой

Visual Basic

1	Application.Workbooks.Open ("D:\ТекГод\ФОРТС\" & s)

где s - переменная, обозначающая имя файла.
Далее из него в лист Exel копируются 2 столбца.
Хрень в том, что один из столбцов - даты, которые при копировании в Exel форматируются как текст, поэтому работать с ними далее невозможно. Если все делать ручками, то все элементарно. Либо при операции "Текст по столбцам" столбик с датами форматируется как даты, либо в операции "специальная вставка" этот долбаный столбик умножаем на 1. В коде VBA никто не предлагает мне указать формат столбика как даты, а специальная вставка не работает. Если записать в макрос эту специальную вставку, макрос она запишет, но работать как спец. вставка макрос не будет. Что делать?!!!!!!

@Казанский · 11.05.2015, 00:02

Сообщение от shavka

Что делать?!

Приложить 2-3 файла csv и показать, что из них должно получиться.

@Alex77755 · 11.05.2015, 06:58

которые при копировании в Exel форматируются как текст

Не много не так:
копируются так, как указано в коде. Никто не мешает форматировать переносимые значения и кодом макроса

@pashulka · 11.05.2015, 09:40

Сообщение от shavka

В коде VBA никто не предлагает мне указать формат столбика как даты

Используйте Workbooks.OpenText , там такая возможность наличествует.

@shavka · 11.05.2015, 12:24 **[ТС]**

xlsx файл - это куда вставляется, а в csv-файлах пришлось поменять расширение на txt, тут csv-формат загружать нельзя. С txt проблем нет - в прошлом году были txt, и все было ОК, код работал как зверь.

@shavka · 11.05.2015, 12:26 **[ТС]**

Сообщение от pashulka

Используйте Workbooks.OpenText , там такая возможность наличествует.

Хрень в том, что в этом случае я не знаю, как прицепить в этот оператор переменную имени файла. Файлов много, и открываются они из папки циклом.

@Hugo121 · 11.05.2015, 13:13

Сообщение от shavka

я не знаю, как прицепить в этот оператор переменную имени файла

Да точно так же.

Добавлено через 1 минуту
Вот пример кода - начало:

Visual Basic

Sub Mac1()
s1 = "C:\result\"
For i = 1 To n
s2 = "result" + LTrim(Str(i)) + ".txt"
Workbooks.OpenText Filename:=s1 + s2, _
Origin:=866, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1)), TrailingMinusNumbers:=True
 
Next i
For i = 2 To n

@shavka · 11.05.2015, 19:41 **[ТС]**

Люди, я фигею. Сделал через Workbooks.Open Text..... В FieldInfo в первом столбце поставил 4 (дата). Ни х... чего. Тот же результат. Самое обидное, то что руками это делается на 1-2-3, VBA не делает никак. Придеца код делить на две части. В первой части редактировать руками эти долбаные даты.

@Alex77755 · 11.05.2015, 20:12

VBA не делает никак

НЕ! VBA делает на раз!
csv-файлы это текстовые файлы. И их вполне можно открывать старыми способами бейсика и ибрабатывать в памяти в массивах.
Например так:

Visual Basic

1	a=Split(CreateObject("Scripting.FileSystemObject").Getfile(Name).OpenasTextStream(1).ReadAll, vbNewLine)

И получаем массив строк
Потом построчно обрабатываем

Добавлено через 2 минуты
Судя по файлу потом Split по ";"

Добавлено через 2 минуты

Хрень в том, что в этом случае я не знаю, как прицепить в этот оператор переменную имени файла

Да не нужно знать имя файла! Программа сама определит его!
Есть куча тем типа: перебрать файлы в папке

Добавлено через 1 минуту
Сек. Нацарапаю пример

@pashulka · 11.05.2015, 20:27

Excel XP (и старше) FieldInfo:=Array(1, 4), Local:=True

Excel 2000 Date field data is incorrect if you use Open or OpenText to open a.csv file in Excel 2000

@Hugo121 · 11.05.2015, 21:25

Split по ";" не всегда подходит - бывает что эти разделители присутствуют внутри значения столбца. Но редко.
Но вот в этом файле в заголовке как раз такой случай...

@shavka · 13.05.2015, 00:57 **[ТС]**

Люди, я разобрался, в чем проблема. В некоторых файлах в столбиках, где цифры (не даты, даты только в первом столбце) есть дробные числа, где есть запятая. Т.е если в правильных файлах все цифры целые, то в этих файлах есть, например, 2710,5. Патаму комп с ума и сходит и пишет всякую ересь. С этим бороться видимо, невозможно. Спасибо всем за помочь. Узнал много полезного, пригадицца.

@Hugo121 · 13.05.2015, 08:23

Там в мастере импорта файлов ведь есть выбор разделителя - так что бороться можно.

Добавлено через 2 минуты
Хотя смотрю приложенный текст - там всюду десятичный разделитель точка, откуда там возьмутся числа с запятой?

@shavka · 13.05.2015, 17:31 **[ТС]**

Если руками открыть файл золотишка (GDU5) - там переведенный в рубли долларовый курс, там появляются дробные числа. Странно, но если открывать руками, все правильно открывается, только даты в текстовом формате, что убирается сотней способов. И все эти способы не работают в VBA. Например, умножение на 1 (PasteSpecial), замена точки на / и т.д. Можно кодом VBA открыть файл csv без разделения на столбцы, а потом кодом же разделить на столбцы с указанием всех параметров - ошибается, собака!

@Hugo121 · 13.05.2015, 18:03

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

@Alex77755 · 13.05.2015, 20:03

Обещал набросать, да замотался..
Примерно так:

@shavka · 16.05.2015, 17:10 **[ТС]**

Все перепробовал, наиболее устойчивая конструкция такая: в цикле открываю файлы с помощью OpenText, при этом во всех настройках ставлю False, т.е. фактически сбрасываю все настройки:

PureBasic

Workbooks.OpenText Filename:="D:\Путь\" & s, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=, _
Comma:=False, Space:=False, Local:=False, Other:=False, TrailingMinusNumbers:=True

А затем просто имитирую операцию "разделить по столбцам" - все форматируется как надо. Настройки такие:

PureBasic

Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
        ), Array(14, 1)), TrailingMinusNumbers:=True

В принципе, можно в FieldInfo отформатировать только тот столбец, который нужен, но для верности как раз лучше указать формат всех столбцов. Это конечно непрофессиональное решение, но заработало железно. Тем более что эти проклятые даты форматируются так и только так. Во всех остальных методах они упроно прикидываются текстом.

@Казанский 15145 / 6418 / 1731 Регистрация: 24.09.2011 Сообщений: 9,999
	11.05.2015, 00:02	2
	Сообщение от shavka Что делать?! Приложить 2-3 файла csv и показать, что из них должно получиться. 1

@Alex77755 11508 / 3794 / 681 Регистрация: 13.02.2009 Сообщений: 11,197
	11.05.2015, 06:58	3
	которые при копировании в Exel форматируются как текст Не много не так: копируются так, как указано в коде. Никто не мешает форматировать переносимые значения и кодом макроса 1

@pashulka 4134 / 2238 / 940 Регистрация: 01.12.2010 Сообщений: 4,624
	11.05.2015, 09:40	4
	Сообщение от shavka В коде VBA никто не предлагает мне указать формат столбика как даты Используйте Workbooks.OpenText , там такая возможность наличествует. 1

@shavka 132 / 15 / 2 Регистрация: 27.11.2013 Сообщений: 509
	11.05.2015, 12:26 [ТС]	6
	Сообщение от pashulka Используйте Workbooks.OpenText , там такая возможность наличествует. Хрень в том, что в этом случае я не знаю, как прицепить в этот оператор переменную имени файла. Файлов много, и открываются они из папки циклом. 0

@shavka 132 / 15 / 2 Регистрация: 27.11.2013 Сообщений: 509
	11.05.2015, 19:41 [ТС]	8
	Люди, я фигею. Сделал через Workbooks.Open Text..... В FieldInfo в первом столбце поставил 4 (дата). Ни х... чего. Тот же результат. Самое обидное, то что руками это делается на 1-2-3, VBA не делает никак. Придеца код делить на две части. В первой части редактировать руками эти долбаные даты. 0

@pashulka 4134 / 2238 / 940 Регистрация: 01.12.2010 Сообщений: 4,624
	11.05.2015, 20:27	10
	Excel XP (и старше) FieldInfo:=Array(1, 4), Local:=True Excel 2000 Date field data is incorrect if you use Open or OpenText to open a.csv file in Excel 2000 1

@Hugo121 6922 / 2832 / 543 Регистрация: 19.10.2012 Сообщений: 8,645
	11.05.2015, 21:25	11
	Split по ";" не всегда подходит - бывает что эти разделители присутствуют внутри значения столбца. Но редко. Но вот в этом файле в заголовке как раз такой случай... 1

@shavka 132 / 15 / 2 Регистрация: 27.11.2013 Сообщений: 509
	13.05.2015, 00:57 [ТС]	12
	Люди, я разобрался, в чем проблема. В некоторых файлах в столбиках, где цифры (не даты, даты только в первом столбце) есть дробные числа, где есть запятая. Т.е если в правильных файлах все цифры целые, то в этих файлах есть, например, 2710,5. Патаму комп с ума и сходит и пишет всякую ересь. С этим бороться видимо, невозможно. Спасибо всем за помочь. Узнал много полезного, пригадицца. 0

@Hugo121 6922 / 2832 / 543 Регистрация: 19.10.2012 Сообщений: 8,645
	13.05.2015, 08:23	13
	Там в мастере импорта файлов ведь есть выбор разделителя - так что бороться можно. Добавлено через 2 минуты Хотя смотрю приложенный текст - там всюду десятичный разделитель точка, откуда там возьмутся числа с запятой? 1

@shavka 132 / 15 / 2 Регистрация: 27.11.2013 Сообщений: 509
	13.05.2015, 17:31 [ТС]	14
	Если руками открыть файл золотишка (GDU5) - там переведенный в рубли долларовый курс, там появляются дробные числа. Странно, но если открывать руками, все правильно открывается, только даты в текстовом формате, что убирается сотней способов. И все эти способы не работают в VBA. Например, умножение на 1 (PasteSpecial), замена точки на / и т.д. Можно кодом VBA открыть файл csv без разделения на столбцы, а потом кодом же разделить на столбцы с указанием всех параметров - ошибается, собака! 0

@Hugo121 6922 / 2832 / 543 Регистрация: 19.10.2012 Сообщений: 8,645
	13.05.2015, 18:03	15
	А, так там разделитель полей другой, нет проблем в мастере задать десятичный разделитель. Вообще конечно бардак что такая чехарда с разделителями полей - но можно кодом посмотреть что за пятый символ, и соотв. изменить дальнейший код. 1

Даты, которые при копировании в Excel из файла csv, форматируются как текст

Решение