Chociaż staram się tego unikać, czasami muszę otworzyć plik CSV w programie Excel. Kiedy to robię, formatuje kolumny zawierające liczby, co czyni je bezużytecznymi dla moich celów. O ile wiem, jedynym sposobem, aby temu zapobiec podczas importu, jest zmiana nazwy pliku, aby rozszerzenie nie było .csv i użycie kreatora importu do określenia formatu każdej kolumny osobno. W przypadku plików z 50–60 kolumnami jest to niepraktyczne.

Ponieważ każda odpowiedź na to często zadawane pytanie w Internecie sugeruje albo jakiś sposób konwersji sformatowanych liczb z powrotem po otwarciu pliku (co nie działa dla mnie - chcę rozwiązać ogólny problem, a nie kilka konkretnych przypadków) lub ręcznie wybierając typ formatu każdej kolumny (czego nie chcę robić), szukam sposobu na ustawienie globalnych preferencji lub stylu, tak aby wszystkie kolumny wszystkich otwieranych plików CSV były zawsze formatowane jako tekst. Wiem także o „zbrojeniu” liczb za pomocą cudzysłowów, ale pliki, które otrzymuję, nie są takie i miałem nadzieję uniknąć konieczności wstępnego przetwarzania plików, aby Excel ich nie zepsuł.

Czy istnieje sposób, aby to zrobić konkretnie : zawsze formatuj wszystkie kolumny w otwartych plikach CSV jako tekst, bez ręcznego wybierania każdej kolumny za każdym razem podczas importu?

Korzystam z programu Excel 2003, ale wezmę odpowiedzi na 2007, jeśli o tym wiesz.

To działa:

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

Stosowanie:

OpenCsvAsText "C:\MyDir\MyFile.txt"

Plik rozdzielany przecinkami jest teraz otwarty jako arkusz programu Excel ze wszystkimi kolumnami sformatowanymi jako tekst.

Pamiętaj, że kreator @ Wetmelon działa dobrze, ale jeśli otwierasz wiele plików, możesz, podobnie jak ja, zmęczyć się za każdym razem, przewijając do kolumny 60, aby ją kliknąć z wciśniętym klawiszem Shift.

EDIT @GSerg stwierdza w komentarzu poniżej, że to „nie działa” i „zjada spacje i zera wiodące”. Przytoczę komentarz do pytania, które jest bardziej opisowe:

Z nieznanych przyczyn, nawet jeśli jawnie podajesz formaty dla wszystkich kolumn w VBA, Excel zignoruje go, jeśli rozszerzenie pliku to CSV. Zaraz po zmianie rozszerzenia ten sam kod da prawidłowe wyniki.

Powyższy kod „działa”, ale zostaje zabity przez to niedorzeczne zachowanie Excela. Niezależnie od tego, w jaki sposób go wycinasz, utknąłeś przy zmianie rozszerzenia na coś innego niż „.csv”, przepraszam! Po tym jesteś w domu wolny.

Jak otwierać pliki CSV w programie Excel

Dobry sposób

• Excel → Dane → Uzyskaj dane zewnętrzne → Wybierz wszystkie kolumny za pomocą Shifti wybierz Tekst

  Upside: traktuje wszystkie wartości poprawnie jako tekst bez wyjątku

  Wada: więcej kroków niż zwykłe podwójne kliknięcie

Zła droga

• Otwórz plik CSV za pomocą podwójnego kliknięcia lub Excel za pomocą okna dialogowego

  Wada: wewnętrzna procedura obsługi CSV w programie Excel błędnie interpretuje wartości z interlinią -lub =znakiem jako formułą, a nie tekstem

  Wada: utracisz zera wiodące z wartości binarnych, na przykład z 0001powodu automatycznie wykrytego formatu kolumny programu Excel

Dobry sposób (dla VBA)

• Użyj QueryTables (odpowiednik VBA Get external data) → Przykładowy kod

  Upside: traktuje wszystkie wartości poprawnie jako tekst bez wyjątku

  Wada: Nieco więcej kodu niż OpenTextmetody

Zły sposób (dla VBA)

• Użyj Workbooks.OpenTextmetody → Przykładowy kod

  Wada: ta metoda nadal wykorzystuje wewnętrzną procedurę obsługi importu CSV programu Excel ze wszystkimi jej wadami

  Wada: Dodatkowo fieldinfoparametr parametru OpenTextjest ignorowany, jeśli rozszerzenie to CSV. Zwykle ten parametr pozwala wybrać każdy format kolumny, ale nie w przypadku rozszerzenia CSV. Możesz przeczytać więcej na temat tego zachowania na Przepełnienie stosu

  ^{Tymczasowa zmiana nazwy rozszerzenia źródła z CSV na TXT, a następnie powrót do CSV jest poprawnym obejściem, jeśli masz pełną kontrolę nad plikiem źródłowym}

