Powershell's export-csv with get-qaduser

Экспорт данных из SQL Server Express в CSV (требуется кавычки и экранирование)

Я потратил 2 дня, пытаясь экспортировать таблицу из 75 000 строк, содержащую большое текстовое поле пользовательских входных данных с установки SQL-сервера. Эти данные содержат каждый простой символ ascii, вкладки и новые строки. Мне нужно экспортировать CSV, где указано каждое поле, а кавычки в цитированных столбцах правильно экранированы («»).

Мне нужно импортировать с помощью библиотеки Ruby FasterCSV. Он не позволяет ограничителю цитат быть нестандартным символом ascii или более чем одним символом. Он также не позволяет \ n или \ r в столбцах без кавычек.

Любая помощь приветствуется.

2017-09-12 02:49

источник

Ответы:

Это можно сделать! Однако вам нужно специально настроить SSMS для использования цитируемого вывода, потому что по какой-то глупой причине это не значение по умолчанию.

В окне запроса, которое вы хотите сохранить перейдите в Query -> Параметры запроса …

Установите флажок «строки цитаты, содержащие разделители списков при сохранении результатов .csv».

тогда

чего мы все хотим.

2018-06-16 13:31

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

Я поднял свой стол в Экспресс, сделав SELECT * заявление
Просто выберите результирующие строки и Ctrl + С
Открыт Excel
Выделив количество столбцов таблицы, в которую я вставлял,
Вклеился, и он работал!
Теперь просто нужно экспортировать Excel в CSV и сделать.

Я знаю, это, наверное, звучит глупо, но это действительно сработало для меня.

2017-12-07 21:09

Самый простой способ сделать это:

Используйте инструменты импорта данных Excel

Перейти к данным> Из других источников> С сервера Sql
Введите имя сервера и т. Д.
Выберите таблицу или представление, которое вы хотите импортировать.

Затем сохраните импортированные данные в файл CSV. Если вы хотите экспортировать запрос, сохраните запрос в виде представления

2017-12-10 11:11

Вот суть сценария, который я использую для этого:

Надеюсь, поможет!

2017-12-07 22:12

Мне любопытно, почему никто не предложил использовать SSIS (службы интеграции SQL Server) для этого процесса? Все мастера и инструменты для импорта / экспорта из SSMS (SQL Server Management Studio) абсолютно не предназначены для того, чтобы быть исчерпывающими (и, конечно же, их нет, и да, есть много того, на что Microsoft должна должна отвечать с ограничениями ). Но SSIS — очень полнофункциональный инструмент ETL, предназначенный для решения проблем в точку как этот. Кривая обучения может быть немного крутой, но экспорт таблицы в файл csv с разделителями / запятыми не является особо сложным.

Возможно, это необходимо для Visual Studio, чтобы создать среду для создания пакета: http://www.microsoft.com/en-us/download/details.aspx?id=42313 (ссылка для VS 2013, другие ссылки доступны для других версий VS).

2017-08-07 14:52

Возможно, приведенная ниже ссылка может вам помочь:

Импорт / экспорт данных с помощью SQL Server 2005 Express

2017-09-12 03:46

Решение Роберта Калхауна не сработало для меня. У нас было много текста с запятыми и возвратами каретки / новостями и т. Д. Мы использовали функцию «Экспорт» с некоторыми изменениями в решении Криса Христодулу выше.

В SQL Management Studio щелкните правой кнопкой мыши базу данных и выберите «Задачи -> Экспорт данных».

Затем выберите SQL Server как источник и Flat File в качестве адресата. Назовите файл MyFile.csv.

Установите определитель текста как »

Выберите «Написать запрос для указания передачи данных» и вставьте запрос. Вы можете оставить следующие настройки в качестве значений по умолчанию.

С экспортированными данными откройте Excel и сохраните в формате Excel.

2017-09-08 10:51

Лучшее решение, которое я мог бы придумать, — это выбрать все строки и сделать копию XML.

Вставьте его в файл блокнота и сохраните его как файл XML. Затем откройте этот XML-файл от excel и voila! По крайней мере, как я получил свой файл.

2017-09-07 18:36

Самое простое решение, которое я нашел, заключается в добавлении двойных кавычек в ваш запрос;

Если ваша электронная таблица позволяет вам использовать специальный текстовый классификатор, вы можете использовать более экзотический символ, например | чтобы избежать двойных кавычек в тексте.

