Страницы: 1
RSS
ADO в Excel и большой размер файла.
 
Уваэаемые форумчане. помогите, пожалуйста, решить задачу. Есть у меня функция  
 
Public Function Get_fcst(Month As Integer, Year As Integer, Market As String, SKU_ID As Integer, Lag As Integer)  
Dim cnn As New ADODB.Connection  
Dim rst As New ADODB.Recordset  
Dim cm As New ADODB.Command  
Dim marketT As String  
Dim monthT As Integer  
Dim YearT As Integer  
Dim Lag_T As String 'new transformed variables  
 
Select Case Market  
   Case "Armenia": marketT = "ARM"  
   Case "Azerbaijan": marketT = "AZE"  
   Case "Azerbaijan (Naxcivan)": marketT = "AZE_NH"  
   Case "Georgia (IMS)": marketT = "GEO_IMS"  
   Case "Georgia (PF)": marketT = "GEO"  
   Case "Moldova": marketT = "MD"  
   Case "Turkmenistan": marketT = "TRKM"  
End Select  
 
Select Case Lag  
   Case 0: Lag_T = "LAG_0"  
   Case 1: Lag_T = "LAG_1"  
   Case 2: Lag_T = "LAG_2"  
   Case 3: Lag_T = "LAG_3"  
End Select  
Dim monthN As Integer  
monthN = Month + 3 - Lag  
 
If monthN <= 12 Then  
   monthT = Month + 3 - Lag  
   YearT = Year  
End If  
If monthN > 12 Then  
   monthT = Month + 3 - Lag - 12  
   YearT = Year + 1  
End If  
     
