QA@IT

項目内に改行CRLFを含むCSVをExcelに取り込む際の書式指定ができません

14944 PV

お世話になります。
現在、Excel2010のマクロを使って、ある項目内に改行CRLFを含むCSVを
Excelシートにコピーして加工するマクロを作成しようとしています。

CSV1レコードの例)

"001","b","c","d[CR][LF]
e[CR][LF]
f[CR][LF]
g","h"

4項目名に改行が含まれますが、1レコードあたり5項目のCSVです。
CSVをExcelに取り込むには様々な方法があると思いますが、改行を含んでいても正しく取り込める方法として、
現在は下記のようにOpenText関数を使用して読み込みを行い、シートへ貼り付けています。

    Workbooks.OpenText fileName:=取り込みたいファイルの名称, StartRow:=1, _
     DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
     ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
     Comma:=True, Space:=False, Other:=False, _
     FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlTextFormat), _
・・・・・・・・・・・・・・

このとき、CSVの取込自体はできるのですが、FieldInfoで指定した書式指定
(ここでは文字列をあらわすxlTextFormat)が無視されてしまい、
Excelに取り込んだときには文字列でなく標準の書式になっています。
つまり上記の例では1項目目の"001"が桁落ちして1となってしまいます。
ネットで調べたところ、OpenText関数では拡張子csvのファイルを読み込む際には
FieldInfo指定が無効となるとありました。そこでファイルの拡張子を.txtに変えてみたところ、
今度は項目内改行が認識されず、行区切りの改行とみなされてしまって、項目取り込みがずれてしまいます。
なんとか、桁落ちせず、なおかつ項目内改行も正しく認識できるような方法はないでしょうか。
OpenText関数以外の方法でもかまいませんので、有識者の方々にご教授いただければ、
と思っております。
よろしくお願いします。

  • 回答のコメントに追記しました -

回答

他の方の回答のQueryTablesの響きを見てそういやADOから取り込めたのを思い出したので試してみました。
完璧かどうかはわかりませんが、Jetエンジンを利用して取り込めば比較的うまく取り込めるのではないかと思います。

Sub ImportCsvViaADO()

    Dim strPath As String
    Dim strFilename As String
    strPath = "C:\path\to\csv\"
    strFilename = "abc.csv"

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")


    Dim oAdoCon As Object
    Set oAdoCon = CreateObject("ADODB.Connection")


    Call oAdoCon.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" _
                    & "Extended Properties='Text;HDR=NO'")


    Dim oRS As Object
    Set oRS = oAdoCon.Execute("SELECT * FROM " & strFilename)


    Dim oVal As Object
    Dim rowIndex As Long
    Dim colIndex As Long

    rowIndex = 1
    Do Until oRS.EOF
        colIndex = 1
        For Each oVal In oRS.Fields
            sh.Cells(rowIndex, colIndex).Value = oVal
            colIndex = colIndex + 1

            Set oVal = Nothing
        Next
        rowIndex = rowIndex + 1

        oRS.MoveNext
    Loop

    Set oRS = Nothing
    Set oAdoCon = Nothing

    Set sh = Nothing
End Sub

以下のようなCSVで期待通り(全部で3行、1行目の一番右のセルには"h"が入っているし改行を含む要素はセル内で改行もされている)に取り込まれました。

"011","b","c","d[CR][LF]
e[CR][LF]
f[CR][LF]
g","h"[CR][LF]
"0あいう","示","c"[CR][LF]
"0[CR][LF]
1[CR][LF]
1","c"[CR][LF]

先頭のゼロはそのままでも残るようでしたが、心配でしたら実行前にテキスト書式にしてしまえばよいかと。
難点を言えば、64bit版のOffice(OSではなくてOfficeが64bit版か否か)だと動かないかもしれない点ですかね。

とりあえずWin8.1 64bit, Excel 2013 32bit の環境では取り込めました。

編集 履歴 (2)
  • 回答ありがとうございます。この方法で対応できました。
    なお、私の使っているのは
    Widnow7、Excel2010で
    バージョン:14.0.7106.5003(64ビット)ですが、
    動いていますよ。
    -
  • 情報ありがとうございます。一般論として64bit版のCOMがリリースされていないと動かないという点が気になってました。
    ただし、http://answers.microsoft.com/ja-jp/windows/forum/windows_7-windows_programs/microsoftjetoledb40/88ba7315-9c05-43cb-9d22-2bd5f59f3450
    -
  • の様な情報もありますので、もし確認したマシンにAccessが入っていて、Excel(64bit)しか入っていないマシンでも動かす予定があるのなら、ついでに確認しておいてもいいかもしれません。 -

以前、同じようなことにぶつかったのですが、
「QueryTables.Add」
を使って解決した記憶があります。
カンマ区切りの指定も、読み込む時の書式も指定できたはずです。

以下の項目を設定すれば、ご期待の動きをしそうです。
TextFileCommaDelimiter
TextFileColumnDataTypes

ご参考になれば、幸いです。

編集 履歴 (0)
  • これはテキストインポートウィザードと同じなのでセル内の改行が上手くいかないかもしれません。 -
  • 回答ありがとうございます。この方法は私も試したことがあるのですが、やはり改行がうまく取り込めませんでした。 -
  • 桁落ちのことしか頭に入っておらず、肝心の改行について、すっかり抜けていましたね。恥ずかしい、、、 -

私もちょっと調べましたが、OpenTextのFieldInfoはテキストファイルのみ有効のようですね。
CSVファイルの出力を変更できるようであれば、数字文字列のダブルクォートのまえにイコールを入れたデータでOpen関数で開けばそれなりに見れるようになります。

="001","b","c","d[CR][LF]
e[CR][LF]
f[CR][LF]
g","h"

Workbooks.Open fileName:=取り込みたいファイルの名称

編集 履歴 (0)
  • 回答ありがとうございます。CSVファイルの内容は変更できないため、他の方が回答してくださったADOを使用することにしました。 -
ウォッチ

この質問への回答やコメントをメールでお知らせします。