桁数の多いテキストファイルをエクセルにインポートする

2021-07-01

エクセルのクエリテーブルオブジェクトを使い、データを読み込んだ時の数値の扱いについてです。

テキストファイルのデータの桁数が多い場合、一般形式でエクセルにインポートするとデータが切れます。

以下はサンプルで適当に作った数字です。項目A～項目Cまでは小数点のある数字です。

一般形式でインポート

データ型を指定しないでインポートした場合は、一般形式で読み込まれます。

サンプルコード

Sub sample1()
    Dim dPath As String: dPath = "F:\sample\data1.txt"
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim imData As QueryTable
    
    Set imData = ws.QueryTables.Add(Connection:="TEXT;" & dPath, _
                    Destination:=ws.Range("A1"))
    With imData
        .TextFilePlatform = 65001
        .TextFileParseType = xlFixedWidth
        .TextFileFixedColumnWidths = Array(18, 20, 20, 20)
        .Refresh
        .Delete
    End With
End Sub

読込み結果

2行目はテキストファイルから読込んだ結果です。

3行目はもとのテキストファイルの数値です。

インポートしたデータは数字がすべて表示されていないだけです。

しかし小数点以下の表示桁数を増やしても15桁までしか表示できません。これは入力できる数値の最大桁数が15桁だからです。

テキスト形式でインポート

項目A～Cをテキスト形式でインポートした場合です。

サンプルコード

Sub sample2()
    Dim dPath As String: dPath = "F:\sample\data1.txt"
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim imData As QueryTable
    
    Set imData = ws.QueryTables.Add(Connection:="TEXT;" & dPath, _
                    Destination:=ws.Range("A1"))
    With imData
        .TextFilePlatform = 65001
        .TextFileParseType = xlFixedWidth
        .TextFileColumnDataTypes = Array(1, 2, 2, 2)
        .TextFileFixedColumnWidths = Array(18, 20, 20, 20)
        .Refresh
        .Delete
    End With
End Sub

実行結果

もとのテキストファイルと同じ数字で取り込まれます。

11行目の「TextFileColumnDataTypes 」で指定します。

スクロールできます

値	定数	形式
1	xlGeneralFormat	一般
2	xlTextFormat	テキスト
3	xlMDYFormat	日付・MDY
4	xlDMYFormat	日付・DMY
5	xlYMDFormat	日付・YMD
6	xlMYDFormat	日付・MYD
7	xlDYMFormat	日付・DYM
8	xlYDMFormat	日付・YDM
9	xlSkipColumn	読込まない
10	xlEMDFormat	日付形式・EMD（台湾年月日）