Как открыть CSV в Excel

Хороший способ

• Excel → Данные → Получить внешние данные → Выберите все столбцы со сдвигом и выберите Text

Плохой способ

• Открыть CSV с Double Click или Excel Open with dialog

Good way (для VBA)

• Используйте таблицы запросов (аналог VBA для Get external data) → Пример кода

Плохой способ (для VBA)

• Используйте метод Workbooks.OpenText → Пример кода

Дополнительные методы

• Если у вас есть доступ к исходному тексту, который создает ваше CSV, вы можете изменить синтаксис CSV.
  Присоедините каждое значение с двойной кавычкой и префиксом знака равенства типа ="00001" или представьте вкладку к каждому значению. Оба способа заставят Excel обращаться со значением как с текстом

• Откройте CSV в Блокнот и скопируйте и вставьте все значения в Excel. Затем используйте Data - Text to Columns Downside: Text в файле columnns для изменения формата столбцов с общего на текстовый дает противоречивые результаты. Если значение содержит -, окруженное символами (например, “=E1-S1”), Excel пытается разделить это значение на несколько столбцов. Значения, расположенные прямо на этой ячейке, могут быть перезаписаны (поведение Text to columns было изменено где-то между Excel 2007 и 2013 годами, поэтому оно больше не работает)

Excel Add-In, чтобы открыть CSV и импортировать все значения в виде текста

Это плагин Excel, чтобы упростить действия по импорту CSV.
Главное преимущество: Это решение, работающее в один клик и использующее QueryTables, тот же пуленепробиваемый метод позади Get external data

• Добавлена новая команда меню в Excel, которая позволяет импортировать CSV и TXT-файлы. Все значения импортируются на активный лист, начиная с текущей выбранной ячейки
• Добавление Excel доступно для всех версий Office на Windows и Mac
• Весь Add-In имеет только 35 строк кода. Проверьте закомментированный исходный код , если вам интересно
• Использованный разделитель списков CSV (запятая или точка с запятой) берется из локальных настроек Excel
• Кодировка установлена в UTF-8

Установка

1. Загрузите Add-In и сохраните его в папку Add-Ins: %appdata%\Microsoft\AddIns
2. Откройте Excel и активируйте Add-In: File tab → Options → Add-Ins → Go To и выберите ImportCSV.xla
3. Включите макросы VBA: File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
4. Перезапустите Excel

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

PowerShell скрипт для открытия CSV-файлов непосредственно из Проводника Windows

Вы можете использовать PowerShell скрипт для открытия CSV-файлов и автоматической передачи их в Excel. Скрипт беззвучно использует метод импорта текста Excel, который всегда рассматривает значения как текст и, в качестве бонуса, обрабатывает кодировку UTF-8

1. Создайте новый текстовый файл и вставьте скрипт, описанный ниже. Комментированную версию можно найти здесь

$CSVs = @()
$args.ForEach({
    If ((Test-Path $_) -and ($_ -Match "\.csv$|\.txt$")) {
        $CSVs += ,$_
    } 
})

if (-Not $null -eq $CSVs) {

    $excel = New-Object -ComObject excel.application 
    $excel.visible = $true
    $excel.SheetsInNewWorkbook = $CSVs.Count    
    $workbook = $excel.Workbooks.Add()

    for ($i=0; $i -lt $CSVs.Count; $i++){

        $csv = Get-Item $CSVs[$i]
        $worksheet = $workbook.worksheets.Item($i + 1)
        $worksheet.Name = $csv.basename

        $TxtConnector = ("TEXT;" + $csv.fullname)
        $Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
        $query = $worksheet.QueryTables.item($Connector.name)
        $query.TextFilePlatform = 65001
        $query.TextFileTextQualifier = 1
        $query.TextFileOtherDelimiter = $Excel.Application.International(5) 
        $query.TextFileParseType = 1
        $arrFormats = ,2 * $worksheet.Cells.Columns.Count
        $query.TextFileColumnDataTypes = $arrFormats
        $query.AdjustColumnWidth = 1
        $query.Refresh()
        $query.Delete()
    }
}

