Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub mergeCSV()
- Dim csvDir As String
- 'ディレクトリの指定
- With Application.FileDialog(msoFileDialogFolderPicker)
- If .Show = True Then
- csvDir = .SelectedItems(1)
- Else: End
- End If
- End With
- '出力用ワークブックを作成
- Dim wb As Workbook
- Set wb = Workbooks.Add
- 'ディレクトリ内のファイルを順に開き内容を新しいブックにコピーする
- Dim fileName As String
- fileName = Dir(csvDir & Application.PathSeparator & "*.csv")
- Do While fileName <> ""
- 'csvファイルの内容をブックにコピー
- Call copyCsvToBook(csvDir & Application.PathSeparator & fileName, wb)
- fileName = Dir()
- Loop
- End Sub
- 'csvファイルの内容をブックにコピー
- Function copyCsvToBook(csvPath As String, wb As Workbook)
- Dim r_num As Long, c_num As Long
- Dim strLine As String
- Dim arrLine() As String 'カンマでsplitして格納
- Dim n As Integer
- r_num = wb.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
- '文字化け対策として文字コードをutf-8にするためADODB.Streamオブジェクトを生成
- Dim adoSt As Object
- Set adoSt = CreateObject("ADODB.Stream")
- With adoSt
- .Charset = "UTF-8"
- .Open
- .LoadFromFile (csvPath)
- Do Until .EOS 'Streamの末尾まで繰り返す
- strLine = .ReadText(adReadLine) 'Streamから1行取り込み
- strLine = replaceDelimiter(strLine, ",", "|") 'データ中にカンマが存在するため区切り文字を"|"に置換
- arrLine = Split(Replace(strLine, """", ""), "|") 'strLineを|で区切りarrLineに格納
- For c_num = 0 To UBound(arrLine)
- wb.Worksheets(1).Cells(r_num, c_num + 1).Value = arrLine(c_num)
- Next c_num
- r_num = r_num + 1
- Loop
- .Close
- End With
- End Function
- '区切り文字を置換する
- Function replaceDelimiter(ByVal strLine As String, ByVal replacedDel As String, ByVal newDel As String) As String
- Dim strTemp As String
- Dim quotCount As Long
- Dim pos As Long
- For pos = 1 To Len(strLine)
- '1文字ずつ切り出し判定する
- strTemp = Mid(strLine, pos, 1)
- 'strTempがダブルクォーテーションなら
- 'ダブルクォーテーションのカウントを1増やす
- If strTemp = """" Then
- quotCount = quotCount + 1
- 'strTempが対象の区切り文字、且つダブルクォーテーションのカウントが2の倍数なら
- '新たな区切り文字に置き換える
- ElseIf strTemp = replacedDel Then
- If quotCount Mod 2 = 0 Then
- strLine = Left(strLine, pos - 1) & newDel & Right(strLine, Len(strLine) - pos)
- End If
- End If
- Next pos
- replaceDelimiter = strLine
- End Function
Add Comment
Please, Sign In to add comment