TextToColumns分离成列

| Home |

1、テキストデータを指定したシートに開く。
プログラムの過程でテキストファイルを開く場合、指定したブックのシート上に開く事が前提になります。
区切り文字形式（各種）・固定長の場合を列情報を設定した上でVBAから開きます。

テキストデータの種類　 →sample-txt
■カンマ区切りの場合。
＜例：１＞
商品番号,商品名,単価,数量,日付
003,みかん,30,8,2008/5/12
001,りんご,100,5,2008/5/12
･････････････
■カンマ区切りでデータが、引用符「ダブルクォーテーション」で囲まれている場合。
＜例：２＞
"商品番号","商品名","単価","数量","日付"
"003","みかん","30","8","2008/5/12"
"001","りんご","100","5","2008/5/12"
･････････････
■カンマ区切りでデータが引用符「シングルクォーテーション」で囲まれている場合。
＜例：３＞
'商品番号','商品名','単価','数量','日付'
'003','みかん','30','8','2008/5/12'
'001','りんご','100','5','2008/5/12'
･････････････

■Cドライブのdataフォルダにあるsample.txtを、シート1のRange(A1)を基点として展開します。

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Sub myimport() Dim i As Integer, j As Integer Dim mytxtfile As String, mystr as String Dim myrange As Range, tmprange As Range mytxtfile = "c:\data¥sample.txt"　'※注１ Worksheets(1).Activate Range("A1").CurrentRegion.Clear Set myrange = Range("A1") Open mytxtfile For Input As #1 Do Until EOF(1) Line Input #1, mystr myrange.Offset(i).Value = mystr i = i + 1 Loop Close #1 Set tmprange = Range(myrange, myrange.End(xlDown)) tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _ FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5)) End Sub

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

myimport という名のプロシージャを記述します。 変数の宣言。 開く対象となるテキストファイルのパスを,変数mytxtfileに代入します。(例：Cドライブのdataフォルダのsampleテキスト) テキストファイルを開くシートを指定します。 念の為、セルA1のアクティブセル領域をクリアします。（領域に以前のデータが残っていた場合の削除） 変数myrangeにセルA1を格納する。 Openステートメントの入力モード(Input)でテキストファイルを開き、番号1を割り当てる。 EOF関数がTrueを返すまで（ファイルの末端になるまで）次の処理を繰り返します。 7.で開いた文書データを行単位で読み込み、変数 mystr　に格納します。 myrange(セルA1)から下方向(Offset(1))に格納した文書データを1行ずつ表示していきます。 変数iに1を足します。 8.に戻ります。 ファイルを閉じて、番号（この場合1）を開放します。 変数tmprangeにmyrang(セルA1)から、下に向けてデータの入力されている終端迄のセル範囲を格納します。 14.で選択された範囲のデータをTextToColumnsメソッドを使い複数の（この場合5つ）セルに分割します。 プロシージャの記述を終了します。

※注１　例として、自機のCドライブのdataフォルダのsample.txtファイルを指定しています。
仮に今開かれているエクセルと同じフォルダの場合、mytxtfile = ActiveWorkbook.Path & "\sample.txt"でも可。
また、ネットワークコンピューターの場合は、mytxtfile = "\\keiri\c\date\sample.txt"（例：keiriというネットワークコンピューターのCドライブのdataフォルダのsample.txtを指定）の様に記述します。



※下図が1～13までのコードを実行した、各々のシート上の結果になります。（セルＡ1～A11展開されています）

＜例：１＞の場合


＜例：２＞の場合


＜例：３＞の場合


この後、＜例：１＞と＜例：２＞の場合は以下14～16のステートメントを実行すれば、求める結果になります。
--------------------------------------------
Set tmprange = Range(myrange, myrange.End(xlDown))
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5))
End Sub



■TextToColumnsメソッド
1つのセル内でスペースやタブ、カンマなどで区切られているデータを複数のセルに分割します。
Ａ列で展開したテキストファイルをカンマ区切りを元に5列（商品番号・商品名・単価・数量・日付）に振り分けます。
対象となるテキストファイルが「区切り文字形式」の時は、引数DataTypeにxlDelimitedを指定します（規定値）。
引数FieldInfoのArray関数では1番目が列番号、2番目が変換方法を表します。（変換方法は、1～10まで、下記表参照 ※表１）
上の設定の場合1、2列目が「文字列」　3,4列目が「標準」　5列目が「年月日」の形式になります。

▲Top

※さて、問題は＜例：３＞の「カンマ区切りでデータが引用符・シングルクォーテーションで囲まれている場合」ですが、よく見ると、各行の頭の「シングルクォーテーション」がありません。

そもそもエクセルの仕様として、セルの先頭に付いている「シングルクォーテーション」は、それ以後に入力された数字や数式を文字列として表示する、という意味を持っていますので、 この場合もその仕様に従った結果になった訳です。

例：）入力値　　→　　セル上の表示
00001　→　1 (シングルクォーテーションのない場合)
'00001　→　00001　（シングルクォーテーションを付けた場合）
└→ 頭のカンマが取れ、文字列として表示される

このまま、先ほどのプロシージャを実行しても求める結果は得られません。
プロシージャmyimportを実行した結果


対応策として、次のステートメントを付け加え、「全てのシングルクォーテーション」をはずしてしまうことにします。
tmprange.replace What:="'", Replacement:=""
これを、
Set tmprange = Range(myrange, myrange.End(xlDown))　と
ココ→
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _　の間に入れ、

