Как настроить Excel на импорт всех столбцов CSV-файлов в текстовом формате?

Question

Хотя я стараюсь избегать этого, мне иногда приходится открывать файл CSV в Excel. Когда я это делаю, он форматирует столбцы, содержащие числа, что делает их бесполезными для моих целей. Насколько я могу судить, единственный способ предотвратить это при импорте - переименовать файл, чтобы расширение не было .csv, и использовать мастер импорта, чтобы указать формат каждого столбца в отдельности. Для файлов с 50-60 столбцами это нецелесообразно.

Поскольку каждый ответ на этот часто задаваемый вопрос в Интернете предлагает какие-либо способы преобразования отформатированных чисел обратно после открытия файла (что не сработает для меня - я хочу решить общую проблему, а не несколько конкретных случаев) или вручную выбирая тип формата каждого столбца (что я не хочу делать), я ищу способ установить глобальные предпочтения или стиль, чтобы все столбцы всех открытых CSV-файлов всегда форматировались как текст. Я также знаю, что нужно "защищать" числа с помощью кавычек, но файлы, которые я получаю, не такие, и я надеялся избежать предварительной обработки файлов, чтобы Excel их не испортил.

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

Я использую Excel 2003, но я приму ответы на 2007 год, если вы это знаете.

Answer 1

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

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

• Excel → Данные → Получить внешние данные → Выделите все столбцы с помощью Shift и выберите Текст.

  Вверху: все значения корректно обрабатываются как текст без каких-либо исключений

  Недостаток: больше шагов, чем простой двойной щелчок

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

• Откройте CSV с двойным щелчком мыши или Excel Открыть с помощью диалогового окна

  Недостаток: внутренний обработчик CSV в Excel неправильно интерпретирует значения с начальным знаком - или = как формула, а не текст

  Недостаток: вы потеряете ведущие нули из двоичных значений, таких как 0001 из-за автоматически определяемого формата столбца в Excel

Хороший способ (для VBA)

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

  Вверху: все значения корректно обрабатываются как текст без каких-либо исключений

  Недостаток: немного больше кода, чем в методе OpenText

Плохой путь (для VBA)

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

  Недостаток: этот метод все еще использует встроенный в Excel обработчик импорта CSV со всеми его недостатками.

  Недостаток: Кроме того, параметр fieldinfo OpenText игнорируется, если расширение CSV. Обычно этот параметр позволяет вам выбрать каждый формат столбца, но только если расширение не CSV. Вы можете прочитать больше об этом поведении в переполнении стека.

  ^{Временное переименование исходного расширения из CSV в TXT и затем обратно в CSV является допустимым обходным решением, если у вас есть полный контроль над исходным файлом.}

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

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

  _{Оригинальный контент CSV}
  

  _{CSV в Excel при двойном щелчке}
  

  Обратите внимание, что строка 2 (метод двойных кавычек) и строка 3 (метод табуляции) не изменяются в Excel

• Откройте CSV в блокноте и скопируйте и вставьте все значения в Excel. Затем используйте Данные - Текст в столбцы
  Недостаток: текст в столбцах для изменения форматов столбцов с общего обратно на текстовый дает несогласованные результаты. Если значение содержит символ « - окруженный символами (например, «= E1-S1»), Excel пытается разбить это значение на несколько столбцов. Значения, расположенные прямо в этой ячейке, могут быть перезаписаны
  ^{(Поведение текста в столбцы было изменено где-то между Excel 2007 и 2013, поэтому он больше не работает)}

---

Надстройка Excel для открытия CSV и импорта всех значений в виде текста

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

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

Монтаж

1. Загрузите надстройку и сохраните ее в папке надстроек : %appdata%\Microsoft\AddIns
2. Откройте Excel и активируйте вкладку «Надстройка: 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

Вы заметите новую запись в строке меню под названием «Надстройки», и вы будете использовать эту кнопку, чтобы быстро открывать файлы 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()
    }
}

2. Сохраните его где-нибудь как C:\my\folder\myScript.ps1 . (Обратите внимание на расширение .ps1)
   • Откройте папку sendto через Win R » shell:sendto » Enter
3. Создайте новый ярлык с помощью правой кнопки мыши »Новый» Ярлык и вставьте эту строку. Не забудьте поменять путь на свой, куда вы положили свой скрипт. Назовите ярлык, например, Excel

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

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

Answer 2

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

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 @GSerg утверждает в комментарии ниже, что это "не работает" и "съедает пробелы и ведущие нули". Я просто процитирую комментарий к вопросу, который является более описательным:

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

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

Answer 3

Вы можете попробовать сначала открыть .xlsx, затем создать подключение для передачи данных и импортировать .csv. Выберите разделитель "запятая", затем выберите параметр для обработки всех столбцов как текста, а не "Общие".

Изменить: О, я не полностью прочитал вопрос. В мастере импорта выберите первый заголовок столбца, который вы хотите импортировать как текст, выделите последний заголовок столбца и нажмите Shift+ щелкните заголовок. Затем выберите радиальную опцию "Текст".

Answer 4

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

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

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

Answer 5

Осторожно!

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

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

Row1Col1, Row1Col2, Row1Col3

Row2Col1, Row2Col2, Row2Col3, Row2Col4

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

Answer 6

Вот альтернативная процедура для кода, размещенного выше Жан-Франсуа Корбеттом

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

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

Answer 7

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

Некоторые варианты:

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

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

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

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

Answer 8

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

Answer 9

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

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

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

Answer 10

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