Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Замены значений в строке циклом (Файл более 4 млн. строк) - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Замены значений в строке циклом (Файл более 4 млн. строк) (Макросы/Sub)
Замены значений в строке циклом (Файл более 4 млн. строк)
monstr_ork Дата: Вторник, 02.07.2019, 15:34 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 124
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Привет, форумчане.

Помогите пожалуйста.
Началось все здесь Предыдущая тема.
Мне было необходимо в файле (более 4 млн строк), заменить значения на в строках по определенным условиям (что-то вроде ВПР).
Это удалось решить с помощью PQ, но оказалось что выгрузить потом данные ооочень проблематично из запроса, а на лист я их загрузить не могу (ограничение по строкам).
В прошлой теме рекомендовали сделать это через VBA, но я так и не нашел ни чего и еще больше запутался.
Помогите, пожалуйста, разобраться, может быть с кодом или с темой где можно почитать информацию по данному случаю. Буду очень признателен любой помощи.

Суть в следующем: есть файл файл на 4 млн. строк, 2 столбца. 1 стлобце цифровое значение, а вот во втором мне нужно заменить слова на другие значения.
Пример строки второго столбца "товар1: машина, качество1: хорошее, цена1: 123, товар2:мотоцикл, качество2: отличное, цена2: 233, ..."
Пример как должно получиться "товар1: a machine, качество1: good, цена1: 123, товар2:motorcycle, качество2: great, цена2: 233, товар3: scooter, качество3: bad, цена3: 123"
К сообщению приложен файл: 3730234.xlsm(14.3 Kb)


Сообщение отредактировал monstr_ork - Вторник, 02.07.2019, 17:35
 
Ответить
СообщениеПривет, форумчане.

Помогите пожалуйста.
Началось все здесь Предыдущая тема.
Мне было необходимо в файле (более 4 млн строк), заменить значения на в строках по определенным условиям (что-то вроде ВПР).
Это удалось решить с помощью PQ, но оказалось что выгрузить потом данные ооочень проблематично из запроса, а на лист я их загрузить не могу (ограничение по строкам).
В прошлой теме рекомендовали сделать это через VBA, но я так и не нашел ни чего и еще больше запутался.
Помогите, пожалуйста, разобраться, может быть с кодом или с темой где можно почитать информацию по данному случаю. Буду очень признателен любой помощи.

Суть в следующем: есть файл файл на 4 млн. строк, 2 столбца. 1 стлобце цифровое значение, а вот во втором мне нужно заменить слова на другие значения.
Пример строки второго столбца "товар1: машина, качество1: хорошее, цена1: 123, товар2:мотоцикл, качество2: отличное, цена2: 233, ..."
Пример как должно получиться "товар1: a machine, качество1: good, цена1: 123, товар2:motorcycle, качество2: great, цена2: 233, товар3: scooter, качество3: bad, цена3: 123"

Автор - monstr_ork
Дата добавления - 02.07.2019 в 15:34
Fidgy Дата: Вторник, 02.07.2019, 16:26 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
На скорую руку сделал через словарь - см. вложение

И замена слов чувствительна к регистру

UPD: есть ошибка - в слове "мотоцик_иж" заменилось только "мотоцикл" - тут нужно думать какой подход применить, для вычленения составных слов
Или тупо поменять местами в таблице подмены, позиции "мотоцикл_иж" и "мотоцикл"
К сообщению приложен файл: 6904656.xlsm(17.9 Kb)


Сообщение отредактировал Fidgy - Вторник, 02.07.2019, 16:37
 
Ответить
СообщениеНа скорую руку сделал через словарь - см. вложение

И замена слов чувствительна к регистру

UPD: есть ошибка - в слове "мотоцик_иж" заменилось только "мотоцикл" - тут нужно думать какой подход применить, для вычленения составных слов
Или тупо поменять местами в таблице подмены, позиции "мотоцикл_иж" и "мотоцикл"

Автор - Fidgy
Дата добавления - 02.07.2019 в 16:26
monstr_ork Дата: Вторник, 02.07.2019, 16:58 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 124
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Fidgy, как я видел решения (но не знал как все это оформить):
дробим сроку на отдельный фразы, ключом будет ":" и "," т.к. заменить нам нужно каждую вторую фразу, ее и искать в таблице значений.
Тем самым решаем проблему дробных слов.