Daft..SSMS должен экспортировать как надлежащий CSV с цитированными текстовыми полями и правильно экранированные кавычки в этих полях.

2018-03-30 11:34

Мы сделали небольшой скрипт для преобразования SSSS «сломанный» CSV в правильный CSV, найти его в этом ответе:

https://stackoverflow.com/a/46876236/1532201

2017-10-22 16:06

Импорт CSV файла с помощью MySQL Workbench

MySQL Workbench предоставляет инструмент для импорта данных в таблицу БД. Он позволяет редактировать данные перед внесением изменений.

Ниже приведены этапы импорта данных в таблицу БД:

Откройте таблицу, в которую будут загружаться данные:

Нажмите кнопку Import, выберите CSV файл и нажмите кнопку Open:

Просмотрите данные, нажмите кнопку Apply:

В MySQL Workbench появится диалоговое окно «Apply SQL Script to Database», нажмите кнопку Apply, чтобы добавить данные в таблицу БД.

Мы рассказали вам, как импортировать CSV в таблицу MySQL с помощью LOAD DATA LOCAL, а также с использованием MySQL Workbench.

С помощью этих методов можно загружать данные и из других файлов текстовых форматов, таких, например, как текстовый файл с разделителями табуляции.

Пример 3

C:\PS>get-process | export-csv processes.csv -Delimiter ";" 

# In processes.csv

#TYPE System.Diagnostics.Process
__NounName;Name;Handles;VM;WS;PM;NPM;Path;Company;CPU;FileVersion;...
Process;powershell;626;201666560;76058624;61943808;11960;C:\WINDOWS...
Process;powershell;257;151920640;38322176;37052416;7836;C:\WINDOWS\...

Описание
-----------
Эта команда экспортирует объекты, представляющие процессы компьютера, в файл Processes.csv в текущем каталоге. Параметр Delimiter используется в этой команде для указания разделителя в виде точки с запятой (;). В результате поля в файле разделены знаком точки с запятой.

Export-Csv

Этот вид экспорта наиболее подходит и чаще всего применяется для формирования табличных выгрузок или отчетов. В параметрах этого командлета есть возможность указать, какой символ будет служить разделителем. Для демонстрации выгрузим все виртуальные машины на хосте виртуализации в файл .csv. Подробнее ►

В результате будет получен файл примерно следующего вида и содержания:

Просмотр в блокноте:

Как Вы видите, теперь содержимое файла автоматически распределилось по колонкам. А чтобы из шапки документа убрать тип выгружаемых данных, к команде необходимо добавить параметр -NoTypeInformation .

Notes

PowerShell includes the following aliases for :

All platforms:

The cmdlet converts the objects that you submit into a series of CSV strings and saves
them in the specified text file. You can use to save objects in
a CSV file and then use the cmdlet to create objects from the text in the CSV file.

In the CSV file, each object is represented by a character-separated list of the property values of
the object. The property values are converted to strings using the ToString() method. The
strings are represented by the property value name. does not
export the methods of the object.

The CSV strings are output as follows:

If IncludeTypeInformation is used, the first string contains the #TYPE information header
followed by the object type’s fully qualified name. For example,
#TYPE System.Diagnostics.Process.
If IncludeTypeInformation is not used the first string includes the column headers. The
headers contain the first object’s property names as a character-separated list.
The remaining strings contain character-separated lists of each object’s property values.

Beginning with PowerShell 6.0 the default behavior of is to not include the #TYPE
information in the CSV and NoTypeInformation is implied. IncludeTypeInformation can be used
to include the #TYPE Information and emulate the default behavior of prior to
PowerShell 6.0.

When you submit multiple objects to , organizes the file based on the
properties of the first object that you submit. If the remaining objects do not have one of the
specified properties, the property value of that object is null, as represented by two consecutive
commas. If the remaining objects have additional properties, those property values are not included
in the file.

You can use the cmdlet to recreate objects from the CSV strings in the files. The
resulting objects are CSV versions of the original objects that consist of string representations of
the property values and no methods.

The and cmdlets convert objects to CSV strings and from CSV
strings. is the same as , except that it saves the CSV strings in a
file.

Export/Write Data From PowerShell Into Text File

In order to export/write data from PowerShell into the text file, we use Out-File CmdLet usually with the Append parameter together.

