2011-07-05 00:01:28 +0000 2011-07-05 00:01:28 +0000
69
69

CSVファイルのすべての列を常にテキストとしてインポートするようにExcelを設定するにはどうすればいいですか？

私はそれを避けるようにしていますが、私は時々ExcelでCSVファイルを開かなければならないことがあります。その際、数値を含む列がフォーマットされてしまい、私の目的には役に立たなくなってしまいます。私の知る限りでは、**インポート時にこれを防ぐ唯一の方法は、ファイルの拡張子を.csvではなく、インポートウィザードを使用して各列の形式を個別に指定するようにファイル名を変更することです。50～60列のファイルでは、この方法は現実的ではありません。

インターネット上でよく聞かれるこの質問に対するすべての回答は、ファイルを開いた後にフォーマットされた数値を元に戻す変換方法を提案しています（これは私には機能しません - いくつかの特定のケースではなく、一般的な問題を解決したいのです）、または各列のフォーマットタイプを手動で選択する方法を提案しています（これは私はしたくありません）ので、開いたすべてのCSVファイルのすべての列が常にテキストとしてフォーマットされるようなグローバルな環境設定またはスタイルを設定する方法を探しています。数値を引用符で「アーマリング」することも知っていますが、私が受け取るファイルはそのようなものではないので、Excelがファイルを台無しにしないようにファイルを前処理する必要がないようにしたいと思っていました。

これを具体的にする方法はありますか？開いているCSVファイルのすべての列を常にテキストとしてフォーマットし、インポート中に毎回手動で列を選択しなくてもよいのでしょうか？

私はExcel 2003を使用していますが、それが知っているものであれば2007の回答を受けます。
ソース

回答 (9)

73
73
73
2013-01-04 01:38:39 +0000

ExcelでCSVを開く方法

良い方法

  • Excel → データ → 外部データを取得 → Shiftですべての列を選択してTextを選択

悪い方法

  • ダブルクリックでCSVを開くか、ExcelのOpen with_ダイアログで開く

良い方法 (VBAの場合)

  • QueryTables(Get external dataのVBA対応)を使用する → コード例

Bad way (VBAの場合)

その他のメソッド

  • CSVを作成するソースへのアクセス権を持っている場合。を使用すると、CSVの構文を変更することができます。
    すべての値をダブルクォーテーションで囲み、="00001"のように等号を先頭につけるか、すべての値にタブを付けてください。

  • メモ帳でCSVを開き、すべての値をExcelにコピー＆ペーストします。その後、Data - Text to Columns を使用します。列のフォーマットを一般的なものからテキストに戻すためにText in Columnsを使用すると、一貫性のない結果が得られます。値が文字で囲まれた-を含む場合(例: “=E1-S1”)、Excelはその値を複数の列に分割しようとします。そのセルの右側に位置する値は上書きされる可能性があります~ (Text to columnsの動作はExcel 2007と2013の間のどこかで変更されたため、現在は動作しません)

CSVを開き、すべての値をテキストとしてインポートするExcelアドイン

これは、CSVのインポート操作を簡素化するExcelプラグインです。

主な利点：これはワンクリックソリューションであり、外部データの取得の背後にあるのと同じ防弾方法であるQueryTablesを使用しています

  • これは、CSVとTXTファイルをインポートすることができるExcelに新しいメニューコマンドを追加します。すべての値は、現在選択されているセルから始まるアクティブシートにインポートされます
  • Excelアドインは、WindowsとMacのすべてのOfficeバージョンで利用可能です
  • アドイン全体のコードはわずか35行です。
  • 使用するCSVリストの区切り文字(カンマまたはセミコロン)は、ローカルのExcelの設定から取得しています。
  • エンコードはUTF-8に設定されています。%appdata%\Microsoft\AddIns
  • Excelを開き、アドインを有効にします: File tab → Options → Add-Ins → Go To を選択し、ImportCSV.xla を選択します。VBAマクロを有効にします。File tab → Options → Trust Center → Trust Center Settings → Macro Settings → Enable all macros
  • Excelを再起動する

アドインという新しいメニューバーのエントリが表示されるので、このボタンを使用して、インポートダイアログの煩わしさを感じることなく、CSVファイルを素早く開くことができます。このスクリプトは、値を常にテキストとして扱う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. WinRでsendtoフォルダを開く “ shell:sendto ” と入力 2. 右クリックして新しいショートカットを作成し、この行を貼り付けます。スクリプトを置いたパスを自分のものに変更することを忘れないでください。ショートカットの名前は、例えば、Excel

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

これで、Right-click » SendTo » ExcelでCSVを選択して、Excelで開くことができます。
ソース
7
7
7
2011-07-06 19:13:20 +0000