И осталась главная проблема, с помощью чего это можно обработать? Через эксель я просто так его не открою.
Читал про то что VBA может работать построчно, но ни чего не понял :(


Сообщение отредактировал monstr_ork - Вторник, 02.07.2019, 17:00
 
Ответить
СообщениеFidgy, как я видел решения (но не знал как все это оформить):
дробим сроку на отдельный фразы, ключом будет ":" и "," т.к. заменить нам нужно каждую вторую фразу, ее и искать в таблице значений.
Тем самым решаем проблему дробных слов.

И осталась главная проблема, с помощью чего это можно обработать? Через эксель я просто так его не открою.
Читал про то что VBA может работать построчно, но ни чего не понял :(

Автор - monstr_ork
Дата добавления - 02.07.2019 в 16:58
Fidgy Дата: Вторник, 02.07.2019, 17:22 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
monstr_ork, я только сложный способ знаю
1) взять строку текста
2) Через функцию InStr найти позицию ":", если не найдено то берём 0. И найти позицию символа ",", если не найдено, то берём длину строки
3) Зная позиции этих символов, вытащить текст через функцию Mid
4) Найти полученный текст в таблице подмены, убрав пробелы с помощью функции Trim
5) С помощью функции Replace заменить найденное слово на нужное, указав параметр Start = номер позиции символа ":" +1
6) Повторить пункты 2 - 6, до тех пор, пока находим текст, только для функции InStr указываем параметр Start = номер позиции символа "," +1
7) перейти к следующей строке, выполнить пункт 2 - 6

Но по моему легче отсортировать таблицу с подменой, чтобы составное слово стояло выше несоставного (пример с мотоциклом) и воспользоваться процедурой которую я дал выше


Сообщение отредактировал Fidgy - Вторник, 02.07.2019, 17:25
 
Ответить
Сообщениеmonstr_ork, я только сложный способ знаю
1) взять строку текста
2) Через функцию InStr найти позицию ":", если не найдено то берём 0. И найти позицию символа ",", если не найдено, то берём длину строки
3) Зная позиции этих символов, вытащить текст через функцию Mid
4) Найти полученный текст в таблице подмены, убрав пробелы с помощью функции Trim
5) С помощью функции Replace заменить найденное слово на нужное, указав параметр Start = номер позиции символа ":" +1
6) Повторить пункты 2 - 6, до тех пор, пока находим текст, только для функции InStr указываем параметр Start = номер позиции символа "," +1
7) перейти к следующей строке, выполнить пункт 2 - 6

Но по моему легче отсортировать таблицу с подменой, чтобы составное слово стояло выше несоставного (пример с мотоциклом) и воспользоваться процедурой которую я дал выше

Автор - Fidgy
Дата добавления - 02.07.2019 в 17:22
Pelena Дата: Вторник, 02.07.2019, 17:24 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 14321
Репутация: 3131 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
monstr_ork, название темы очень общее, придумайте более конкретно, отражающее суть задачи. Тем более, что в первом посте это почти сформулировано
Исправлено


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениеmonstr_ork, название темы очень общее, придумайте более конкретно, отражающее суть задачи. Тем более, что в первом посте это почти сформулировано
Исправлено

Автор - Pelena
Дата добавления - 02.07.2019 в 17:24
monstr_ork Дата: Вторник, 02.07.2019, 17:39 | Сообщение № 6
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 124
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Fidgy, это все работает, основной вопрос у меня как это открыть?
Этот файл даже через текстовый редактор не открывается.
Как эти строки получать?

Pelena, поправил название темы


Сообщение отредактировал monstr_ork - Вторник, 02.07.2019, 17:39
 
Ответить
СообщениеFidgy, это все работает, основной вопрос у меня как это открыть?
Этот файл даже через текстовый редактор не открывается.
Как эти строки получать?

Pelena, поправил название темы