1. Сохраните ее где-нибудь вроде C:\my\folder\myScript.ps1. (Обратите внимание на расширение .ps1)
2. Откройте папку sendto через WinR “ shell:sendto ” Введите
3. Создайте новый ярлык с помощью правой кнопки мыши “ New ” Shortcut и вставьте эту строку. Не забудьте изменить путь на свой собственный, куда вы поместили ваш скрипт. Назовите ярлык, например, Excel

“%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe ” -NoProfile -NonInteractive -WindowStyle Hidden -File “C:\my\folder\myScript.ps1”

Теперь вы можете выбрать (несколько) CSV и открыть их в Excel через Right-click » SendTo » Excel.

Это работает:

Sub OpenCsvAsText(ByVal strFilepath As String)

    Dim intFileNo As Integer
    Dim iCol As Long
    Dim nCol As Long
    Dim strLine As String
    Dim varColumnFormat As Variant
    Dim varTemp As Variant

    '// Read first line of file to figure out how many columns there are
    intFileNo = FreeFile()
    Open strFilepath For Input As #intFileNo
    Line Input #intFileNo, strLine
    Close #intFileNo
    varTemp = Split(strLine, ",")
    nCol = UBound(varTemp) + 1

    '// Prepare description of column format
    ReDim varColumnFormat(0 To nCol - 1)
    For iCol = 1 To nCol
        varColumnFormat(iCol - 1) = Array(iCol, xlTextFormat)
        ' What's this? See VBA help for OpenText method (FieldInfo argument).
    Next iCol

    '// Open the file using the specified column formats
    Workbooks.OpenText _
            Filename:=strFilepath, _
            DataType:=xlDelimited, _
            ConsecutiveDelimiter:=False, Comma:=True, _
            FieldInfo:=varColumnFormat

End Sub

Использование:

OpenCsvAsText "C:\MyDir\MyFile.txt"

Файл с разделителями-запятыми теперь открыт как лист Excel со всеми столбцами, отформатированными как текст.

Обратите внимание, что решение мастера @Wetmelon работает просто отлично, но если вы открываете много файлов, то вы, как и я, можете уставать от этого, каждый раз прокручивая до колонки 60, чтобы Shift-Click.

EDIT @В комментарии ниже говорится, что это “не работает” и “ест пробелы и ведущие нули”. Я просто процитирую комментарий к вопросу, который более описательный:

По неизвестным причинам, даже если вы явно зададите форматы для всех столбцов в VBA, Excel проигнорирует его, если расширение файла - CSV. Как только вы измените расширение, этот же код даст правильный результат.

Таким образом, код выше “работает”, но убивается этим нелепым поведением Excel. Каким бы способом вы ни резали его, вы застряли в том, что вам придется менять расширение на что-то кроме “.csv”, извините! После этого, ты свободен дома.

Предложение Ветмелона работает (даже с .CSV), если вы сделаете следующее:

1. Откройте Excel на пустую рабочую книгу или лист
2. Щелкните по ссылке Данные > [Получить внешние данные] из текста
3. Используйте “Мастер импорта текста”, как описано в Wetmelon (разделите запятыми, выберите первый столбец, SHIFT+CLICK последний столбец, установите все в Текст).

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

Берегись!

При использовании ручного метода “Excel → Данные → Получить внешние данные → Выделить все столбцы и выбрать Текст” …….

При этом столбцы будут иметь только текст “которые имеют данные в первой строке” (обычно это строка заголовка). Этот мастер не покажет дальше справа столбцы, которые могут иметь данные ниже, но не в первой строке. Например:

Row1Col1, Row1Col2, Row1Col3

Row2Col1, Row2Col2, Row2Col3, Row2Col4

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

Вот альтернативная процедура коду, опубликованному выше Jean-François Corbett

