Excel

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

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行目はもとのテキストファイルの数値です。

インポート結果の比較

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

項目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

-Excel
-

© 2021 オフィスのQ&A