Всем доброго времени суток! Помогите, пожалуйста, с решением следующего вопроса:
1. Есть .csv файл со статистикой продаж (в нем больше миллиона записей, т.е. в эксель он весь не залезет), в первой строчке находятся заголовки столбцов, делиметер - ; Что нужно сделать: Нужно обработать эту статистику макросом, не загружая ее в эксель изначально. Загрузить уже нужно будет промежуточный результат (например, сумму продаж по юнитам за все дни) расчета, который будет сильно меньше по размеру. Приоритеты: Минимизация шагов при обработке данных. Скорость выполнения обработки.
Заголовки для примера: Юнит, день, продажи - SKU, day, sales.
Сам пытался реализовать вот такой макрос - он работает, но при условии, что я предварительно руками перегоняю текстовый файлик .csv в формат .xlsx, теряя часть данных, и разбиваю текст по столбцам в нем:
Код
Sub SQL_test()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlManual
End With
Dim sSQLQry As String
Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = "C:\blahblahblah\File.xlsx"
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
Conn.Open sconnect
sSQLQry = "Select SKU, sum(Sales) FROM [HT$] group by SKU" - т.е., например, суммируем продажи по юнитам за все дни.
mrs.Open sSQLQry, Conn
Range("FT!A2:ZZ100000" .ClearContents
Range("FT!A2" .CopyFromRecordset mrs
mrs.Close
Set mrs = Nothing
Conn.Close
Set Conn = Nothing
With Application
.Calculation = xlAutomatic
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
Открыли текст кодом, читаем построчно, каждую строку разбиваем в массив по разделителям, анализ массива - собираем нужное в словарь. Если файл не очень огромный - можно его сразу прочитать целиком в массив, затем построчно перебирать этот массив. В конце уже работа только с словарём - выгрузка всего или выборочная. И никакого SQL не нужно.
2 Hugo: Файл - 100 мегабайт и 1200000 строк. Подскажи, пож-та, как это можно реализовать в коде? У меня просто все остальные макросы уже привязаны к SQL - тут некая стандартизация, чтоли.
100 мегабайт в память должно поместиться, можно сразу читать. Вот проверьте заготовку:
Код
Sub tt()
With CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\Downloads\example.csv", 1)
arrstr = Split(.ReadAll, vbCrLf)
End With
For i = 0 To UBound(arrstr)
If Len(arrstr(i)) Then
a = Split(arrstr(i), ";")
'тут вставьте перебор циклом массива из строки
End If
Next
MsgBox "OK", vbInformation
End Sub
Передавайте на сервер запрос с функцией BULK INSERT, OPENROWSET или OPENDATASOURCE. Таким образом, Excel используется только для передачи команды, файл откроет только сервер.
Пример запроса:
Код
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\excel-sql-server.xls', [Sheet1$])
Hugo, csv съедает. Увы, своего файлика, с подходящим примером под рукой нет, поэтому выложил линк по которому сам учился. ЗЫ на сайте MS приведен полный синтаксис и описание всех функций. Там разные форматы воспринимаются.
Еще пример запроса:
Код
SELECT * FROMOPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=D:\mmc;', 'SELECT * from test.csv');
Я практически с csv да ещё с такими не работаю, но думаю что могут быть проблемы с форматами всяких "000123" или с датами. А если сам "крутишь" массив - можно на лету и дату поправить как нужно, и нули не трогать.
Если честно, очень давно курил эту тему. Но, если мне не изменяет память, там только была проблема с исключениями разделителей (когда оный содержится в строковом выражении), а проблем с нулями и уж тем более с датами точно не было.
Вы импортируете данные в существующую таблицу, где типы данных полей уже определены. При необходимости привести смешанный тип к единому, разумеется, потребуется обработчик (http://linesofcode.net/snippets/45). Так же можно заюзать SET DATEFORMAT
Ещё бы узнать что именно нужно из этого файла получить - нужен пример результата. Может угадал?
Код
Sub tt()
Dim i&, ii&
With CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\Downloads\Test.csv", 1)
arrstr = Split(.ReadAll, vbCrLf)
End With
With CreateObject("Scripting.Dictionary"): .comparemode = 1
For i = 1 To UBound(arrstr)
If Len(arrstr(i)) Then
a = Split(arrstr(i), ";")
.Item(a(0)) = .Item(a(0)) + --a(5)
End If
Next
Workbooks.Add(1).Sheets(1).[a1].Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
End With
End Sub
Потому что иначе собирается сумма строк Посмотрите результаты с бинарным отрицанием и без него.
Да, там цикл с 1 а не с 0 именно потому, чтоб не преобразовывать в число строку заголовка. Вообще конечно на деле можно нарастить код проверкой на число, и тогда в вывод можно добавить и заголовок, и избежать ошибок если вдруг где-то в этих данных вместо чисел будут слова.
Схему не создал ,ругается на ваши отличные имена столбцов
Код
Sub Exec()
FilePath = "F:\Documents and Settings\Сергей\Рабочий стол" 'Путь к папке с файлом csv
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FilePath & "\" & " ;Extended Properties=Text;"
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim sCon As String, n As Long, sSql As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
sCon = ConnectionString
cn.Open sCon
Sql = "SELECT [T].SKU, Sum([T].[Sales, RUB]) AS [Sum-Sales, RUB]" & _
" FROM [Test.csv] as t GROUP BY [T].SKU;"
rs.Open Sql, cn, 3, 1
If rs.RecordCount > 0 Then
Range("A1").CopyFromRecordset rs
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
Текст ошибки точно не помню, но что-то там было про неполные данные. Думаю тоже что это из-за разделителей... Ну в общем всё сложно, мой подход проще. И работает, не требуя дополнительных библиотек Лишь бы словарь в системе был...
Игорь, так Jet штатно идёт начиная с WinXP - разве что есть проблема в 64бит версиях Офиса. Marchuk,
Цитата
в Accesse нельзя этот файлик прилинковать?
так в интерфейсе Access вы делаете тоже самое - неявно определяете схему данных для текстового файла: есть ли заголовки, разделитель и ограничитель, типы данных - есть только одно преимущество - через интерфейс можно выбрать большее языков. Если же посмотрите на строку подключения - то увидите, что используется "движок" Access.