|
▼kosuke さん:
こんにちは。
>列数は、287です。今回質問のデータは、22列にありましたが、
>今後、他の列にも発生する可能性があります。
>また、" "内に,があったり、なかったりします。
Input#ステートメントで 1項目ずつ読んでみました。
なので、超低速です。
Sub 超256列CSV読み込み()
Dim myCSV As String
myCSV = Application.GetOpenFilename("287列CSV,*.csv")
If myCSV = "False" Then Exit Sub
'------- テキストファイルの全行数取得
Dim io As Integer
Dim buf() As Byte
Dim maxRow As Long
Dim maxCol As Long
Dim i As Long, j As Long, n As Long
io = FreeFile()
Open myCSV For Binary As io
n = LOF(io)
ReDim buf(1 To n)
Get #io, , buf
Close io
maxRow = UBound(Split(StrConv(buf, vbUnicode), vbCrLf))
'------- テキストファイルの列数取得
For i = 1 To n
If buf(i) = 13 Then
If buf(i + 1) = 10 Then
maxCol = i + 1
Exit For
End If
End If
Next
Dim S As String
Open myCSV For Input As io
j = 0
Do While Seek(io) < maxCol
j = j + 1
Input #io, S
Debug.Print j, S
Loop
Close io
maxCol = j
'------- 配列に読み込み
ReDim v(1 To maxRow, 1 To maxCol)
Open myCSV For Input As io
For i = 1 To maxRow
For j = 1 To maxCol
Input #io, v(i, j)
Next
Next
Close io
'------- シートに展開
Dim nOld As Long
Dim nSheet As Long, maxSheet As Long
Dim m As Long, mz As Long, mCol As Long
Const mm = &H100 'シート最大列数
Dim v2()
maxSheet = maxCol \ mm - (maxCol Mod mm > 0)
With Application
nOld = .SheetsInNewWorkbook
.SheetsInNewWorkbook = maxSheet '◆必要なシート枚数のBookを
.Workbooks.Add '新規追加
.SheetsInNewWorkbook = nOld
End With
For m = 1 To maxCol Step mm
nSheet = nSheet + 1
mz = m + mm - 1
If mz > maxCol Then mz = maxCol
mCol = mz - m + 1
If nSheet = 1 Then
Worksheets(1).Cells(1).Resize(maxRow, mCol).Value = v
Else
ReDim v2(1 To maxRow, 1 To mCol)
For i = 1 To maxRow
For j = 1 To mCol
v2(i, j) = v(i, m + j - 1)
Next
Next
Worksheets(nSheet).Cells(1).Resize(maxRow, mCol) _
.Value = v2
End If
Next
MsgBox maxSheet & "つのシートに読み込みました"
End Sub
|
|