質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.46%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

3回答

9638閲覧

VBA CSVを取り込む際に、ダブルクォーテーション内のカンマを削除なり、置換をしたいです

dmg

総合スコア8

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

1クリップ

投稿2021/09/21 04:48

編集2021/09/22 05:05

CSVを取込み、エクセルシートに表示をすると値がずれてしまいます。
以下のコードを使用して取り込みましたが項目内のカンマで値がずれてしまいます・・
ダブルクォーテーション内のカンマを削除なり、置換をしたいです
replace等も使用しましたが、上手くいきません
ご教示いただけると幸いです

VBA

1Sub csv_import2() 2 File_path = Worksheets("Sheet3").Range("C3").Value 3 Debug.Print (File_path) 4 5 ' ファイル読み込み 6 MsgBox "ファイルを選択してください" 7 Dim buf As String, Target As String, i As Long 8 Dim records As Variant, items As Variant, j As Long 9 Dim Csv_Import_File ' Excelファイルに取り込むCSVファイルの名前を入れ込む変数 10 Csv_Import_File = Application.GetOpenFilename("CSVファイル,*.csv") ' CSVファイルを選択する 11 12 13 With CreateObject("ADODB.Stream") 14 .charset = charset 15 .Open 16 .LoadFromFile Csv_Import_File 17 buf = .ReadText 18 .Close 19 records = Split(buf, "#") 20 headers = Split(records(0), ",") 21 22 ' header書き出し 23 For j = 0 To UBound(headers) 24 Worksheets(targetsheet).Cells(1, j + 1).Value = headers(j) 25 Next j 26 27 For i = 1 To UBound(records) 28 items = Split(records(i), ",") 29 For j = 0 To UBound(items) 30 If j = 0 And i <> 0 Then 31 Worksheets(targetsheet).Cells(i + 1, j + 1).Value = "#" + items(j) 32 Else 33 Worksheets(targetsheet).Cells(i + 1, j + 1).Value = Replace(items(j), """", "") 34 Worksheets(targetsheet).Cells(i + 1, j + 1).Value = Replace(items(j), " ", "") 35 End If 36 Next j 37 Call write_target(i, items, headers) 38 Next i 39 End With 40 41 Set ws = ThisWorkbook.Worksheets(targetsheet) 42 Call writeCsv(ws, ws.Range("A1")) '//ファイル保存 43End Sub
<CSV> ``` Name,Email,Financial Status,Paid at,Fulfillment Status,Fulfilled at,Accepts Marketing,Currency,Subtotal,Shipping,Taxes,Total,Discount Code,Discount Amount,Shipping Method,Created at,Lineitem quantity,Lineitem name,Lineitem price,Lineitem compare at price,Lineitem sku,Lineitem requires shipping,Lineitem taxable,Lineitem fulfillment status,Billing Name,Billing Street,Billing Address1,Billing Address2,Billing Company,Billing City,Billing Zip,Billing Province,Billing Country,Billing Phone,Shipping Name,Shipping Street,Shipping Address1,Shipping Address2,Shipping Company,Shipping City,Shipping Zip,Shipping Province,Shipping Country,Shipping Phone,Notes,Note Attributes,Cancelled at,Payment Method,Payment Reference,Refunded Amount,Vendor,Outstanding Balance,Employee,Location,Device ID,Id,Tags,Risk Level,Source,Lineitem discount,Tax 1 Name,Tax 1 Value,Tax 2 Name,Tax 2 Value,Tax 3 Name,Tax 3 Value,Tax 4 Name,Tax 4 Value,Tax 5 Name,Tax 5 Value,Phone,Receipt Number,Duties,Billing Province Name,Shipping Province Name,Payment Terms Name,Next Payment Due At #1041,addres,pending,,unfulfilled,,yes,JPY,5500,0,0,5500,"",0,送料,2021-07-21 20:45:23 +0900,1,AAAA N,5500,,062906810,true,false,pending,name,**"Tokyo3-369, Tokyo2F"**,Tokyo3-369,Tokyo4F,,江東区,107-0000,JP-13,JP,'+81 00-0000-0000,00000000 : 111 ,"指定: なし 指定: 指定なし",,Ca"",0,A,30,,,,42348,"",Low,web,0,,,,,,,,,,,,,,東京都,東京都,,
"Tokyo3-369, Tokyo2F" こちらの個所からずれが発生いたします ### 対応したこと ```VBA For i = 1 To UBound(records) items = Split(records(i), ",") For j = 0 To UBound(items) If j = 0 And i <> 0 Then Worksheets(targetsheet).Cells(i + 1, j + 1).Value = "#" + items(j) Else ' Worksheets(targetsheet).Cells(i + 1, j + 1).Value = Replace(items(j), """", "") Worksheets(targetsheet).Cells(i + 1, j + 1).Value = Split(Replace(replaceColon1(strLine), """", ""), ":") End If Next j Call write_target(i, items, headers) Next i
End With

やったこと

VBA

1Dim buf As String, Target As String, i As Long 2 Dim records As Variant, items As Variant, j As Long 3 Dim Csv_Import_File ' Excelファイルに取り込むCSVファイルの名前を入れ込む変数 4 Csv_Import_File = Application.GetOpenFilename("CSVファイル,*.csv") ' CSVファイルを選択する 5 6 MsgBox "先程選択した、データを選択してください" 7 With CreateObject("ADODB.Stream") 8 .charset = "Shift-JIS" 9 .Open 10 .LoadFromFile Csv_Import_File 11 buf = .ReadText 12 .Close 13 records = Split(buf, "#") 14 headers = Split(records(0), ",") 15 16 ' header書き出し 17 For j = 0 To UBound(headers) 18 Worksheets(targetsheet).Cells(1, j + 1).Value = headers(j) 19 Next j 20 21 'カンマをセミコロンに置換 22 buf = ReplaceSeparator(buf, ",", ";") 23 buf = Replace(buf, """", "") '引用符(")を削除 24 25 records = Split(buf, "#") '#で分割して配列に 26 For i = 0 To UBound(records) 27 If i > 0 Then records(i) = "#" & records(i) 28 items = Split(records(i), ";") 'セミコロンで分割して配列に 29 '配列をセル範囲に代入 30 Worksheets(1).Cells(i + 1, 1).Resize(, UBound(items) + 1).Value = items 31 Next 32 End With

