У меня есть скрипт, который выводит ряд показателей производительности для компьютера в .csv. Excel выполняет базовую проверку статистики для стандартного отклонения и т.д.
Я понимаю, что вы можете импортировать файлы .csv в Excel, но я пытаюсь динамически считывать эти данные в мою модель Excel без необходимости повторного импорта и повторного моделирования каждый раз.
Есть ли способ сделать ссылку на ячейку / таблицу внешним .csv без повторного импорта каждый раз?
Самый простой способ сделать это - импортировать данные в Access или SQL Express, а затем использовать функции и функции Data Connection в Excel. После начальной настройки все, что вам нужно сделать, это нажать «Обновить все», и он извлечет данные и пересчитает значения.
Вы можете напрямую импортировать CSV с помощью функции подключения к данным, но он захочет извлечь данные и добавить их в рабочую таблицу. Это будет работать до тех пор, пока вы импортируете его на дополнительный рабочий лист и оставите свои формулы и вычисления на другом листе. Когда вы нажмете «Обновить» с помощью этого метода, он запросит имя файла, а затем повторно импортирует его, используя те же настройки, которые использовались изначально. Этот метод также обычно медленнее или непригоден для больших наборов данных.
Вы также можете использовать Power Query, который является более гибким способом достижения того же, что и в ответе @ andy-lynch.
Смотрите анимированный GIF ниже для предварительного просмотра:
Power Query недоступен для MacOS; а также диалоговое окно открытия файла Excel "Из текста" в MacOS не принимает URL (по крайней мере, в версии 15.33).
Чтобы создать такое соединение, нужно сначала создать файл веб-запроса (.iqy), как описано в этом руководстве, а затем импортировать его с помощью команды меню «Данные -> Получить внешние данные -> Выполнить сохраненный запрос».
Вопрос «Как мне ссылаться на данные в CSV-файле?
Мой способ - вырезать файл .csv и использовать макрос, содержащий VBA и ADO, который получает данные и помещает их непосредственно в рабочую таблицу в Excel. Любая ODBC-совместимая база данных может быть вашим источником.
В Excel 2010 есть Данные >> Получить внешние данные - из различных источников данных. Однако я предпочитаю писать свой собственный код, потому что у меня есть абсолютный контроль и я могу отформатировать результаты в макросе. Обновить можно одним нажатием кнопки, и вы можете делать с ней все что угодно.
Я мог бы сообщить вам некоторые особенности, если вы скажете мне, из какой базы данных поступают метрики.
PS Посмотрите на список драйверов в разделе Данные >> Получить внешние данные >> Существующие подключения >> Просмотреть дополнительные сведения >> Новый источник >> Прочие дополнительные сведения >> Далее, что приведет вас к списку свойств канала передачи данных. Затем вы должны знать, какую исходную базу данных и какой драйвер использовать. Вы можете написать мне на cliffbeacham на hotmail с более подробной информацией.
Более простой способ (благодаря этому посту) - просто импортировать данные, как если бы вы уже загружали текстовый файл (например, « Data > Import Text File), но вместо ввода имени файла вставьте URL-адрес файла CSV. вместо.
С VBS макрос или скрипт будет выглядеть (Excel2010 и более поздние версии):
Sub Macro1()
'
' Macro1 Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;\\Path\To\CSV\Folder\CSV_Data.csv" _
, Destination:=Range("$A$1"))
.CommandType = 0
.Name = "Book1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Этот макрос был фактически записан в Excel 2013 Macro Recorder.
Вы можете создать кнопку, которая запускает этот макрос.
импортировать CSV-файл напрямую через File->From Text проверить свойства соединения в Data->Connections