EXCELにcsv形式の外部データを取り込む際にカンマ区切りで取り込も

質問

EXCELにcsv形式の外部データを取り込む際にカンマ区切りで取り込もうとした場合、csvデータの同一セル内に改行データがあると、ずれて表示されてしまいます。
改行を取り除くのはclean関数があることは知っていますが、マクロを利用して、EXCELのデータ→外部データの取り込みの選択だけで取り込む方法はないでしょうか？ご教示ください。

回答No.5

end-u · ベストアンサー

Sub try()
　　Dim WRK As String '一時作業ファイル名。削除するので既存ファイル名と重複させないように。
　　Dim buf As String
　　Dim tmp As String
　　Dim n　 As Long
　　Dim f

　　'読込みファイルを選択
　　f = Application.GetOpenFilename("csv,*.csv")
　　If VarType(f) = vbBoolean Then Exit Sub
　　'作業ファイル名設定
　　WRK = Application.DefaultFilePath & "\temporary.csv"
　　'読込みファイルOpen
　　n = FreeFile
　　Open f For Input As #n
　　buf = StrConv(InputB(LOF(n), #n), vbUnicode)
　　Close #n
　　'置換（セル内改行を半角スペースに）
　　With CreateObject("VBScript.RegExp")
　　　　.Global = True
　　　　.Pattern = "([^\r])(\n+)"
　　　　buf = .Replace(buf, "$1 ")
　　End With
　　'作業ファイルOpen
　　n = FreeFile
　　Open WRK For Output As #n
　　Print #n, buf
　　Close #n
　　'[外部データの取り込み]
　　With Sheets.Add
　　　　With .QueryTables.Add(Connection:="TEXT;" & WRK, _
　　　　　　　　　　　　　　　Destination:=.Range("A1"))
　　　　　　.AdjustColumnWidth = False
　　　　　　.TextFileParseType = xlDelimited
　　　　　　.TextFileTextQualifier = xlTextQualifierDoubleQuote
　　　　　　.TextFileCommaDelimiter = True
　　　　　　.Refresh BackgroundQuery:=False
　　　　　　.Delete
　　　　End With
　　End With
　　'作業ファイル削除
　　Kill WRK
End Sub

Excelで作成したcsvファイルで、セル内改行が含まれている場合は上記で読み込めるはずです。
そのcsvファイルをテキストエディタで編集してしまった場合は、うまくいかなくなるかもしれません。

'[外部データの取り込み]...以降、 Kill WRK までのコードは、無くても構いません。
取り込み前にセル内改行を置換してしまえば、
あとは手作業ででも[外部データの取り込み]ができます。

お礼

ありがとうございました。
標準モジュールに上記を貼りつけて、うまく読み込むことができました。
VBAの知識が乏しいので頼ってしまいましたが、これを機に勉強して習得したいと思います。
本当にありがとうございました。

回答No.7

Wendy02

何年後しのせっかくの回答だったのに、そのままにしてしまうのは残念ですから、私なりの解釈で回答を入れておきます。

VBAのポイントは、
　'*ここで、Lf を抜く(Excelのセル内改行-Chr(10)）
　buf = Replace(buf, vbLf, "")

この部分です。もし、該当するものがありましたら、ここに加えてください。
ここで、ひとつずつ不要コードを抜きますが、vbLf かvbCr のどちらかは残してください。

Clean 関数ですと、改行コードもなくなって一行になってしまいますので、それは使えません。

私の記憶では、Binary でインポートすると、StrConv の変換が必要になるということだったと思いますので、
それを省きました。元のファイル名の末尾に、ファイル名$.csv　というのが、変換されたファイルです。
本当は、QueryTablesは、使わなくても出来ますが、ご要望なので、入れておきました。

ただし、ファイルの種類によっては、Cr+Lf で、Lf が落ちてしまい、内容が変わってしまう可能性がありますので、
上手くいかなかったら、その後に、
　　buf = Replace(buf, vbCr, vbCrLf)
と加えることで、標準改行コードに戻るはずです。考え方は、単純だと思います。

'//
Sub MyAnswer()
Dim fName As String
Dim bkf As String
Dim fNum As Integer
Dim fbk As String
Dim buf As Variant
　fName = Application.GetOpenFilename("csvファイル(*.csv),*.csv")
　If VarType(fName) = vbBoolean Or fName = "" Then Exit Sub
　fbk = Mid(fName, 1, InStrRev(fName, ".") - 1) & "$.csv"
　fNum = FreeFile()

　Open fName For Input As #fNum
　buf = Input(LOF(fNum), #fNum)
　Close fNum

　'*ここで、Lf を抜く
　buf = Replace(buf, vbLf, "")

　fNum = FreeFile()
　Open fbk For Output As #fNum
　Print #fNum, buf
　Close #fNum
　　'Sheet の挿入
　　 With Sheets.Add(After:=Sheets(Sheets.Count))
　　　With .QueryTables.Add(Connection:= _
　　　　"TEXT;" & fbk, Destination:=Range("A1"))
　　　　.RefreshOnFileOpen = False
　　　　.RefreshStyle = xlOverwriteCells　'上書き設定
　　　　.AdjustColumnWidth = False
　　　　.TextFilePlatform = xlWindows
　　　　.TextFileStartRow = 1
　　　　.TextFileParseType = xlDelimited
　　　　.TextFileSpaceDelimiter = True
　　　　.TextFileConsecutiveDelimiter = True
　　　　.TextFileTextQualifier = xlTextQualifierDoubleQuote
　　　　.TextFileTabDelimiter = True
　　　　.TextFileCommaDelimiter = True
　　　　.Refresh BackgroundQuery:=False
　　　　.Delete
　　End With
　End With
　''Kill fbk 'テンポラリファイルの削除
End Sub

お礼

ありがとうございます。
質問を締め切ってしまった後にもかかわらず、ご投稿下さいまして感謝申し上げます。
上記のコードを標準モジュールに貼り付けましてうまく取り込むことができました。
私はVBAの基本的な用語もままならない状態ですので、用語や構造を勉強してコードを理解し、作成できるようになりたいと思っております。
締め切ってしまったためポイントを付与できない状態になってしまい申し訳なく思っております。
本当にありがとうございました。

回答No.6

layy

＞csvの2つ目を取り込む前に加工して改行をなくしてから取り込むのでは手間がかかる

ＶＢＡが十分にできない状態ではこの手作業のが確実で早いのでは？と思います。

＞clean関数があることは知っています

この関数を見つける時間をＶＢＡを習得する時間に費やせたらと思います。

ＶＢＡでは
Ａファイルを読み込みＢファイルへ編集して出力する、
Ｂファイルを読み込みシート０１へ値をセットする、
シート０１から編集してシート０２へ値をセットする、
シート０２から編集してＣファイルへ出力する、
などシート操作はもちろんファイル操作もできますから、
これまでの関数頼りより対応がかなり広くなり、
極論言えば質問しなくても済む程度になります。

今回はすぐできないにしても、これを機にまた取り組むと良いです。
ここの回答ＶＢＡを実行させるくらいは覚えておくべきです。
ＶＢＡを習得するにはどうしたら？という質問も出ているくらいですから。

今回の、データはどんな具合になっていますか？。

フリーソフトで「改行だけ取り除く」ものは探してみましたか？。

お礼

フリーソフトで探してはおりませんでした。
今回Ｎｏ５さんの方法でうまく取り込むことができましたが、これを機にVBAをきちんと勉強したいと思います。
ありがとうございました。

回答No.4

Wendy02

#3の回答者です。
>マクロの作成後にコードを編集するという方法しかできない程度のレベルで

それで十分だと思います。掲示板の回答者の人たちの対応は、それぞれでも、ある程度のことは、それなりに満足させるまで、付き合うはずです。今まで、VBAの質問は、途中で分からなくなって、投げ出していたのではありませんか？そのようにお見受けしているからです。

VBAは、基本的に、Alt + F11 で開いたら、標準モジュールを挿入して、そこに貼り付けて、画面を締めて、フォーム・ツールボタンをつけるなり、Alt + F8 で、選んで実行すれば良いということさえすればよいのですね。分からないことは、分からないで、ここを教えて欲しいと、具体的に聞けばよいのですが、あまり漠然としたものは、回答には難しいです。いままで、VBAの質問は、こちらのチェックでは、どれも成功していないように思ったからです。投げ出さないで成功に結びつけてください。

今回の場合は、

------
もとの、CSV が、

123,456,"bb
b"

こうなっているのではないでしょうか？

それを、

123,456,bbb

このように直す、というように理解いているけれども、それはどうなんでしょうか？ということで、それをVBAのコードで作るというわけです。
-------
それ自体は、VBAでは大したことがありませんが、Clean 関数はあくまでも、ワークシートの関数ですから、それなりに方法がある、ということです。

お礼

>今まで、VBAの質問は、途中で分からなくなって、投げ出していたのではありませんか？そのようにお見受けしているからです。
★おっしゃる通りです。ですので私でもできそうな回答を選んで使用させていただいておりました。
VBAのコードを見ても自分のファイルにあてはめた時にどこをどう直せばよいのかわからず諦めていたのです。

今回ご質問させていただいた理由は複数のcsvファイルデータをA4サイズ縦1枚の表示にしたいからです。
仕事でこちらからお客様に提示した項目に入力していただいたものをcsv形式で媒体に保存して頂き、提出していただいております。それを見やすいようにしたいと思っております。csvファイルは1行目に項目名、2行目に内容が入っております。csvファイルは2つです。
しかし、その状態では見づらいし内容確認に時間がかかるので見やすくするためにexcelで取り込んで（sheet1にはcsvの1つ目、sheet2にはcsvの2つ目を取り込む）、sheet3にレイアウトして表示しようと考えました。
csvの1つ目は文章的なものがないので同一セル内に改行がないため、excelにとりこんでも同じレイアウトで表示されます。しかしcsvの2つ目をexcelにとりこんだところ、文章を入力するセルで改行されたものが、改行以降から別の場所（A列）に表示されてしまったため目的が達成できないでおります。検索したところclean関数で改行をなくすことができるところまではわかったという状況です。外部データの取り込みはマクロの記録で自動的にできるようになったのですが、改行によって分断されるところが解決されればと思いました。csvの2つ目を取り込む前に加工して改行をなくしてから取り込むのでは手間がかかるし、私以外の複数の人間が簡単に作業ができればと思ったのです。
それは業者に頼めば済むことではないかと思われるかもしれませんが予算の関係か後回しにされてシステム化の目処が立っていないのです。ご質問のcsvの構造がどうなっているかは明日にならないとわかりませんので確認いたします。

回答No.3

Wendy02

もとの、CSV が、

123,456,"bb
b"

こうなっているのではないでしょうか？

それを、

123,456,bbb

にするということですか？確認してください。Clean 関数は使っても、除去できないと思います。

>マクロを利用して、EXCELのデータ→外部データの取り込みの選択だけで取り込む方法はないでしょうか？
マクロなら、外部データの取り込みの選択自体を、メニューからの取り込みでしたほうが楽です。外部データの取り込みは、テーブルオブジェクトを残してしまいます。

それと、ご質問者さんの過去の質問で、VBAの質問をしながら、私に対する返事を含めて、VBAの回答はきちんと対応がなされていませんが、今回は、VBAの回答は大丈夫なのですか？今度、ダメでしたというのでは、一部の人には覚えられてしまいます。2年も3何年も前とは言いながらも、どこかでご自身のVBA（マクロ）の質問に対してはケジメは必要だと思います。

お礼

申し訳ございません。
VBAの構造というのがほとんど分かっておらず、マクロの作成後にコードを編集するという方法しかできない程度のレベルで安易に質問をしてしまいました。
VBAに関する質問は勉強してわかるようになってから致します。
ありがとうございました。

回答No.2

layy

想定した形式でない以上、何かプログラムするかデータ補整しないとできないと思った方が良いです。

ＶＢＡで取り込みとする方法はどうですか。これならできると思います。

こうなるとＣＳＶをどう作ったかも怪しいしTABとかも気になりますね。

回答No.1

imogasi

>EXCELのデータ→外部データの取り込みの選択だけで取り込む方法
そこまでMSのエクセルの機能を利用するなら、セル内改行（のコード文字）だけを取り除く操作（ダイアロウグボックスなどの選択肢）が無い以上、どうしようもないのでは。
なぜそんなことを改めて聞くのかな。
（VBAでも勉強して）自前で、CSVファイルの各行（レコード）を順次読んで、セル内改行のコードは削除し、書き出し他ファイルを作ってしまうとか、
レコードを読み込んだときに、セル内改行コードを取り除き、項目（列・フィールド）ごとにセルにセットしていけば仕舞いでしょう。１０数行のコードで出来ることだ。
先所のためには
Instr関数で捉えられる
そのままシートに読み込んでからChar(10)を空白に置換する方法もあるのでは。
それはChar(10)はCTRL+Jで捉えられること
http://officetanaka.net/excel/function/tips/tips09.htm
を読むこと。Googleで「セル内改行　削除」で照会してみたらこういうのも直ぐ見つかる。