教えていただいたURL参考に行いましたが、CSVの値がすべて消えてしまいます。
recordsの中身がない状態です
リンク内容

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hex309

2021/09/21 05:19

どこでずれているかも提示いただけないでしょうか? 回答者は「正解」がわからないので、そのあたりを提示いただくと、より回答がつきやすいかと思います。
dmg

2021/09/21 05:24

失礼いたしました。 "Tokyo3-369, Tokyo2F" こちらの項目内のカンマからずれが発生いたします。
guest

回答3

0

ADODB.Streamを使って読み込んでいるということは、CSVファイルがShift-JISではないということでしょうか。UTF-8とか。

だとしたら、[データ]-[データの取得と変換]-[テキストまたはCSVから] で取り込めます。

Office TANAKA - 取得と変換[別ファイルのデータを読み込む]

取りこむときに文字コードも指定できますので、指定して取り込んでみてください。
CSVの仕様にそったファイルなら問題なく取り込めると思います。
ダブルクォーテーションで囲まれたカンマや改行は無視してくれます。

上記で正しく取り込めたら、マクロの起動で、同じ操作をしてVBAに変換して、それをもとに作成したらどうでしょう。


ファイルがCSVの仕様に沿ったものなら、上記の方法でうまくいくはずなんですが、
自前のコードでいくなら、下記のような方針でいくといいでしょう。