INFO: I have written an article that has a great implementation of Out-File CmdLet so please read the section (PROCESS block code) and if you want to learn more about PowerShell Error Handling and writing errors in an external log file please read the whole article How To Log PowerShell Errors And Much More

How To Log PowerShell Errors And Much More

Here is the screenshot from that article and Log file created using Out-File CmdLet.

Example of error logged in an external text file

Исходные данные

В качестве примера я буду использовать СУБД Microsoft SQL Server 2012 Express.

Допустим, у нас будет база данных Test, а в ней следующая таблица

       
   CREATE TABLE dbo.TestTable(
                Id int IDENTITY(1,1) NOT NULL,
                ProductName VARCHAR(50) NOT NULL,
                Price MONEY NULL,
                CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (Id ASC))
        GO

Заполним таблицу тестовыми данными

   
        INSERT INTO dbo.TestTable
                           (ProductName, Price)
                 VALUES('Принтер',100)
        GO
        INSERT INTO dbo.TestTable
                           (ProductName, Price)
                 VALUES('Сканер',150)
        GO
        INSERT INTO dbo.TestTable
                           (ProductName, Price)
                 VALUES('Монитор',200)
        GO
        INSERT INTO dbo.TestTable
                           (ProductName, Price)
                 VALUES('Системный блок',300)
        GO
        INSERT INTO dbo.TestTable
                           (ProductName, Price)
                 VALUES('Ноутбук',400)
        GO

Посмотрим, что у нас получилось

      
   SELECT * FROM dbo.TestTable

И эти данные нам нужно выгрузить в CSV файл и не просто выгрузить, а реализовать функционал, с помощью которого обычные пользователи могли бы сами экспортировать данные. В качестве разделителя допустим, нам необходимо использовать точку с запятой.

Using PowerShell Export-CSV Delimiter Parameter

When you export a CSV file, by default values in the CSV file are comma-separated.

You can change the delimiter in a CSV file using the Export-CSV Delimiter parameter.

Using the Delimiter parameter, allows you to specify the delimiter.

For example, referring to the above example to get free disk space using PowerShell, use delimiter as ‘;’ (semicolon) instead of the default ‘,’ (comma).

Get-WmiObject -Class Win32_Bios | Select-Object -Property BIOSVersion,Manufacturer,Version,ReleaseDate | Export-CSV -Path D:\PowerShell\ComputerDetails.csv -NoTypeInformation -Delimiter ';'

In the above PowerShell Export CSV Delimiter example,

It writes output to CSV file separated by delimiter ‘;’ (semicolon)

The output of the above example is:

PowerShell Export CSV Delimiter

Параметры

-Delimiter <char>

Задает разделитель значений свойств. По умолчанию используется запятая (,). Введите символ, например двоеточие (:). Чтобы задать точку с запятой (;), заключите ее в кавычки.

Обязательно?	false
Позиция?	2
Значение по умолчанию	, (comma)
Принимать входные данные из конвейера?	false
Принимать подстановочные знаки?	false

-Кодирование <string>

Задает кодировку экспортируемого CSV-файла. Допустимые значения: Unicode, UTF7, UTF8, ASCII, UTF32, BigEndianUnicode, Default и OEM. По умолчанию используется значение ASCII.

Обязательно?	false
Позиция?	named
Значение по умолчанию	ASCII
Принимать входные данные из конвейера?	false
Принимать подстановочные знаки?	false

-Force

Перезаписывает файл по указанному пути, не выводя предупреждение.

Обязательно?	false
Позиция?	named
Значение по умолчанию
Принимать входные данные из конвейера?	false
Принимать подстановочные знаки?	false

-InputObject <psobject>

Задает объекты для экспорта в формате строк CSV. Введите переменную, содержащую объекты, либо получающую их команду или выражение. Кроме того, можно передать объекты командлету Export-CSV по конвейеру.

Обязательно?	true
Позиция?	named
Значение по умолчанию
Принимать входные данные из конвейера?	true (ByValue, ByPropertyName)
Принимать подстановочные знаки?	false

-NoClobber

Не перезаписывать существующий файл (то есть не заменять его содержимое). По умолчанию если файл существует по указанному пути, командлет Export-CSV перезаписывает файл без предупреждения.

Обязательно?	false
Позиция?	named
Значение по умолчанию
Принимать входные данные из конвейера?	false
Принимать подстановочные знаки?	false

-NoTypeInformation

