SvetaS, Выложу пример загрузки простым открытием файла в памяти (сделал под Ваш файл 7050606.xlsx, нужно поместить два файла в одну папку) У меня спокойно загружается размер 100000+ строк в массив за пару секунд Также можно не выгружать в массив, а работать напрямую с листом данных
SvetaS, Выложу пример загрузки простым открытием файла в памяти (сделал под Ваш файл 7050606.xlsx, нужно поместить два файла в одну папку) У меня спокойно загружается размер 100000+ строк в массив за пару секунд Также можно не выгружать в массив, а работать напрямую с листом данныхmiver
SvetaS - по поводу остальных пунктов - много слов в посте - а примера ИТОГОВОГО разбора и присвоения - нет. Сделайте пример - повыделяйте цветом что добавили и куда... т.е. какие столбцы добавлены к первоначальному файлу, какие данные в него внесены из "справочника"(2-го файла) и в примечаниях кратко опишите откуда тянуть данные и условия.
Зы и еще зачем 3-и поля на дату? достаточно просто даты - а месяц и год - потом легко вычисляются.... Может какие-то столбцы вообще не нужны?
SvetaS - по поводу остальных пунктов - много слов в посте - а примера ИТОГОВОГО разбора и присвоения - нет. Сделайте пример - повыделяйте цветом что добавили и куда... т.е. какие столбцы добавлены к первоначальному файлу, какие данные в него внесены из "справочника"(2-го файла) и в примечаниях кратко опишите откуда тянуть данные и условия.
Зы и еще зачем 3-и поля на дату? достаточно просто даты - а месяц и год - потом легко вычисляются.... Может какие-то столбцы вообще не нужны?SLAVICK
Иногда все проще чем кажется с первого взгляда.
Сообщение отредактировал SLAVICK - Четверг, 17.09.2015, 09:51
SLAVICK, огромное СПАСИБО. ЭТО САМАЯ ЗАМЕЧАТЕЛЬНАЯ ИДЕЯ , добавлять новые строки не в массив , а в вспомогательный файл. Подскажите, только, пожалуйста: если заменить строку [vba]
Код
mF = [a1].CurrentRegion
[/vba] на строку [vba]
Код
Dim ADO As New ADO Dim mF As Variant ADO.DataSource = "G:\РАЗРАБОТКА_2\данно" & "\" & "exim_ua_2015_1-8_рыб.xls" ' полный путь к книге ADO.Query ("SELECT * FROM [exim_ua_2015_1_8_рыб$]") mF = ADO.ToArray() ' записать результат выполнения запроса в массив
[/vba]
как тогда изменить модуль?
[vba]
Код
n = FreeFile() 'функция FreeFile возвращает целое число, представляющее номер файла, доступный для использования в выражении Open. On Error Resume Next If Filename = "" Then Filename = "G:\РАЗРАБОТКА_2" & "\file.csv" Open Filename For Append As #n
For i = 1 To UBound(mF) 'служит для определения верхней границы (индекса самого последнего элемента) массива по заданному измерению If InStr(1, mF(i, 16), ";") > 0 Then mt = Split(mF(i, 16), ";") ReDim m(1 To UBound(mt) + 1, 1 To 34) For ii = 1 To 34 If ii = 16 Then For iii = 0 To UBound(mt) m(iii + 1, ii) = mt(iii) Next Else For iii = 0 To UBound(mt) m(iii + 1, ii) = mF(i, ii) Next End If Next Else ReDim m(1 To 1, 1 To 34) For ii = 1 To 34 m(1, ii) = mF(i, ii) Next End If txt_write_from_Mas m, n If i Mod 100 = 0 Then ' Mod - оператор делит число на 100 и возвращает остаток DoEvents 'прерывание выполнения бесконеного цикла с помощью комбинацци клавиш Ctrl+Break ' передача управления другим процессам Application.StatusBar = "Обработка записи " & Format(i / UBound(mF), "0%") & ". " & i & " из " & UBound(mF) 'отображаем в строке состояния какую запись обрабатываем End If Next Application.StatusBar = False ' обнуляем строку состояния Close #n
ReDim mfull(1 To nM, 1 To 34) TXT_ToMas mfull, Filename$ ' перегон текста в массив
Sheets.Add [a1].Resize(UBound(mfull), UBound(mfull, 2)) = mfull ' выгрузка массива на лист End Sub
[/vba]
SLAVICK, огромное СПАСИБО. ЭТО САМАЯ ЗАМЕЧАТЕЛЬНАЯ ИДЕЯ , добавлять новые строки не в массив , а в вспомогательный файл. Подскажите, только, пожалуйста: если заменить строку [vba]
Код
mF = [a1].CurrentRegion
[/vba] на строку [vba]
Код
Dim ADO As New ADO Dim mF As Variant ADO.DataSource = "G:\РАЗРАБОТКА_2\данно" & "\" & "exim_ua_2015_1-8_рыб.xls" ' полный путь к книге ADO.Query ("SELECT * FROM [exim_ua_2015_1_8_рыб$]") mF = ADO.ToArray() ' записать результат выполнения запроса в массив
[/vba]
как тогда изменить модуль?
[vba]
Код
n = FreeFile() 'функция FreeFile возвращает целое число, представляющее номер файла, доступный для использования в выражении Open. On Error Resume Next If Filename = "" Then Filename = "G:\РАЗРАБОТКА_2" & "\file.csv" Open Filename For Append As #n
For i = 1 To UBound(mF) 'служит для определения верхней границы (индекса самого последнего элемента) массива по заданному измерению If InStr(1, mF(i, 16), ";") > 0 Then mt = Split(mF(i, 16), ";") ReDim m(1 To UBound(mt) + 1, 1 To 34) For ii = 1 To 34 If ii = 16 Then For iii = 0 To UBound(mt) m(iii + 1, ii) = mt(iii) Next Else For iii = 0 To UBound(mt) m(iii + 1, ii) = mF(i, ii) Next End If Next Else ReDim m(1 To 1, 1 To 34) For ii = 1 To 34 m(1, ii) = mF(i, ii) Next End If txt_write_from_Mas m, n If i Mod 100 = 0 Then ' Mod - оператор делит число на 100 и возвращает остаток DoEvents 'прерывание выполнения бесконеного цикла с помощью комбинацци клавиш Ctrl+Break ' передача управления другим процессам Application.StatusBar = "Обработка записи " & Format(i / UBound(mF), "0%") & ". " & i & " из " & UBound(mF) 'отображаем в строке состояния какую запись обрабатываем End If Next Application.StatusBar = False ' обнуляем строку состояния Close #n
, можно ли как-то уйти от объявления Const BUFSIZE As Long = 100000
Это не количество строк - а размер буфера - к количеству строк не имеет ни какого отношения - ссылку на эту процедуру я уже скидывал - почитайте внимательно:
Цитата
Given a 7,000,000 line file of 293MB it only takes 0.7 seconds here.
, можно ли как-то уйти от объявления Const BUFSIZE As Long = 100000
Это не количество строк - а размер буфера - к количеству строк не имеет ни какого отношения - ссылку на эту процедуру я уже скидывал - почитайте внимательно:
Цитата
Given a 7,000,000 line file of 293MB it only takes 0.7 seconds here.
SLAVICK, ОГРОМНОЕ СПАСИБО. :hands::flowers: Можно только спросить - почему в макросе выгружается только в одну накопительную переменную ( при выгрузки в файл ) . А можно -ли как-то сделать чтобы выгружался массив по столбикам (чтобы было быстро). И как тогда это повлияет на быстрый подсчёт строк?
SLAVICK, ОГРОМНОЕ СПАСИБО. :hands::flowers: Можно только спросить - почему в макросе выгружается только в одну накопительную переменную ( при выгрузки в файл ) . А можно -ли как-то сделать чтобы выгружался массив по столбикам (чтобы было быстро). И как тогда это повлияет на быстрый подсчёт строк?SvetaS
Можно только спросить - почему в макросе выгружается только в одну накопительную переменную ( при выгрузки в файл )
При выгрузке в файл, т.е. при дописывании данных в текст. файл - передается промежуточный массив - а в самой процедуре запись идет построчно, для этого данные из этого промежуточного массива собираются в одну текст. переменную и потом записываются в текст.файл записывать по столбикам в текстовом формате нельзя.
И как тогда это повлияет на быстрый подсчёт строк?
запись в текст. файл никак не связана с просчетом строк. В принципе от процедуры подсчета строк можно вообще отказаться - для этого достаточно ввести отдельную накопительную переменную.
Можно только спросить - почему в макросе выгружается только в одну накопительную переменную ( при выгрузки в файл )
При выгрузке в файл, т.е. при дописывании данных в текст. файл - передается промежуточный массив - а в самой процедуре запись идет построчно, для этого данные из этого промежуточного массива собираются в одну текст. переменную и потом записываются в текст.файл записывать по столбикам в текстовом формате нельзя.
И как тогда это повлияет на быстрый подсчёт строк?
запись в текст. файл никак не связана с просчетом строк. В принципе от процедуры подсчета строк можно вообще отказаться - для этого достаточно ввести отдельную накопительную переменную. SLAVICK
SLAVICK, просто мне подсказали, в виду большого количества строк, во время обработки записывать сразу данные в накопительный массив (как накопительный регистр в 1С).Вот я и думаю как тогда в этом случае эту идею совместить с Вашей. Т.Е. записывать сразу не в текстовую переменную, а после разложения строк ";" , сразу их проверять с помощью нечёткого поиска на наличие слов из справочника и сразу записывать по колонкам в нужный "массив- файл", но так чтобы это было быстро (). Чтобы опять не ставить Redim ... Это было бы идеальным решением....... Простоя я знаю, что если построчно обращаться к каждой строке bp 300 000 excel, записывая туда данные, то он просто зависнет. Как думаете, как это лучше тогда сделать?
SLAVICK, просто мне подсказали, в виду большого количества строк, во время обработки записывать сразу данные в накопительный массив (как накопительный регистр в 1С).Вот я и думаю как тогда в этом случае эту идею совместить с Вашей. Т.Е. записывать сразу не в текстовую переменную, а после разложения строк ";" , сразу их проверять с помощью нечёткого поиска на наличие слов из справочника и сразу записывать по колонкам в нужный "массив- файл", но так чтобы это было быстро (). Чтобы опять не ставить Redim ... Это было бы идеальным решением....... Простоя я знаю, что если построчно обращаться к каждой строке bp 300 000 excel, записывая туда данные, то он просто зависнет. Как думаете, как это лучше тогда сделать?SvetaS
SLAVICK, да но как? , тогда, чтобы их вынуть - там нет колонок? какой смыл? - получается каждый раз вынимать - только при помощи Split каждой конкретной строки, а их 300 000 . Я правда не понимаю.....нужен же массив в конечном виде в xls... Я не понимаю, правда..... По времени сплит текста такая же долгая, как и вставка в ячейки массива?
SLAVICK, да но как? , тогда, чтобы их вынуть - там нет колонок? какой смыл? - получается каждый раз вынимать - только при помощи Split каждой конкретной строки, а их 300 000 . Я правда не понимаю.....нужен же массив в конечном виде в xls... Я не понимаю, правда..... По времени сплит текста такая же долгая, как и вставка в ячейки массива?SvetaS
по данным массива m и вызывайте ее непосредственно перед вызовом процедуры записи промежуточного массива:
В массиве m - 34 колонки [vba]
Код
ReDim m(1 To UBound(mt) + 1, 1 To 34)
[/vba] процедура txt_write_from_Mas записывает этот массив в ТХТ файл. Вам нужно свою процедуру проверки и поиска вставлять до записи этого массива в текст. См. файл добавил вызов процедуры txtLOOP [vba]
Код
m = txtLOOP(m) 'Запуск процедуры проверки
[/vba]
Во внутрь этой процедуры и вставьте проверку данных промежуточного массива.
по данным массива m и вызывайте ее непосредственно перед вызовом процедуры записи промежуточного массива:
В массиве m - 34 колонки [vba]
Код
ReDim m(1 To UBound(mt) + 1, 1 To 34)
[/vba] процедура txt_write_from_Mas записывает этот массив в ТХТ файл. Вам нужно свою процедуру проверки и поиска вставлять до записи этого массива в текст. См. файл добавил вызов процедуры txtLOOP [vba]
Код
m = txtLOOP(m) 'Запуск процедуры проверки
[/vba]
Во внутрь этой процедуры и вставьте проверку данных промежуточного массива.SLAVICK
SLAVICK, Огромное СПАСИБО. Извините, вопрос был немного в другом. Массив использовался – как временноехранилище данных Вопрос был – «Как вместо массива использовать файл без колонок?» Т.е. если мы кладём в файл всё одной строкой S (где все столбики через & ) – Вы показали как. То, наверное, нужно уметь и вытащить из файла обратно (мне кажется логичным). Вытаскивать придётся через Split, так как изначально в файле нетколонок. Т.Е. Я ПОНИМАЮ, ЧТО , НАВЕРНОЕ, НЕ СРАБОТАЕТ, [vba]
Код
Dim mF As Variant ADO.DataSource = "G:\РАЗРАБОТКА_2\данно" & "\" & "exim_ua_2015_1-8_рыб.xls" ' полный путь к книге ADO.Query ("SELECT * FROM [exim_ua_2015_1_8_рыб$]") mF = ADO.[b]ToArray()[/b] ' записать результат выполнения запроса в массив
[/vba]
Вопрос был второй- «как вытащить корректно данные 300 000 строк (чтобы было быстро)»
SLAVICK, Огромное СПАСИБО. Извините, вопрос был немного в другом. Массив использовался – как временноехранилище данных Вопрос был – «Как вместо массива использовать файл без колонок?» Т.е. если мы кладём в файл всё одной строкой S (где все столбики через & ) – Вы показали как. То, наверное, нужно уметь и вытащить из файла обратно (мне кажется логичным). Вытаскивать придётся через Split, так как изначально в файле нетколонок. Т.Е. Я ПОНИМАЮ, ЧТО , НАВЕРНОЕ, НЕ СРАБОТАЕТ, [vba]
Код
Dim mF As Variant ADO.DataSource = "G:\РАЗРАБОТКА_2\данно" & "\" & "exim_ua_2015_1-8_рыб.xls" ' полный путь к книге ADO.Query ("SELECT * FROM [exim_ua_2015_1_8_рыб$]") mF = ADO.[b]ToArray()[/b] ' записать результат выполнения запроса в массив
[/vba]
Вопрос был второй- «как вытащить корректно данные 300 000 строк (чтобы было быстро)»SvetaS
Вытаскивать придётся через Split, так как изначально в файле нет колонок.
Не обязательно через split. По сути файл СSV - это итоговая база данных в текстовом формате с разделителем VbTab(символ табуляции) - это символ обозначающий новую колонку. Такие файлы можно импортировать в другие базы как-то: SQL Access ... Указав что это импорт из текстового файла с разделителем VbTab(символ табуляции).
Т.Е. Я ПОНИМАЮ, ЧТО , НАВЕРНОЕ, НЕ СРАБОТАЕТ, Dim mF As Variant ADO.DataSource = "G:\РАЗРАБОТКА_2\данно" & "\" & "exim_ua_2015_1-8_рыб.xls" ' полный путь к книге
Правильно понимаете - это процедура импорта из .xls файлов. У меня в файле импорт в массив - это процедура TXT_ToMas Я же написал:
Последние два пункта - при большом количестве строк могут быть недоступны - у меня из 100 000 стало 1,2млн, поэтому я бы делал подключение к CSV, поскольку у Вас 2013-й офис - Вам это трудности не составит.
Даже в новом формате excel - всего 1048576 строк - при 300 000 начальных данных может получится - больше 3млн строк - при таком раскладе на лист данные никак не влезут. Поэтому и нужно внешнее подключение к CSV файлу. На основании этого подключения можно создать сводные таблицы - и работать с ними.
Вытаскивать придётся через Split, так как изначально в файле нет колонок.
Не обязательно через split. По сути файл СSV - это итоговая база данных в текстовом формате с разделителем VbTab(символ табуляции) - это символ обозначающий новую колонку. Такие файлы можно импортировать в другие базы как-то: SQL Access ... Указав что это импорт из текстового файла с разделителем VbTab(символ табуляции).
Т.Е. Я ПОНИМАЮ, ЧТО , НАВЕРНОЕ, НЕ СРАБОТАЕТ, Dim mF As Variant ADO.DataSource = "G:\РАЗРАБОТКА_2\данно" & "\" & "exim_ua_2015_1-8_рыб.xls" ' полный путь к книге
Правильно понимаете - это процедура импорта из .xls файлов. У меня в файле импорт в массив - это процедура TXT_ToMas Я же написал:
Последние два пункта - при большом количестве строк могут быть недоступны - у меня из 100 000 стало 1,2млн, поэтому я бы делал подключение к CSV, поскольку у Вас 2013-й офис - Вам это трудности не составит.
Даже в новом формате excel - всего 1048576 строк - при 300 000 начальных данных может получится - больше 3млн строк - при таком раскладе на лист данные никак не влезут. Поэтому и нужно внешнее подключение к CSV файлу. На основании этого подключения можно создать сводные таблицы - и работать с ними.SLAVICK
да ладно... Впервые слышу, ни разу не встречал CSV с разделителем VbTab.
Ну тогда файл, который создается благодаря моему макросу из 31-го поста будет для Вас открытием А если серьезно, то даже в приведенной Вами статье есть:
Цитата
Однако, большинство программ вольно трактует стандарт CSV и допускают использование иных символов в качестве разделителя
и еще:
Цитата
на сегодняшний день под CSV, как правило, понимают набор значений, разделенных какими угодно разделителями, в какой угодно кодировке с какими угодно окончаниями строк.
CSV - это текстовый файл, какой разделитель поставим такой и будет. Я поставил для этой задачи VbTab , а моя цитата которую Вы указали - это не общее описание формата файла, а конкретно моего файла - для решения задачи ТС. я так объяснял что к чему в моем коде
да ладно... Впервые слышу, ни разу не встречал CSV с разделителем VbTab.
Ну тогда файл, который создается благодаря моему макросу из 31-го поста будет для Вас открытием А если серьезно, то даже в приведенной Вами статье есть:
Цитата
Однако, большинство программ вольно трактует стандарт CSV и допускают использование иных символов в качестве разделителя
и еще:
Цитата
на сегодняшний день под CSV, как правило, понимают набор значений, разделенных какими угодно разделителями, в какой угодно кодировке с какими угодно окончаниями строк.
CSV - это текстовый файл, какой разделитель поставим такой и будет. Я поставил для этой задачи VbTab , а моя цитата которую Вы указали - это не общее описание формата файла, а конкретно моего файла - для решения задачи ТС. я так объяснял что к чему в моем коде SLAVICK
Иногда все проще чем кажется с первого взгляда.
Сообщение отредактировал SLAVICK - Воскресенье, 27.09.2015, 20:31
Ну как частный случай может быть и VbTab, но не безкомпромиссно "файл СSV - это итоговая база данных в текстовом формате с разделителем VbTab". Я помнится и сам давно в одной своей личной задаче генерил текстовые файлы с разделителем VbTab и расширением xls - вообще 2003 эксель открывал такие файлы как родные без вопросов.
Ну как частный случай может быть и VbTab, но не безкомпромиссно "файл СSV - это итоговая база данных в текстовом формате с разделителем VbTab". Я помнится и сам давно в одной своей личной задаче генерил текстовые файлы с разделителем VbTab и расширением xls - вообще 2003 эксель открывал такие файлы как родные без вопросов.Hugo
а моя цитата которую Вы указали - это не общее описание формата файла, а конкретно моего файла - для решения задачи ТС.
Т.е. описание конкретно под решаемую задачу, а не формата в целом. Разделитель может быть любым, но конкретно для этой задачи запятая и точка с запятой - не подходят - много разного текста - поэтому я выбрал VbTab
а моя цитата которую Вы указали - это не общее описание формата файла, а конкретно моего файла - для решения задачи ТС.
Т.е. описание конкретно под решаемую задачу, а не формата в целом. Разделитель может быть любым, но конкретно для этой задачи запятая и точка с запятой - не подходят - много разного текста - поэтому я выбрал VbTab SLAVICK
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
Можно только спросить - почему в макросе выгружается только в одну накопительную переменную ( при выгрузки в файл ) . А можно -ли как-то сделать чтобы выгружался массив по столбикам (чтобы было быстро). И как тогда это повлияет на быстрый подсчёт строк?