Эта процедура импортирует csv-файл в Excel, все колонки которого отформатированы как Text

Public Sub ImportCSVAsText()
    Dim TempWorkbook As Workbook
    Dim TempWorksheet As Worksheet
    Dim ColumnCount As Integer
    Dim FileName As Variant
    Dim ColumnArray() As Integer

    'Get the file name
    FileName = Application.GetOpenFilename(FileFilter:="All Files (*.*),*.*", FilterIndex:=1, Title:="Select the CSV file", MultiSelect:=False)

    If FileName = False Then Exit Sub

    Application.ScreenUpdating = False

    'Open the file temporarily to get the count of columns
    Set TempWorkbook = Workbooks.Open(FileName)
    ColumnCount = TempWorkbook.Sheets(1).Range("A1").SpecialCells(xlCellTypeLastCell).Column
    TempWorkbook.Close SaveChanges:=False

    'Resize the array to number of columns
    ReDim ColumnArray(1 To ColumnCount)

    For i = 1 To ColumnCount
        ColumnArray(i) = xlTextFormat
    Next i

    Set TempWorkbook = Workbooks.Add
    Set TempWorksheet = TempWorkbook.Sheets(1)

    Application.DisplayAlerts = False
    TempWorkbook.Sheets(2).Delete
    TempWorkbook.Sheets(2).Delete
    Application.DisplayAlerts = True

    With TempWorksheet.QueryTables.Add("TEXT;" & FileName, TempWorksheet.Cells(1, 1))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1251
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = ColumnArray
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End Sub

Если вы всегда импортируете одни и те же данные (постоянный формат записи, макет и т.д…), вы можете записать макрос Access, используя спецификацию импорта, а затем выкинуть данные обратно в Excel. Сделано это может быть несколько раз. Другой способ - использовать VBA и считывать данные в рабочий лист по одной записи за раз и разбирать их по мере чтения. Насколько я знаю, нет способа установить формат по умолчанию при импорте в Excel, и даже если бы вы могли, это привело бы к проблемам со следующим типом файла, который вы пытаетесь разобрать.

В качестве запроса, отправив мой комментарий в качестве ответа (с добавлением дополнительной информации):

Hey Scripting Guy написал статью в блоге об Импорт CSV в Excel , которая может быть полезна для вас. Игра с объектом данных внутри powershell может позволить вам делать то, что вы хотите.

Хотя в статье упоминается только импорт данных в ячейки, которые могут оставлять формат числа, можно поиграть с некоторыми свойствами и методами Excel ComObject, чтобы заставить данные вводиться в ячейки в виде необработанного текста вместо этого (или заставить форматирование ячеек в текст до или после импорта).

По неизвестным причинам, даже если вы явно зададите форматы для всех столбцов, Excel проигнорирует его, если файловое расширение CSV.

Некоторые опции:

• Создайте запрос на импорт данных, как предполагает Wetmelon предлагает .
  Недостаток: на 64-битной машине могут отсутствовать драйверы баз данных CSV.

• Используйте Jean’s code , но включите копирование файла во временную папку и измените расширение копии.
  Недостаток: Нет ссылки на исходный файл (сохранение перезапишет копию); после этого вам придется удалить копию вручную. Тем не менее, вы можете вручную сохранить как поверх оригинального CSV.

• Откройте CSV в Блокноте, Ctrl+A, Ctrl+C, вставьте в Excel, затем Data - Text to Columns, затем это обычный мастер, где вы можете установить все столбцы в Text за один заход. Это другой вкус предыдущей опции, потому что он также скрывает драгоценное расширение от Excel. 0x2 & Недостаток: ручной.

• Иметь очень простой цикл VBA, который считывает весь файл в память и помещает его на лист, ячейка за ячейкой.
  Недостаток: медленнее, уродливее.

Если я правильно понял вопрос, то он легко решается с помощью опции Transpose в Paste-Special, как описано здесь здесь .