Dodatkowe metody

• Jeśli masz dostęp do źródła, które tworzy plik CSV, możesz zmienić składnię CSV.
  Każdą wartość należy zawrzeć w podwójnych cudzysłowach i poprzedzić znakiem równości, np. ="00001"Lub wstawić tabulator do każdej wartości. Oba sposoby wymuszą na Excelu traktowanie wartości jako tekstu

  _{Oryginalna treść CSV}
  

  _{CSV w programie Excel po otwarciu podwójnym kliknięciem}
  

  Zwróć uwagę, że linia 2 (metoda podwójnego cudzysłowu) i linia 3 (metoda tabulatorów) nie są zmieniane przez program Excel

• Otwórz CSV w Notatniku i skopiuj i wklej wszystkie wartości do Excela. Następnie użyj Dane - Tekst do kolumn
  Minus: Tekst w kolumnach do zmiany formatów kolumn z ogólnego z powrotem na tekst daje niespójne wyniki. Jeśli wartość zawiera -znaki otoczone (np. „= E1-S1”), program Excel próbuje podzielić tę wartość na więcej niż jedną kolumnę. Wartości znajdujące się bezpośrednio w tej komórce mogą zostać nadpisane
  ^{(zachowanie tekstu na kolumny zostało zmienione gdzieś pomiędzy Excel 2007 i 2013, więc już nie działa)}

Dodatek Excel do otwierania plików CSV i importowania wszystkich wartości jako tekstu

To jest wtyczka programu Excel, która upraszcza działania importu CSV.
Główna zaleta: to rozwiązanie dostępne za jednym kliknięciem i wykorzystuje QueryTablestę samą kuloodporną metodę uzyskiwania danych zewnętrznych

• Dodaje nowe polecenie menu do programu Excel, które pozwala importować pliki CSV i TXT. Wszystkie wartości są importowane do aktywnego arkusza, zaczynając od aktualnie wybranej komórki
• Dodatek Excel jest dostępny dla wszystkich wersji pakietu Office w systemach Windows i Mac
• Cały dodatek zawiera tylko 35 wierszy kodu. Sprawdź skomentowany kod źródłowy, jeśli jesteś ciekawy
• Używany separator listy CSV (przecinek lub średnik) pochodzi z lokalnych ustawień programu Excel
• Kodowanie jest ustawione na UTF-8

Instalacja

1. Pobierz dodatek i zapisz go w folderze dodatków:%appdata%\Microsoft\AddIns
2. Otwórz program Excel i aktywuj dodatek: File tab → Options → Add-Ins → Go Toi wybierzImportCSV.xla
3. Włącz makra VBA: File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
4. Uruchom ponownie program Excel

Zauważysz nową pozycję paska menu o nazwie Dodatki i użyjesz tego przycisku, aby szybko otworzyć pliki CSV bez konieczności przechodzenia przez okno dialogowe Importuj

Skrypt PowerShell do otwierania plików CSV bezpośrednio z Eksploratora Windows

Za pomocą skryptu PowerShell można otwierać pliki CSV i automatycznie przekazywać je do programu Excel. Skrypt po cichu używa metody importu tekstu Excela, która zawsze traktuje wartości jak tekst i, jako bonus, obsługuje kodowanie UTF-8

1. Utwórz nowy plik tekstowy i wklej poniższy skrypt. Skomentowaną wersję można znaleźć tutaj

$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. Zapisz to gdzieś jak C:\my\folder\myScript.ps1. (Uwaga rozszerzenie .ps1)
   • Otwórz folder sendto przez WinR» shell:sendto» Enter
3. Utwórz nowy skrót, klikając prawym przyciskiem myszy »Nowy» Skrót i wklej ten wiersz. Nie zapomnij zmienić ścieżki na własną, w której umieściłeś skrypt. Nazwij skrót, na przykład Excel

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

Teraz możesz wybrać (wiele) plików CSV i otworzyć je w programie Excel za pośrednictwem Right-click » SendTo » Excel

Możesz spróbować, otwierając najpierw plik .xlsx, a następnie tworząc połączenie danych i importując plik .csv. Wybierz rozdzielany przecinkami, a następnie wybierz opcję traktowania wszystkich kolumn jako tekstu zamiast „Ogólnego”.

Edycja: Och, nie w pełni przeczytałem pytanie. W kreatorze importu wybierz nagłówek pierwszej kolumny, który chcesz zaimportować jako tekst, przewiń do nagłówka ostatniej kolumny, a następnie Shift + kliknij nagłówek. Następnie wybierz opcję promieniową „Tekst”.