cnn.Provider = "Microsoft.Jet.OLEDB.4.0"  
cnn.ConnectionString = "Data Source = S:\Export\!BD_Projects\TOOLS\Forecast Accuracy Report\FAR.xls; Extended Properties=Excel 8.0;"""  
cnn.Open  
Dim sqlstr As String  
cm.ActiveConnection = cnn  
   cm.CommandText = "SELECT Month_Lag3, Year, Market, SKU_ID, " & Lag_T & " FROM [Forecast$] Where (Month_Lag3=" & monthT & " and Year=" & YearT & " and Market=""" & marketT & """ and SKU_ID=" & SKU_ID & ");"
     
sqlstr = cm.CommandText  
   rst.Open cm  
Get_fcst = rst(4)  
rst.Close  
Set rst = Nothing  
cnn.Close  
Set cnn = Nothing  
End Function  
 
Функция работает правильно. но если менять параметры функции, то при пересчёте сильно возрастает размер файла (функция фозвращает около 400 значений на одном листе; скопирована в каждую из 400 ячеек). В процесах (CTRL+ALT+DEL) процес Excel занимет от 100 до 800 мегабайт, причем до начала пересчёта - только 17-20 мегабайт. При закрытии файла процес остается висеть и его приходится удалять вручную, иначе винда начинает глючить. В чём проблема, что в коде надо исправить, чтобы размер процесса Excel не зашкаливал?  
 
П.С. Только недавно немного разобрался с ADO и не знаю всех тонкостей. Не судите строго.
 
Меня смущает Market.  
 
1. Если введёте слово с ошибкой, что будет?  
Я бы case else добавил.  
2. в запросе опять Market:  
Market=""" & marketT & """    
Это не мешает?
 
{quote}{login=Hugo}{date=17.09.2010 05:28}{thema=}{post}Меня смущает Market.  
 
1. Если введёте слово с ошибкой, что будет?  
Я бы case else добавил.  
2. в запросе опять Market:  
Market=""" & marketT & """    
Это не мешает?{/post}{/quote}  
 
1. Будет #Value :). Спасибо за совет, case else добавлю. Хотя ошибок быть не должно, потому что когда юзер запускает функцию, то в соответственных ячейках прописаны значения рынков и пользователю остается только выбрать нужный, кликнув на ячейку.  
2. Вряд ли мешает, так как первый Market - переменная, а второй - строка. Я подозреваю, что неполадки в использовании ADO. При открытии файла размер нормальный, а вот при обновлении в нём формул весь процес Excel разрастается. Вроде как бы подключения или рекордсеты не закрывались или ещё что-то. Даже не знаю. Но поменяв значение ячейки, в которой прописан тот же Market, несколько раз процес разростается. Обновление функции проходит медленно, конечно, но ето пустяки. Надо чтобы после этого процес Excel не разрастался
 
А меня смущает то, что данные из Excel переносятся в Excel с помощью, в общем-то, чужеродного средства.  
Ну ладно бы еще один запрос сделать, чтобы сразу заполнить диапазон нужными данными. Но 400 запросов?! Сколько времени это занимает, интересно?  
 
ЗЫ Мой 500-й пост :)
 
{quote}{login=Казанский}{date=17.09.2010 05:56}{thema=}{post}А меня смущает то, что данные из Excel переносятся в Excel с помощью, в общем-то, чужеродного средства.  
Ну ладно бы еще один запрос сделать, чтобы сразу заполнить диапазон нужными данными. Но 400 запросов?! Сколько времени это занимает, интересно?  
 
ЗЫ Мой 500-й пост :){/post}{/quote}  
У меня похожих функций очень много, и тянут данные они также из Accessа. Все работают нормально. Лучшего поиска данных я не знаю. Подскажите лучше, почему процесс Excel раздувается.
 
{quote}{login=Казанский}{date=17.09.2010 05:56}{thema=}{post}А меня смущает то, что данные из Excel переносятся в Excel с помощью, в общем-то, чужеродного средства.  
Ну ладно бы еще один запрос сделать, чтобы сразу заполнить диапазон нужными данными. Но 400 запросов?! Сколько времени это занимает, интересно?  
 
ЗЫ Мой 500-й пост :){/post}{/quote}  
 
Почему чужеродного? Родной вроде инструмент. Кроме того, как видите, SQL-запрос позволяет делать. Как вы будете SQL исполнять без драйвера то?  
Кроме того, он быстрее чем метод Copy.  
 
Могу посоветовать:  
1. Не открывать файл, с которого читаются файлы.  
2. Установить Office 2010 и использовать ACE OLEDB 12.0.  
3. Попробывать драйвер на ODBC.  
4. Перенести данные в формат txt(csv) и уже с них читать.  
5. Использовать две функции: одну на запрос данных, вторую непосредственно на их принятие, хранение и обработку.  
6. Использовать полноценную базу (postgres, например)
 
В предыдущем посте имел ввиду, что не стоит открывать файл с данными, хотя технически можно читать через ADO даже данные в этом же файле. Лучше создать временную копию тогда.
 
Еще про размер процесса: у меня при копировании условного форматирования на 30000 далее еще хотя бы на 10000 процесс начинает превышать 1,5 гб. Так что не все у вас плохо. Плохо лишь то, что остается в памяти.
 
{quote}{login=Longines}{date=17.09.2010 06:40}{thema=Re: }{post}{quote}{login=Казанский}{date=17.09.2010 05:56}{thema=}{post}А меня смущает то, что данные из Excel переносятся в Excel с помощью, в общем-то, чужеродного средства.  
Ну ладно бы еще один запрос сделать, чтобы сразу заполнить диапазон нужными данными. Но 400 запросов?! Сколько времени это занимает, интересно?  
 
ЗЫ Мой 500-й пост :){/post}{/quote}  
 
Почему чужеродного? Родной вроде инструмент. Кроме того, как видите, SQL-запрос позволяет делать. Как вы будете SQL исполнять без драйвера то?  
Кроме того, он быстрее чем метод Copy.  
 
Могу посоветовать:  
1. Не открывать файл, с которого читаются файлы.  
2. Установить Office 2010 и использовать ACE OLEDB 12.0.  
3. Попробывать драйвер на ODBC.  
4. Перенести данные в формат txt(csv) и уже с них читать.  
5. Использовать две функции: одну на запрос данных, вторую непосредственно на их принятие, хранение и обработку.  
6. Использовать полноценную базу (postgres, например){/post}{/quote}  
 
2 и 4 не подходят (на работе 2010 офиса не дождусь)  
1, 5 и 6 попробую и отпишусь  
До 3 пункта в изучении Excel еще не дошел. изучу и попробую  
 
П.С. интересно суто теоретически, почему процес висит после закрытия файла.
 
Всё равно висит. Наверное что-то с кодом.
 
{quote}{login=yavorsky90}{date=20.09.2010 11:03}{thema=}{post}Всё равно висит. Наверное что-то с кодом.  
 
1, 5 и 6 попробую и отпишусь  
{/post}{/quote}  
 
Если для вас пункт 1 действительно имеет значение, то у Microsoft на это есть отдельная статья про утечку памяти в этих случаях.  
http://support.microsoft.com/kb/319998  
Пункт 6 вы явно не пробовали, так как в этом случае ничего попросту висеть не может. Сам пользуюсь.  
Пункт 4 не имеет в качестве требований Excel 2010, он вообще не требует Excel, если хотите. Вы можете сделать это элегантно, написав скрипт на Python или PHP.  
Например, работать с csv очень просто:  
 
Private Sub CSV_Parser()  
   Dim CN As ADODB.Connection, rs As ADODB.Recordset  
   Dim strcon As String, strsql As String  
   Dim dbfilePath As String, dbfile As String, x  
   Dim c(), d(), e(), n&, i&    
   Set CN = New ADODB.Connection  
   dbfilePath = "C:\;"  
   dbfile = "parsed.csv"  
   strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfilePath & "Extended Properties=""text;HDR=No;FMT=Delimited"";"  
   CN.Open strcon  
   strsql = "SELECT TOP 1 * FROM " & dbfile ' здесь в начале находится число строк csv-файла, чтобы не перебирать построчно  
   Set rs = New ADODB.Recordset  
   rs.CursorType = adOpenForwardOnly  
   rs.LockType = adLockReadOnly  
   rs.PageSize = 10000  
   rs.Open strsql, CN  
   i = 1  
   n = rs.Fields(0).Value  
   ReDim c(1 To n, 1 To 1): ReDim d(1 To n, 1 To 1): ReDim e(1 To n, 1 To 1)  
   rs.Close  
   strsql = "SELECT * FROM " & dbfile & " WHERE F3 IS NOT NULL Order by Val(F3)"  
   rs.Open strsql, CN  
   i = 1  
   For i = 1 To n  
       c(i, 1) = rs.Fields(2).Value  
       d(i, 1) = rs.Fields(3).Value  
       e(i, 1) = rs.Fields(4).Value  
       rs.MoveNext  
   Next  
   rs.Close  
   Set rs = Nothing  
   Set CN = Nothing  
End Sub  
 
Дальше с массивами работается невероятно просто. 3 млн. строк импортируются за 3 секунды.
 
{quote}{login=Longines}{date=21.09.2010 11:43}{thema=Re: }{post}{quote}{login=yavorsky90}{date=20.09.2010 11:03}{thema=}{post}Всё равно висит. Наверное что-то с кодом.  
 
1, 5 и 6 попробую и отпишусь  
{/post}{/quote}  
 
Если для вас пункт 1 действительно имеет значение, то у Microsoft на это есть отдельная статья про утечку памяти в этих случаях.  
http://support.microsoft.com/kb/319998  
Пункт 6 вы явно не пробовали, так как в этом случае ничего попросту висеть не может. Сам пользуюсь.  
Пункт 4 не имеет в качестве требований Excel 2010, он вообще не требует Excel, если хотите. Вы можете сделать это элегантно, написав скрипт на Python или PHP.  
Например, работать с csv очень просто:  
 
Private Sub CSV_Parser()  
   Dim CN As ADODB.Connection, rs As ADODB.Recordset  
   Dim strcon As String, strsql As String  
   Dim dbfilePath As String, dbfile As String, x  
   Dim c(), d(), e(), n&, i&    
   Set CN = New ADODB.Connection  
   dbfilePath = "C:\;"  
   dbfile = "parsed.csv"  
   strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfilePath & "Extended Properties=""text;HDR=No;FMT=Delimited"";"  
   CN.Open strcon  
   strsql = "SELECT TOP 1 * FROM " & dbfile ' здесь в начале находится число строк csv-файла, чтобы не перебирать построчно  
   Set rs = New ADODB.Recordset  
   rs.CursorType = adOpenForwardOnly  
   rs.LockType = adLockReadOnly  
   rs.PageSize = 10000  
   rs.Open strsql, CN  
   i = 1  
   n = rs.Fields(0).Value  
   ReDim c(1 To n, 1 To 1): ReDim d(1 To n, 1 To 1): ReDim e(1 To n, 1 To 1)  
   rs.Close  
   strsql = "SELECT * FROM " & dbfile & " WHERE F3 IS NOT NULL Order by Val(F3)"  
   rs.Open strsql, CN  
   i = 1  
   For i = 1 To n  
       c(i, 1) = rs.Fields(2).Value  
       d(i, 1) = rs.Fields(3).Value  
       e(i, 1) = rs.Fields(4).Value  
       rs.MoveNext  
   Next  
   rs.Close  
   Set rs = Nothing  
   Set CN = Nothing  
End Sub  
 
Дальше с массивами работается невероятно просто. 3 млн. строк импортируются за 3 секунды.{/post}{/quote}  
 
Спасибо за ссылку. Все симптомы, кажется, мои. попробую разделить файл.  
С текстовыми файлами никогда не работал. Надо попробовать  
За последними наблюдениями вкладка с данными разростается очень быстро. Наверно сделаю базу в Аксесе и к ней буду клепать запросы.  
 
Проблема, скорее всего, из-за того, что я из одного листа через VBA обращаюсь к другому (оба листа из одной книжки). В то же время, аналогичная функция, которая обращаеться к Аксу, память не забивает. попробую с большим количеством функций и отпишусь
Страницы: 1
Наверх