Автор - monstr_ork
Дата добавления - 02.07.2019 в 17:39
Fidgy Дата: Вторник, 02.07.2019, 17:57 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
monstr_ork,
аа, у вас текст в другом файле находится?
Это уже совсем другой вопрос
Я даже не уверен что Excel здесь подходит. Возможно как-то через Access этот фарш можно провернуть
В любом случае тут я не помогу(
 
Ответить
Сообщениеmonstr_ork,
аа, у вас текст в другом файле находится?
Это уже совсем другой вопрос
Я даже не уверен что Excel здесь подходит. Возможно как-то через Access этот фарш можно провернуть
В любом случае тут я не помогу(

Автор - Fidgy
Дата добавления - 02.07.2019 в 17:57
bmv98rus Дата: Вторник, 02.07.2019, 18:09 | Сообщение № 8
Группа: Проверенные
Ранг: Старожил
Сообщений: 2456
Репутация: 421 ±
Замечаний: 0% ±

Excel 2013/2016
Этот файл даже через текстовый редактор не открывается.

Возьмите другой текстовый редактор
В целом читать строку, обрабатывать, писать в другой файл, читать следующую строку до EOF.
Обработку можно и через SPLIT Делать через разделители указанные.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Этот файл даже через текстовый редактор не открывается.

Возьмите другой текстовый редактор
В целом читать строку, обрабатывать, писать в другой файл, читать следующую строку до EOF.
Обработку можно и через SPLIT Делать через разделители указанные.

Автор - bmv98rus
Дата добавления - 02.07.2019 в 18:09
monstr_ork Дата: Вторник, 02.07.2019, 18:24 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 124
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, может быть знаете где можно взять инфу, как это реализовать?
 
Ответить
Сообщениеbmv98rus, может быть знаете где можно взять инфу, как это реализовать?

Автор - monstr_ork
Дата добавления - 02.07.2019 в 18:24
bmv98rus Дата: Вторник, 02.07.2019, 18:49 | Сообщение № 10
Группа: Проверенные
Ранг: Старожил
Сообщений: 2456
Репутация: 421 ±
Замечаний: 0% ±

Excel 2013/2016
Файл строк на 20 исходный проиложите. Файл какой размер имеет?


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеФайл строк на 20 исходный проиложите. Файл какой размер имеет?

Автор - bmv98rus
Дата добавления - 02.07.2019 в 18:49
monstr_ork Дата: Вторник, 02.07.2019, 18:57 | Сообщение № 11
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 124
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, чистый исходник не смогу дать. Аналогичный ему приложил.
А вот вес файла порядка 4,5 Гб
 
Ответить
Сообщениеbmv98rus, чистый исходник не смогу дать. Аналогичный ему приложил.
А вот вес файла порядка 4,5 Гб

Автор - monstr_ork
Дата добавления - 02.07.2019 в 18:57
bmv98rus Дата: Вторник, 02.07.2019, 19:48 | Сообщение № 12
Группа: Проверенные
Ранг: Старожил
Сообщений: 2456
Репутация: 421 ±
Замечаний: 0% ±

Excel 2013/2016
Аналогичный ему приложил.
да наверно не совсем, исходный наверно все ж не XLSX(m) раз там 4мульта строк. 4,5 ГБ гипотетически можно загрузить целиком и разбить на строки в массив и с ним уже возиться, но это если система x64 и памяти не 4ГБ.
Делайте пример исходного файла, но так чтоб он был один в один с исходным, по формату данных.


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 02.07.2019, 20:01
 
Ответить
Сообщение
Аналогичный ему приложил.
да наверно не совсем, исходный наверно все ж не XLSX(m) раз там 4мульта строк. 4,5 ГБ гипотетически можно загрузить целиком и разбить на строки в массив и с ним уже возиться, но это если система x64 и памяти не 4ГБ.
Делайте пример исходного файла, но так чтоб он был один в один с исходным, по формату данных.

Автор - bmv98rus
Дата добавления - 02.07.2019 в 19:48
monstr_ork Дата: Среда, 03.07.2019, 08:06 | Сообщение № 13
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 124
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, исходник в CSV.
Исходные данные такие и есть.
В архиве 3 файл, 1 исходник и 2 таблицы, со значениями на которые нужно менять.

Видел статью, где применяли язык SQL для работы с файлами, может есть кто из форумчан, которые работали по такому принципу?
Как я понял, при выполнении кода с использованием SQL команд, файл не открывается, а строчку можно запихать в массив. Может можно обработать его?
К сообщению приложен файл: File.zip(0.6 Kb)


Сообщение отредактировал monstr_ork - Среда, 03.07.2019, 08:45
 
Ответить
Сообщениеbmv98rus, исходник в CSV.
Исходные данные такие и есть.
В архиве 3 файл, 1 исходник и 2 таблицы, со значениями на которые нужно менять.

Видел статью, где применяли язык SQL для работы с файлами, может есть кто из форумчан, которые работали по такому принципу?
Как я понял, при выполнении кода с использованием SQL команд, файл не открывается, а строчку можно запихать в массив. Может можно обработать его?

Автор - monstr_ork
Дата добавления - 03.07.2019 в 08:06
bmv98rus Дата: Среда, 03.07.2019, 09:23 | Сообщение № 14
Группа: Проверенные
Ранг: Старожил
Сообщений: 2456
Репутация: 421 ±
Замечаний: 0% ±

Excel 2013/2016
Уточнение, список замен переменный или это все? Вопрос к тому, что проще не искать что есть в категориях и менять а заменять если есть по списку.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеУточнение, список замен переменный или это все? Вопрос к тому, что проще не искать что есть в категориях и менять а заменять если есть по списку.

Автор - bmv98rus
Дата добавления - 03.07.2019 в 09:23
monstr_ork Дата: Среда, 03.07.2019, 09:38 | Сообщение № 15
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 124
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, список будет дополняться и изменяться, единственное что, их всегда будет два: товар и качество.
 
Ответить
Сообщениеbmv98rus, список будет дополняться и изменяться, единственное что, их всегда будет два: товар и качество.

Автор - monstr_ork
Дата добавления - 03.07.2019 в 09:38
bmv98rus Дата: Среда, 03.07.2019, 10:32 | Сообщение № 16
Группа: Проверенные
Ранг: Старожил
Сообщений: 2456
Репутация: 421 ±
Замечаний: 0% ±

Excel 2013/2016
ну в общих чертах для работы с файлом и при условии что сможет загрузится, загрузка словаря и прочее это отдельно


Результирующий фал в приложении.

Если в память не загрузить весь файл (под рукой сейчас Excel x32 и я сделал пример на 4 лимона строк, естественно не загрузил) , то иначе и медленнее будет из-за Cr в качетсве разделителя строк. посимвольно читать придется

Тестовый файл молотил 20мин :-)
А вот первый вариант, запустил в режиме VBS и там 40сек длилась загрузка , при преобразовании в массив память скаканула к 3ГБ (файл тестовый 578MB) ,
Start: 15:29:48
Start Load file: 15:29:48
Start Split: 15:30:26
Start Process: 15:31:40
Start Write File: 15:35:04
Finish: 15:35:15
Rows processed: 4194305

Fidgy, я посомтрел что у Dас было, а зачем был нужен в вашем варианте словарь? При таком алгоритме просто перебор массива даст тот же эффект.
К сообщению приложен файл: test_out.csv(0.5 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Среда, 03.07.2019, 15:59
 
Ответить
Сообщениену в общих чертах для работы с файлом и при условии что сможет загрузится, загрузка словаря и прочее это отдельно


Результирующий фал в приложении.

Если в память не загрузить весь файл (под рукой сейчас Excel x32 и я сделал пример на 4 лимона строк, естественно не загрузил) , то иначе и медленнее будет из-за Cr в качетсве разделителя строк. посимвольно читать придется

Тестовый файл молотил 20мин :-)
А вот первый вариант, запустил в режиме VBS и там 40сек длилась загрузка , при преобразовании в массив память скаканула к 3ГБ (файл тестовый 578MB) ,
Start: 15:29:48
Start Load file: 15:29:48
Start Split: 15:30:26
Start Process: 15:31:40
Start Write File: 15:35:04
Finish: 15:35:15
Rows processed: 4194305

Fidgy, я посомтрел что у Dас было, а зачем был нужен в вашем варианте словарь? При таком алгоритме просто перебор массива даст тот же эффект.

Автор - bmv98rus
Дата добавления - 03.07.2019 в 10:32
monstr_ork Дата: Среда, 03.07.2019, 16:11 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 124
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, можно вместо objDict использовать файл с уже известными данными. которые нужно заменить?
По поводу открытия, notepad тоже его не смог открыть.
я не могу открыть данный файл стандартными программами, поэтому смотрю в сторону sql запроса. Он вроде бы не открывается файл, а только создает подключение, потом каждую строку в массив и ее уже обрабатывать макросом.


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


Сообщение отредактировал monstr_ork - Среда, 03.07.2019, 16:12
 
Ответить
Сообщениеbmv98rus, можно вместо objDict использовать файл с уже известными данными. которые нужно заменить?
По поводу открытия, notepad тоже его не смог открыть.
я не могу открыть данный файл стандартными программами, поэтому смотрю в сторону sql запроса. Он вроде бы не открывается файл, а только создает подключение, потом каждую строку в массив и ее уже обрабатывать макросом.


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

Автор - monstr_ork
Дата добавления - 03.07.2019 в 16:11
Fidgy Дата: Среда, 03.07.2019, 16:24 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 33
Репутация: 8 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, да, не нужен словарь
Я сначала думал выбирать слова из строки и через словарь получать их подмену - тогда это было бы быстрее, так-как количество слов для подмены может быть значительно больше
Но потом решил что легче просто перебирать все варианты слов, а переделывать на массив уже было лень
 
Ответить
Сообщениеbmv98rus, да, не нужен словарь
Я сначала думал выбирать слова из строки и через словарь получать их подмену - тогда это было бы быстрее, так-как количество слов для подмены может быть значительно больше
Но потом решил что легче просто перебирать все варианты слов, а переделывать на массив уже было лень

Автор - Fidgy
Дата добавления - 03.07.2019 в 16:24
bmv98rus Дата: Среда, 03.07.2019, 16:33 | Сообщение № 19
Группа: Проверенные
Ранг: Старожил
Сообщений: 2456
Репутация: 421 ±
Замечаний: 0% ±

Excel 2013/2016
можно вместо objDict использовать файл с уже известными данными

Я ж писал
загрузка словаря и прочее это отдельно

notepad тоже его не смог открыть.

попробуйте Notepad++, то скорее всего тоже не прокатит.
Он вроде бы не открывается файл
невозможно обработать файл не открывая его. Другое дело, что обработку можно вести по разному.

В целом сделать VBS на вход в качестве аргументов которому подавать три файла. Исходный и два с терминами для перевода - не проблема. но мне не очень интересно.


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
можно вместо objDict использовать файл с уже известными данными

Я ж писал
загрузка словаря и прочее это отдельно

notepad тоже его не смог открыть.

попробуйте Notepad++, то скорее всего тоже не прокатит.
Он вроде бы не открывается файл
невозможно обработать файл не открывая его. Другое дело, что обработку можно вести по разному.

В целом сделать VBS на вход в качестве аргументов которому подавать три файла. Исходный и два с терминами для перевода - не проблема. но мне не очень интересно.

Автор - bmv98rus
Дата добавления - 03.07.2019 в 16:33
anvg Дата: Среда, 03.07.2019, 20:38 | Сообщение № 20
Группа: Друзья
Ранг: Обитатель
Сообщений: 489
Репутация: 236 ±
Замечаний: 0% ±

2016, 365
Rows processed: 4194305

Привет, Михаил.
Что-то долго на таком алгоритме. У меня ровно для 4000000 строк вышла 101 секунда.
Результирующий файл прилагаю.
P. S. Если использовать построчное чтение/запись pStream.ReadLine/WriteLine, то выходит 179 секунд. Диск SSD.
К сообщению приложен файл: test4kk_en.7z(70.2 Kb)


Сообщение отредактировал anvg - Среда, 03.07.2019, 20:39
 
Ответить
Сообщение
Rows processed: 4194305

Привет, Михаил.
Что-то долго на таком алгоритме. У меня ровно для 4000000 строк вышла 101 секунда.
Результирующий файл прилагаю.
P. S. Если использовать построчное чтение/запись pStream.ReadLine/WriteLine, то выходит 179 секунд. Диск SSD.

Автор - anvg
Дата добавления - 03.07.2019 в 20:38
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Замены значений в строке циклом (Файл более 4 млн. строк) (Макросы/Sub)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

Яндекс цитирования
© 2010-2019 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!