↓
Set tmprange = Range(myrange, myrange.End(xlDown))
tmprange.replace What:="'", Replacement:=""
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5))
End Sub

として、実行すれば完成です。　　 →sample-code

▲Top

カンマ区切り以外の場合
■タブ、セミコロン、スペース、カンマ＋スペースなどの場合
※例えば、sub myimport() のステートメント15.の　Comma:=True, _　の箇所を
･････････････
tmprange.TextToColumns DataType:=xlDelimited, Comma:=True, _ 　←ココ　

Tab:=True, _ 　　　 ←タブ区切りオン
Semicolon:=True, _ 　　　　 ←セミコロン区切りオン
Space:=True, _ 　　　　 ←スペース区切りオン

に、変更する事で対応できます。

■特殊なケースとして、「カンマ＋スペース」での区切りの場合。　　 →sample-code 　　 →sample-txt

同じく、sub myimport() のステートメント15.を以下の様に変更します。
･････････････
tmprange.TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, _
Comma:=True, Space:=True, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 1), Array(4, 1), Array(5, 5))
End Sub

※ConsecutiveDelimiter:=True
連続した区切り文字を1文字として認識する時は、Trueを指定します。

FieldInfoの書き方は下記もできます。

Sub Test()
　　Dim a() As Variant
　　Dim fmt(0 To 7) As Variant
　　Dim fld_cnt As Integer
　　Dim i As Integer
　　
　　'書式の配列を用意する
　　fmt(0) = Array(1, 2)
　　fmt(1) = Array(2, 1)
　　fmt(2) = Array(3, 2)
　　fmt(3) = Array(4, 1)
　　fmt(4) = Array(5, 2)
　　fmt(5) = Array(6, 1)
　　fmt(6) = Array(7, 2)
　　fmt(7) = Array(8, 1)
　　
　　fld_cnt = 3 'フィールド数を指定する
　　
　　'配列 a を生成
　　For i = 0 To fld_cnt - 1
　　　　ReDim Preserve a(0 To i)
　　　　a(i) = fmt(i)
　　Next i
　　
　　'TextToColumns メソッド
　　Range(A1).TextToColumns Destination:=Range(A1), DataType:=xlDelimited, _
　　　　TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
　　　　Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
　　　　FieldInfo:=a '←生成した配列を指定
End Sub

■固定長フィールド形式の場合
固定長フィールド形式とは、文字列の長さが決められている文字列のことです。文字が指定の長さの満たない場合は、スペースや0が追加されます。 主にCOBOLを使ったメインフレームとのデータのやり取りなどに必要です。

＜コード例：２＞　 →sample-txt

Sub myimport_kotei()

Dim i As Integer, j As Integer
Dim mytxtfile As String, mystr As String
Dim myrange As Range, tmprange As Range

mytxtfile = "c:\data\kotei.txt" 　'←固定長フィールド形式のファイルを選択。
Worksheets(1).Activate
Range("A1").CurrentRegion.Clear
Set myrange = Range("A1")
Open mytxtfile For Input As #1
Do Until EOF(1)
Line Input #1, mystr
myrange.Offset(i).Value = mystr
i = i + 1
Loop
Close #1

Set tmprange = Range(myrange, myrange.End(xlDown)) 　'←ここまではテキストファイルの指定以外、＜コード例・１＞と同じです。
tmprange.TextToColumns DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(8, 2), Array(18, 1), Array(24, 1), Array(30, 5)) 　'←引数とArray関数に注意 ※注２
End Sub


※注２
固定長フィールド形式のテキストファイルを開く時は引数 DataType に xlFixedWidth を必ず指定します。
引数FieldInfoのArray関数では1番目が文字位置、2番目が変換方法を表します。（変換方法は、1～10まで、下記表参照 ※表１）
上の設定の場合1、2列目が「文字列」　3,4列目が「標準」　5列目が「年月日」の形式になります。



■参考として、kotei.txtをワークシート上から開いてみます。
ファイル→開く→Cドライブ→dataフォルダ→kotei.txt　（※Cドライブのdataフォルダにkotei.txtがある場合）
すると、下のテキストファイルウィザードが開きます。


区切り位置を指定する。（0、8、18、24、30）


列のデータ形式を選択する。（1、2列 = 文字列　3、4列 ＝標準　5列 = 日付）


　　　　　　　　　　　　　 ↓↓↓完了です↓↓↓

※区切り位置に付いては、受け側で決めるものではなく、通常、メインフレーム側で予め設定されていますので、その指示に従って区切ればOKです。逆に桁数を指定してテキストファイルの作成を依頼される場合は、その指示通りに作成する必要があります。
【参考】
└→　 ワークシート上のデータを固定長にして、テキストファイルとして書き出す。

※表１
XlColumnDataTypeクラスの定数

数値	組み込み定数	変換方式
1	xlGeneralFormat	一般（標準）
2	xlTextFormat	テキスト（文字列）
3	xlMDYFormat	MDY (月日年) 形式の日付
4	xlDMYFormat	DMY (日月年) 形式の日付
5	xlYMDFormat	YMD (年月日) 形式の日付
6	xlMYDFormat	MYD (月年日) 形式の日付
7	xlDYMFormat	DYM (日年月) 形式の日付
8	xlYDMFormat	YDM (年日月) 形式の日付
9	xlEMDFormat	EMD (台湾年月日) 形式の日付
10	xlSkipColumn	スキップ列（その列は削除）

▲Top