База данных текстовых файлов SQL с поддержкой VBA Excel

У нас нет доступа к SQL-серверу на работе, поэтому мне нужно создать приложение в Excel VBA и использовать текстовый файл (CSV) для хранения данных.

У меня нет проблем с запросом данных, присоединением к CSV, но я хотел бы использовать оператор SQL Pivot / Unpivot для переноса одного из столбцов в строки. Я не уверен, существует ли эта функциональность, поскольку я продолжаю получать ошибку, что в моем предложении FROM есть синтаксическая ошибка.

Public Function getData() As ADODB.Recordset Dim path As String, conn As ADODB.Connection, rs As ADODB.Recordset path = ThisWorkbook.path & "\" Set conn = New ADODB.Connection Set rs = New ADODB.Recordset conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & path & ";" & _ "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""") rs.ActiveConnection = conn rs.Source = "SELECT * " & _ "FROM " & _ "(SELECT emp_id, client, allocation " & _ "FROM ALLOCATIONdb.csv) AS s " & _ "PIVOT (SUM(allocation) FOR client IN (client1, client2)) AS pvt" Set getData = rs End Function 

Мои данные в настоящее время выглядят как верх изображения, и я хочу, чтобы это выглядело как дно.

Это достаточно просто, чтобы сделать с сводной таблицей, но я хотел, чтобы пользователь мог набирать данные.

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

заранее спасибо

Это может быть не лучшим решением, но оно работает для меня. Я преобразовал свои данные в список и передал его функции «ToDataTable», чтобы сначала преобразовать в datatable.

 public DataTable ToDataTable<T>(List<T> items) { DataTable dataTable = new DataTable(typeof(T).Name); PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance); foreach (PropertyInfo prop in Props) { dataTable.Columns.Add(prop.Name , prop.PropertyType); } foreach (T item in items) { var values = new object[Props.Length]; for (int i = 0; i < Props.Length; i++) { object val; val = Props[i].GetValue(item, null); values[i] = val; } dataTable.Rows.Add(values); } return dataTable; } 

Затем использование функции GetInversedDataTable () преобразует ее в желаемый формат.

 public static DataTable GetInversedDataTable(DataTable table, string columnX, string columnY, string columnZ, string nullValue, bool sumValues) { DataTable returnTable = new DataTable(); if (columnX == "") columnX = table.Columns[0].ColumnName; returnTable.Columns.Add(columnY); List<string> columnXValues = new List<string>(); foreach (DataRow dr in table.Rows) { string columnXTemp = dr[columnX].ToString(); if (!columnXValues.Contains(columnXTemp)) { returnTable.Columns.Add(columnXTemp); } } //Verify if Y and Z Axis columns re provided if (columnY != "" && columnZ != "") { List<string> columnYValues = new List<string>(); foreach (DataRow dr in table.Rows) { if (!columnYValues.Contains(dr[columnY].ToString())) columnYValues.Add(dr[columnY].ToString()); } //Loop all Column Y Distinct Value foreach (string columnYValue in columnYValues) { DataRow drReturn = returnTable.NewRow(); drReturn[0] = columnYValue; DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'"); foreach (DataRow dr in rows) { string rowColumnTitle = dr[columnX].ToString(); foreach (DataColumn dc in returnTable.Columns) { if (dc.ColumnName == rowColumnTitle) { if (sumValues) { try { drReturn[rowColumnTitle] = Convert.ToDecimal(drReturn[rowColumnTitle]) + Convert.ToDecimal(dr[columnZ]); } catch { drReturn[rowColumnTitle] = dr[columnZ]; } } else { drReturn[rowColumnTitle] = dr[columnZ]; } } } } returnTable.Rows.Add(drReturn); } } else { throw new Exception("The columns to perform inversion are not provided"); } if (nullValue != "") { foreach (DataRow dr in returnTable.Rows) { foreach (DataColumn dc in returnTable.Columns) { if (dr[dc.ColumnName].ToString() == "") dr[dc.ColumnName] = nullValue; } } } return returnTable; } - public static DataTable GetInversedDataTable(DataTable table, string columnX, string columnY, string columnZ, string nullValue, bool sumValues) { DataTable returnTable = new DataTable(); if (columnX == "") columnX = table.Columns[0].ColumnName; returnTable.Columns.Add(columnY); List<string> columnXValues = new List<string>(); foreach (DataRow dr in table.Rows) { string columnXTemp = dr[columnX].ToString(); if (!columnXValues.Contains(columnXTemp)) { returnTable.Columns.Add(columnXTemp); } } //Verify if Y and Z Axis columns re provided if (columnY != "" && columnZ != "") { List<string> columnYValues = new List<string>(); foreach (DataRow dr in table.Rows) { if (!columnYValues.Contains(dr[columnY].ToString())) columnYValues.Add(dr[columnY].ToString()); } //Loop all Column Y Distinct Value foreach (string columnYValue in columnYValues) { DataRow drReturn = returnTable.NewRow(); drReturn[0] = columnYValue; DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'"); foreach (DataRow dr in rows) { string rowColumnTitle = dr[columnX].ToString(); foreach (DataColumn dc in returnTable.Columns) { if (dc.ColumnName == rowColumnTitle) { if (sumValues) { try { drReturn[rowColumnTitle] = Convert.ToDecimal(drReturn[rowColumnTitle]) + Convert.ToDecimal(dr[columnZ]); } catch { drReturn[rowColumnTitle] = dr[columnZ]; } } else { drReturn[rowColumnTitle] = dr[columnZ]; } } } } returnTable.Rows.Add(drReturn); } } else { throw new Exception("The columns to perform inversion are not provided"); } if (nullValue != "") { foreach (DataRow dr in returnTable.Rows) { foreach (DataColumn dc in returnTable.Columns) { if (dr[dc.ColumnName].ToString() == "") dr[dc.ColumnName] = nullValue; } } } return returnTable; } 

Используйте его так:

 DataTable dtReturn = GetInversedDataTable(dt, "client", "emp_id", "allocation", "0", true); 

Три столбца предоставляются и возвращается новый DataTable.

В приведенном ниже примере будет использоваться исходная таблица и параметры ниже для создания сводной таблицы. В данном случае: Столбец X-оси: «клиент»: столбец Y-оси: «emp_id» Столбец Z-оси: «распределение» Null value: «0»; Сумма значений: true

Обратите внимание, что это то, что я использовал для своей проблемы, а мой код – в C #

 Public Function getData(db1 As String, db2 As String, var1 As String, var2 As String) As ADODB.Recordset Dim path As String, conn As ADODB.Connection, rs As ADODB.Recordset path = ThisWorkbook.path & "\" Set conn = New ADODB.Connection Set rs = New ADODB.Recordset conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & path & ";" & _ "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""") rs.ActiveConnection = conn rs.Source = "TRANSFORM sum(a.allocation) " & _ "SELECT a.emp_id, b.name, b.[new title], b.[new department] " & _ "FROM " & db1 & " a " & _ "INNER JOIN " & db2 & " b " & _ "ON a.emp_id = b.emp " & _ "GROUP BY a.emp_id, b.name, b.[new title], b.[new department] " & _ "ORDER BY b.[new department], b.[new title], b.name " & _ "PIVOT a.client" Set getData = rs 

Конечная функция

Трансформация закончилась тем, что мне нужно.

Спасибо за помощь!

  • Запрос диапазона ячеек, где каждое значение ячейки должно возвращать другой результат - Excel VBA ADODB
  • Импорт Excel в SQL2005, выпуск с символами валюты и текстом в поле чисел
  • как извлечь несколько записей, сравнив уникальный идентификатор в mySQL
  • Обновить запрос, не работающий для базы данных в том же файле, что и код vba
  • импортировать данные sql в excel с помощью vba
  • Данные SQL, представленные в Excell (ячейки вне порядка)
  • Экстренное длинное ведение / заявление - лучший вариант?
  • Получите разницу в датах со временем SQL / Excel
  • Импортировать XML в Excel 2013 - пределы размера файла?
  • Функция Excel иногда загружает нулевые значения в SQL
  • Скопировать несколько таблиц в Excel
  • Давайте будем гением компьютера.