これは動作します。

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ですべての列に明示的にフォーマットを指定しても、ファイルの拡張子がCSVの場合、Excelはそれを無視します。拡張子を変更するとすぐに、同じコードで正しい結果が得られます。

つまり、上記のコードは “動作する "のですが、このばかげたExcelの動作によって殺されてしまいます。どちらにしても、拡張子を”.csv “以外のものに変更する必要があります。その後は自由の身です。
ソース
4
4
4
2013-01-03 22:42:44 +0000

Wetmelonさんの提案は、以下のようにすれば（.CSVでも）機能します。

1.空白のワークブックまたはワークシートにExcelを開く 2. Data > [Get External Data] From Text 3. Wetmelonが説明しているように、「テキストインポートウィザード」を使用します（カンマ区切り、最初の列を選択、最後の列をSHIFT+CLICK、すべてをTextに設定）。

私はそれがより多くのステップであることを知っていますが、少なくともこれは拡張子を変更することなく、この方法でCSVを開くことができます
ソース
2
2
2
2015-12-02 14:21:59 +0000

気をつけよう！「Excel→データ→外部データ取得→列選択→テキスト」の場合

手動方法「Excel → データ → 外部データを取得 → すべての列を選択してテキストを選択」の場合 ……

これでは、「最初の行にデータがある」列（通常はヘッダー行）のみがテキストに設定されます。このウィザードでは、右端よりも下にデータがあり、最初の行にはデータがない可能性のある列は表示されません。例：

Row1Col1, Row1Col2, Row1Col3

Row2Col1, Row2Col2, Row2Col3, Row2Col4

インポート ウィザードではCol 4は表示されないので、インポート前にCol 4を一般的な形式からテキスト形式に変更するオプションは得られません!!!!
ソース
1
1
1
2015-12-04 09:21:54 +0000

Jean-François Corbett氏が上記に投稿したコードの代替手順は以下の通りです。

この手順では、すべての列が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
```としてフォーマットされたcsvファイルをExcelにインポートします。
ソース
0
0
0
2011-07-05 01:20:26 +0000

いつも同じデータをインポートしている場合（レコード形式、レイアウトなどが一定）は、インポート仕様を使用してAccessのマクロを書き、データをExcelにダンプして戻すことができます。これは何度かやったことがあります。私が行った他の方法は、VBAを使用して、ワークシートに1レコードずつデータを読み込み、読み込んだデータを解析することです。私の知る限りでは、Excelでインポート中にデフォルトのフォーマットを設定する方法はありませんし、もし設定できたとしても、解析しようとする次のファイルタイプで問題が発生するでしょう。
ソース
0
0
0
2011-07-05 17:29:47 +0000

Hey Scripting GuyがCSVをExcelにインポートするについてのブログ記事を書いてくれました。powershell内のdataオブジェクトを弄れば、あなたが望むようなことができるかもしれません。

記事ではデータをセルにインポートすることに言及していますが、Excel ComObjectのプロパティやメソッドを使って、データを強制的に生のテキストとしてセルに入力させることができるかもしれません（インポートの前後でセルの書式をテキストにすることもできます）。
ソース
0
0
0
2011-07-06 19:45:53 +0000

理由は不明ですが、すべての列に明示的にフォーマットを指定しても、ファイルの拡張子がCSVの場合はExcelは無視します。

いくつかのオプション。

  • Wetmelon [ が提案するように、データをインポートするためのクエリを作成する (https://stackoverflow.com/questions/6576983/how-can-i-set-excel-to-always-import-all-columns-of-csv-files-as-text/6600890#6600890).
    欠点: 64ビットマシンではCSVデータベースドライバが不足している可能性があります。

  • Jeanのコード を使用しますが、ファイルを一時フォルダにコピーし、コピーの拡張子を変更することを組み込んでください。
    不利。元のファイルへのリンクがない(保存するとコピーが上書きされる)；後から手動でコピーを削除しなければならない。それでも、元のCSVの上に手動で名前を付けて保存することはできます。

  • メモ帳でCSVを開き、Ctrl+A、Ctrl+CでExcelに貼り付けて、「データ」-「テキストを列に」を選択すると、一度にすべての列をテキストに設定できるいつものウィザードです。貴重な拡張子もExcelから非表示にしてくれるので、先ほどのオプションとはまた違った味わいがあります。
    欠点：手動。

  • ファイル全体をメモリに読み込み、シート上にセルごとに配置する非常にシンプルなVBAループを持つ。
    欠点: より遅い、醜い。
ソース
-2
-2
-2
2013-03-12 21:37:27 +0000

私が質問を正しく理解しているならば、これは ここに で説明されているように、Paste-SpecialのTransposeオプションを使用することで簡単に解決できます。
ソース