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

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

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

以下はサンプルで適当に作った数字です。項目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行目はもとのテキストファイルの数値です。

インポート結果の比較

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

項目Aの比較

しかし小数点以下の表示桁数を増やしても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 」で指定します。

スクロールできます
定数形式
1xlGeneralFormat一般
2xlTextFormatテキスト
3xlMDYFormat日付・MDY 
4xlDMYFormat日付・DMY
5xlYMDFormat日付・YMD
6xlMYDFormat日付・MYD
7xlDYMFormat日付・DYM
8xlYDMFormat日付・YDM 
9xlSkipColumn読込まない
10xlEMDFormat日付形式・EMD(台湾年月日)
XlColumnDataType

テキストファイルのデータが指数表示の場合

文字列で読込めばすべての桁数でで表示できますが、もとのデータが指数表示だった場合はどうなるか。

項目Cが指数表示の場合、以下のように表示されます。

指数表示

この後、このデータを使い計算結果を利用する場合、このままの表示では困ります。

数値をどう扱うかでやり方は変わります。

例えば、読み込みデータと計算結果は小数点以下8桁までしか使用しないなら、以下の方法で対応できます。

  • データを読み込んだ後に「NumberFormatLocal = "0.00000000"」でデータの桁数をそろえる
  • 計算結果の値をRound関数等で小数点以下8桁までの結果にする
NumberFormatLocal
目次