Исключает из CSV-файла сведения о типах. По умолчанию первая строка CSV-файла содержит элемент «#TYPE «, после которого указывается полное имя типа объекта .NET Framework.

Обязательно?	false
Позиция?	named
Значение по умолчанию
Принимать входные данные из конвейера?	false
Принимать подстановочные знаки?	false

-Path <string>

Задает путь к выходному CSV-файлу. Это обязательный параметр.

Обязательно?	true
Позиция?	1
Значение по умолчанию
Принимать входные данные из конвейера?	false
Принимать подстановочные знаки?	false

-UseCulture

Использовать в качестве разделителя записей разделитель элементов списка из текущей культуры. По умолчанию используется запятая (,).

Этот параметр бывает полезным в скриптах, которые распространяются среди пользователей в разных странах мира. Чтобы найти разделитель элементов списка для текущей культуры, воспользуйтесь следующей командой: (Get-Culture).TextInfo.ListSeparator.

Обязательно?	false
Позиция?	named
Значение по умолчанию	Comma
Принимать входные данные из конвейера?	false
Принимать подстановочные знаки?	false

-Confirm

Запрашивает подтверждение перед выполнением команды.

Обязательно?	false
Позиция?	named
Значение по умолчанию
Принимать входные данные из конвейера?	false
Принимать подстановочные знаки?	false

-WhatIf

Описывает, что произойдет при выполнении команды, без ее фактического выполнения.

Обязательно?	false
Позиция?	named
Значение по умолчанию
Принимать входные данные из конвейера?	false
Принимать подстановочные знаки?	false

<CommonParameters>

Данный командлет поддерживает общие параметры -Verbose, -Debug, -ErrorAction, -ErrorVariable, -OutBuffer и -OutVariable. Дополнительные сведения см. в разделе
about_Commonparameters.

Using PowerShell Export-CSV NoClobber

PowerShell Export-CSV file writes output to CSV file and overwrites any existing file if exists on disk.

Using the Export-CSV NoClobber parameter throws an error if trying to overwrite and prevents overwriting the existing CSV file.

Let’s consider the above example to illustrate PowerShell export CSV noclobber functionality.

PS C:\> Get-WmiObject -Class Win32_Bios | Select-Object -Property BIOSVersion,Manufacturer,Version,ReleaseDate | Export-CSV -Path D:\PowerShell\ComputerDetails.csv -NoClobber

In the above PowerShell output to file script, we have used the Export-CSV NoClobber parameter.

If the file already exists on the specified path, it will throw an error and prevent overwriting the output to a CSV file.

The output of the above example to write output to CSV file is:

PowerShell Export-CSV NoClobber Parameter

Действие 3. Добавление сведений в свойства внешних контактов

После выполнения команды в действии 2 создаются внешние контакты, однако они не включают сведений о контакте или организации, то есть данных, содержащихся в остальных ячейках CSV-файла.

Выполните следующие команды, чтобы добавить в созданные внешние контакты другие свойства из CSV-файла:

$Contacts = Import-CSV D:\externalcontacts.csv

$contacts | ForEach {Set-Contact $_.Name -StreetAddress $_.StreetAddress -City $_.City -StateorProvince $_.StateorProvince -PostalCode $_.PostalCode -Phone $_.Phone -MobilePhone $_.MobilePhone -Pager $_.Pager -HomePhone $_.HomePhone -Company $_.Company -Title $_.Title -OtherTelephone $_.OtherTelephone -Department $_.Department -Fax $_.Fax -Initials $_.Initials -Notes $_.Notes -Office $_.Office -Manager $_.Manager}

Не беспокойтесь, если вы указали не все данные в CSV-файле. Если их там нет, они не добавляются.

Примечание. Параметр Manager может представлять проблему. Если эта ячейка пуста в CSV-файле, появится сообщение об ошибке и никакие данные свойств не будут добавлены в контакт. Если руководителя указывать не требуется, просто удалите часть –Manager $_.Manager из приведенной выше команды PowerShell.

Вот и все. В панели управления Exchange можно просмотреть свойства контакта. Пользователи могут просматривать контакты �� адресной книге Outlook и Outlook Web App.

PowerShell Export-CSV

The Export-CSV cmdlet is pretty straightforward and only has a few properties that are useful:

Path – (Required) Location of the CSV file
NoTypeInformation – Removes the Type information header from the output. Not needed any more in PowerShell 6
Delimiter – Default is comma, but you can change it
Append – Append to an existing CSV file
Force – Useful in combination with Append
NoClobber – Don’t overwrite existing files
UseQuotes – (PowerShell 7 only) wrap values in quotes or not

We are going to start with something simple, exporting our Microsoft 365 users to a CSV file. I am going to use Azure AD throughout the examples here, so if you want to follow along, make sure you connect the Azure AD first.

The cmdlet returns all the users in your Microsoft 365 tenant, as you can see in the screenshot below. What we are going to do is to export this output to a CSV file. To do this we can simply pipe the cmdlet behind it:

Get-AzureADUser | Export-Csv c:\temp\azureadusers.csv -NoTypeInformation

Sounds easy, right? Well if you open the CSV file you will notice that we got a bit more than we needed and not the nice list that we had seen in PowerShell before.

So why is this happening? The Export-CSV cmdlet exports all the individual objects from the Get-AzureADUser cmdlet. If we look up a single Azure AD user then you can see all the data that is returned from a single user object:

 Get-AzureADUser -Filter "Displayname eq 'Rudy Mens'" | select *

How to Export the correct information with Export-CSV

What we need to do is first select the correct information (properties) that we need before we export the user objects to a CSV file.

Get-AzureADUser | select userprincipalname, displayname, jobtitle, department, city | Export-CSV c:\temp\azureaduser.csv -NoTypeInformation

This will return the selected fields from each user in a nice CSV file that we can actually use:

Import Data From Text File Into PowerShell

To import data from a text file into PowerShell we use Get-Content CmdLet. In the example above we read the text file and save the result in variable $computers that will be used later.

The syntax is very simple we call Get-Content and provide value to the Path parameter where our text file has been located.

Here is the result of running only Get-Content CmdLet, the CmdLet simple reads the content of the text file.

Get-Content CmdLet result of reading the text file

Let’s quickly look at what the data type of the result is and what the properties and methods that we can use to manipulate the data are.

As you can see from the screenshot below (open in new Tab if the screen is too small for you), as a result of running Get-Content CmdLet, we got strings as expected and we have all the properties and methods that come with String data type.

Get-Content result data type with properties and methods

Использование SQL Server Management Studio

Импортировать данные из CSV можно стандартными средствами программы SQL Server Management Studio. Для этого выполните следующие шаги:

В обозревателе объектов Object Explorer выберите целевую базу данных, щелкните по ней правой кнопкой мыши и выберите из контекстного меню команду Tasks —> Import Data.

На экране появится диалоговое окно, в котором нужно выбрать источник данных DataSource из выпадающего списка. Выберите вариант Flat File Source и нажмите кнопку Next. На следующем экране укажите путь до файла CSV на жестком диске. Для нашего тестового файла нужно снять галочку «Column names in the first data row», т.к

в файле не содержатся заголовки столбцов в первой строке.

После этого SQL Server Management Studio загрузит файл и отобразит данные в таблице:

Обратите внимание, что программа автоматически определит разделитель между столбцами и строками в CSV-файле. Вы также можете настроить параметры столбцов (по умолчанию SSMS задаст столбцы в формате NVARCHAR(50) с названиями Column 0, Column 1 и т.д.) Перейдите на вкладку Advanced в левом боковом меню:

Как видите я изменил названия столбцов

Если у вас есть строки длиной больше 50, попросите SQL Server проверить все столбцы в файле. Это можно сделать с помощью кнопки Suggest Types (Предложить типы). SQL Server проанализирует первые 100 строк в файле и укажет предлагаемые типы для каждого столбца. В процессе проверки указывается ошибка. В зависимости от размера файла вы можете выбрать вариант, чтобы просмотреть весь файл или просто выбранные поля.

На следующем экране вы можете выбрать таблицу, в которую нужно импортировать данные. Здесь можно выбрать одну из существующих таблиц в базе данных, либо таблицу dbo.report, которую SSMS сгенерирует автоматически. Я рекомендую использовать второй вариант, а потом вручную преобразовать типы данных в основную таблицу.

После всех выполненных шагов SQL Server Management Studio отобразит процесс выполнения, сгенерирует таблицу report и заполнит ее данными:

На следующем рисунке отображена сгенерированная таблица report в окне Object Explorer:

Теперь выполните следующую инструкцию:

В результате мы получим результаты, аналогичные подходу с использованием кода.