Sugestia Wetmelon działa (nawet z .CSV), jeśli wykonasz następujące czynności:

1. Otwórz program Excel w pustym skoroszycie lub arkuszu
2. Kliknij Dane> [Pobierz dane zewnętrzne] z tekstu
3. Użyj „Kreatora importu tekstu”, jak opisuje Wetmelon (rozdzielany przecinkami, wybierz pierwszą kolumnę, SHIFT + KLIKNIJ ostatnią kolumnę, ustaw wszystko na Tekst).

Wiem, że to więcej kroków, ale przynajmniej pozwala mi w ten sposób otwierać pliki CSV bez konieczności zmiany rozszerzenia

Uważaj!

Podczas korzystania z metody ręcznej „Excel → Dane → Pobierz dane zewnętrzne → Wybierz wszystkie kolumny i wybierz Tekst” ......

Spowoduje to ustawienie w kolumnach tylko tekstu „mającego dane w pierwszym rzędzie” (zazwyczaj wiersz nagłówka). Ten kreator nie pokazuje kolumn dalej po prawej stronie, które mogą zawierać dane poniżej, ale nie w pierwszym rzędzie. Na przykład:

Row1Col1, Row1Col2, Row1Col3

Row2Col1, Row2Col2, Row2Col3, Row2Col4

Nigdy nie zobaczysz kolumny 4 w kreatorze importu, więc nie będziesz mieć możliwości zmiany jej z ogólnego na format tekstowy przed importowaniem !!!!

Oto alternatywna procedura do kodu zamieszczonego powyżej przez Jean-François Corbett

Ta procedura zaimportuje plik csv do programu Excel ze wszystkimi kolumnami sformatowanymi jako Tekst

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

Jeśli zawsze importujesz te same dane (stały format rekordu, układ itp.), Możesz napisać makro Access za pomocą specyfikacji importu, a następnie zrzucić dane z powrotem do Excela. Zrobiono to razy. Innym sposobem, w jaki to zrobiłem, jest użycie VBA i wczytywanie danych do arkusza roboczego po jednym rekordzie i analizowanie go podczas odczytywania. O ile mi wiadomo, nie ma sposobu, aby ustawić domyślny format podczas importu w programie Excel, a nawet gdyby to możliwe, spowodowałoby to problemy z następnym typem pliku, który próbujesz przeanalizować.

Na prośbę, przesyłając mój komentarz jako odpowiedź (z dodaniem nieco więcej informacji):

Hej Skrypciarze napisał artykuł na blogu na temat importowania CSV do Excela, który może mieć dla Ciebie przydatne informacje. Zabawa z obiektem danych w programie PowerShell może pozwolić ci robić, co chcesz.

Chociaż w artykule wspomniano tylko o importowaniu danych do komórek, które mogą pozostawić format liczbowy, możliwe jest, że będziemy się bawić niektórymi właściwościami i metodami Excel ComObject, aby zmusić dane do wprowadzenia komórek jako surowy tekst (lub wymusić formatowanie komórek w tekst przed lub po imporcie).

Z nieznanych powodów, nawet jeśli jawnie podajesz formaty dla wszystkich kolumn, Excel zignoruje je, jeśli rozszerzenie pliku to CSV.

Niektóre opcje:

• Utwórz zapytanie, aby zaimportować dane, jak sugeruje Wetmelon .
  Wada: może brakować sterowników bazy danych CSV na komputerze 64-bitowym.

• Użyj kodu Jean , ale włącz kopiowanie pliku do folderu tymczasowego i zmianę rozszerzenia kopii.
  Wada: brak linku do oryginalnego pliku (zapisanie spowoduje zastąpienie kopii); będziesz musiał ręcznie usunąć kopię później. Nadal możesz ręcznie zapisać jako w stosunku do oryginalnego pliku CSV.

• Otwórz plik CSV w programie Notatnik Ctrl+A, Ctrl+Cwklej do Excela, dane - Tekst jako kolumny, to jest to zwykły czarodziej gdzie można ustawić wszystkie kolumny do tekstu w jednym zamachem. To inny smak poprzedniej opcji, ponieważ ukrywa także cenne rozszerzenie z Excela.
  Wada: ręczna.

• Mają bardzo prostą pętlę VBA, która odczytuje cały plik do pamięci i umieszcza go na arkuszu, komórka po komórce.
  Wada: wolniejsza, brzydka.

Jeśli dobrze rozumiem pytanie, można to łatwo rozwiązać za pomocą opcji transpozycji w Paste-Special, jak opisano tutaj .