読み込んだテキスト全文のカンマを別の区切り文字(テキストに含まれていない文字)に置換する。ただし、引用符(")に囲まれている部分のカンマは置換しない。(自作関数)

その後、引用符(")を削除する(必要ならば)。

後は、テキストをレコード区切り文字で分割して配列にする。
今回のものだと行頭に#があるのでそれをレコード区切り文字とする。

レコード配列をループで順に読み込んで、置換した区切り文字で分割して配列にして、その配列をセル範囲に代入する。

上記の方針で作成したコード例

vba

1Sub csv_import3() 2 ’前略 ファイル読み込み処理 3 4 Dim buf As String 5 buf = .ReadText 6 7 'カンマをセミコロンに置換 8 buf = ReplaceSeparator(buf, ",", ";") 9 buf = Replace(buf, """", "") '引用符(")を削除 10 11 Dim records, i As Long 12 records = Split(buf, "#") '#で分割して配列に 13 For i = 0 To UBound(records) 14 If i > 0 Then records(i) = "#" & records(i) 15 Dim items 16 items = Split(records(i), ";") ’セミコロンで分割して配列に 17 ’配列をセル範囲に代入 18 Worksheets(1).Cells(i + 1, 1).Resize(, UBound(items) + 1).Value = items 19 Next 20 21 '後略 22End Sub 23 24'引用符(")で固まれた部分以外の区切り文字を置換する 25Function ReplaceSeparator(s As String, Separator1 As String, Separator2 As String) As String 26 Dim ary 27 ary = Split(s, """") 28 29 Dim i As Long 30 For i = 0 To UBound(ary) Step 2 31 ary(i) = Replace(ary(i), Separator1, Separator2) 32 Next 33 34 ReplaceSeparator = Join(ary, """") 35End Function

投稿2021/09/21 06:08

編集2021/09/21 15:31
hatena19

総合スコア33795

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

dmg

2021/09/21 06:41

hatena19様、前回もご教示いただきありがとうございました。 また説明が足りず申し訳ございません。 おっしゃる通りShift-JISではなくUTF8のCSVファイルです マクロの記録でしょうか? 記録であれば試してみたのですが上手くいかなかったです。。
hatena19

2021/09/21 09:49

Office TANAKAさんのページの手順通りに進めて、文字コードをUTF8に指定して取り込んでもうまくいきませんでしたか。 それでうまくいったらその手順をマクロの記録でVBAに変換です。
dmg

2021/09/21 11:41

ありがとうございます。 取込自体は上手くいくのですが、マクロの記録のものを、そのまま使用するとエラーが起きてしまいます。。 また、どうしても上記コード必須なのでコードを変えずにの方法を模索中です。
dmg

2021/09/21 15:59

hatena様、今回もコードのご提示ありがとうございます。 <やったこと>に記載しました。 このように書いてみたのですが、CSVの値は空っぽでした、、 シートに書き込まれない? 私の書き方がいけなかったのでしょうか。(Dimは自分のコードと重複していたので省いております) お手を煩わせて申し訳ございませんが再度ご教示いただければ幸いです。 いつもありがとうございます。
hatena19

2021/09/22 00:48

提示したのはコード例で合って、そのままコピーして正しく動作するとは限りません。内容を理解して、そちらの状況にあわせて書き換える必要があります。 例えば、 Worksheets(1).Cells(i + 1, 1).Resize(, UBound(items) + 1).Value = items 上記の部分は、最初のワークシートに出力してますが、ここは出力したいワークシートになるように辺ウする必要があります。 あと、UTF8のファイルとのことですが、 .charset = "Shift-JIS" としているのはどういうことでしょう。 こちらでサンプルを作成してテストして正しく動作するのは確認済みですので、内容を理解して、状況に合うように書き換えれば動作するはずですので、頑張ってください。
dmg

2021/09/22 05:04

>あと、UTF8のファイルとのことですが、.charset = "Shift-JIS" Shift-JIsのも読込みたかったので途中で変えてしまいました。 申し訳ございません。。 内容を理解できるよう頑張ります。 また考えを改めて、クエリも再度試しましたが、最初に選んだファイルのみの選択になってしまいます。 できればダイアログで選択したファイルをシートに出力できればいいのですが、、
guest

0

ベストアンサー

VBA

1Sub sample() 2 Dim buf As String, Target As String, i As Long 3 Dim records As Variant 4 Dim Csv_Import_File ' Excelファイルに取り込むCSVファイルの名前を入れ込む変数 5 Csv_Import_File = Application.GetOpenFilename("CSVファイル,*.csv") ' CSVファイルを選択する 6 7 With CreateObject("ADODB.Stream") 8 .Charset = "UTF-8" 9 .Open 10 .LoadFromFile Csv_Import_File 11 buf = .ReadText 12 .Close 13 End With 14 15 'カンマをタブに置換 16 CommaToTab buf 17 18 records = Split(buf, "#") 19 20 Dim ws As Worksheet 21 Set ws = ActiveSheet 22 23 'header 24 Dim rng As Range, arr As Variant 25 arr = Split(records(0), vbTab) 26 Set rng = ws.Range("A1").Resize(, UBound(arr) + 1) 27 rng.Value = arr 28 29 'data 30 For i = 1 To UBound(records) 31 arr = Split("#" & records(i), vbTab) 32 rng.Offset(i).Value = arr 33 Next 34 35End Sub 36 37 38Function CommaToTab(ByRef s As String) 39'カンマをタブに変換(ダブルクォーテーション内のカンマは変換しない) 40 Dim l As Long, m As String, q As Boolean 41 For l = 1 To Len(s) 42 m = Mid(s, l, 1) 43 If m = """" Then q = Not q 44 If m = "," And Not q Then s = WorksheetFunction.Replace(s, l, 1, vbTab) 45 Next l 46 s = Replace(s, """", "") 47End Function

投稿2021/09/22 05:01

jinoji

総合スコア4585

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

dmg

2021/09/22 08:38

こちらで実行できました! 今回もありがとうございます。
guest

0

ダブルクォーテーション内のカンマは無視したい、ということでしょうか。
それであれば、以下が参考になるかと

https://tonari-it.com/vba-csv-camma/

投稿2021/09/21 05:17

hex309

総合スコア761

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

dmg

2021/09/21 05:34 編集

>ダブルクォーテーション内のカンマは無視したい、ということでしょうか。 おっしゃる通りです。 参考URLありがとうございます。 こちら私も参考にしたのですが、今のコードのどこに手を加えていいか分かっておりません。。
hex309

2021/09/21 05:33

ポイントは、カンマをすべて置換するのではなく、ダブルクォーテーション内のカンマ以外を置換する、という点です。 ご紹介したURLでは「:(コロン)」に置換しているかと
dmg

2021/09/21 05:39

置換するというのも分かってはいるのですが、そのコードを私が書いているコードのどこにいれるか模索中です
hex309

2021/09/21 07:09 編集

ご自分の書かれているコードはきちんと理解されていますか? レコードを転記する際にカンマごとにデータを区切っていますが、その部分かと。 実際に試してみていただいて、うまくいかないようでしたら、そのコードを提示してみてください。
dmg

2021/09/21 07:25 編集

すみません、きちんと理解はしていないです。 hex309様のおっしゃる通りその部分に手を加えておりますが、欲しい結果がでません。 エラーは出ていないですが、欲しい結果が出ません
hex309

2021/09/21 07:37

以下の部分は何をしているかわかりますか? For i = 1 To UBound(records) items = Split(records(i), ",")
dmg

2021/09/21 07:53 編集

上記のものは値をカンマ区切りすることでしょうか そちらも試しましたが Split(Replace(replaceColon1(strLine), """", ""), ":")ここを参考に Split(Replace(replaceColon1(records(i)), """", ""), ":") コンパイルエラーとなってしまいます 「Sub またはFunctionがしていされていません」 本当にお恥ずかしながら、どう現コードに繋げるのか分かっておりません お手を煩わせてしまい、申し訳ございません。
hex309

2021/09/21 08:01

参考サイトにある、replaceColon1プロシージャはコピーされましたか? それがないと、上記のようなエラーになります。
dmg

2021/09/21 08:11

はい、replaceColon1プロシージャはコピーしました。 その中のコードの str = Left(str, l - 1) & ":" & Right(str, Len(str) - l) '現在の1文字をコロンに置き換える こちらの変数等も現コードに合わせて変えないといけないのかもしれないですが、その方法が分かりません、、
hex309

2021/09/21 08:23

私も見落としましたが、よく見ると呼び出し側は「replaceColon1」で、呼び出される側は「replaceColon」ですね。名前をそろえてください。
dmg

2021/09/21 08:44

できました! しかし、2021-07-21 20:45:23 +0900の部分が分かれるようになってしまったので 値のずれが変わらずです・・
hex309

2021/09/21 08:49 編集

それはそうでしょう。カンマの代わりに「:」を使ったのが提示したURLでの処理ですから。 カンマの代わりに使う記号は、元のCSVファイルでは使用しない記号にしてください。 あと、ずれるという「Tokyo3-369,Tokyo2F」ですが、提示いただいたCSVファイルのデータだと、そのあとの「Tokyo3-369,Tokyo4F」はダブルクォーテーションで囲まれていないので、分割されますが、それであっていますか? どうも、いただいたCSVにも問題があるように見えますが、いかがですか?
dmg

2021/09/21 11:39

>カンマの代わりに「:」を使ったのが提示したURLでの処理ですから。 他の記号で、;セミコロンや?を使用しましたが、そうするとデータがA1にまとめられてしまいます。。 その後のTokyo3-369,Tokyo4はあっております。
hex309

2021/09/21 13:02

修正して使用されたとのことですが、修正箇所は2箇所あるはずです。2箇所とも直されましたか?
dmg

2021/09/22 05:01

items = Split(records(i), ",") この箇所だけでなくとのことですよね、、 直しておりません。 本当にお恥ずかしながらよくわかっていないです、、
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.46%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問