[[20150410101623]] 『csvファイルを開かずに、xlsxに変える方法』(ゆ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『csvファイルを開かずに、xlsxに変える方法』(ゆ)

260MBのcsvファイルがあり、開くのに数分必要です

一度開いて、xlsxで保存し
xlsxで開くと、少し遅いかな程度で開いてきます
 (csvの方がデータのみなので、軽いはずと認識していますが
 今回の場合はcsv出力してもらったときに、データの情報?も持たせてしまっているのかな?と
 思っています、詳しくはわかりません)

なので、csvをxlsxに自動変換できるようなものを
VBAで作成しようと考えております

しかし、vbaであっても一度はcsvファイルを開かないといけないため
効果が出にくそうです

csvファイルを開かずに、xlsxに変える方法はあるのでしょうか?

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 CSVだとEXCELがデータの種別を判断して変換する処理が入るのでそれに時間がかかっているのではないか?

 データタブの「外部データの取り込み」-「テキストファイル」で取り込んで時間がどうなるか確認してみてくれないか?
 (拡張子をTXTに変換してファイル-開くでテキストを選ぶのでもいいが)
(ねむねむ) 2015/04/10(金) 10:51

 ねむねむさんありがとうございます
 教えて頂いた「データタブのテキストファイルで取込」を試してみました

 結果
 時間は、csvと変わらず遅かったです

 他に確認できることなどありましたら教えて下さい
(ゆ) 2015/04/10(金) 11:15

 大昔、拡張子をTXTにしてから読み込んだらかなり早くなった記憶がある。

 ので、検索してみたらでてきた。

[[20090113172518]] 『VBAでCSVを開く際の速度』(ポム) >>BOT

 VBAでやるなら参考になるんじゃないかな
(1111) 2015/04/10(金) 11:26

物理的に、ファイルを開かず中身を得るのは無理として…。
ダブルクォートで囲まれた中にカンマがあるような場合を全く考慮しない例。

Excel自身のインポート機能より速いかどうかは不明。200M程メモリ確保できるかも不明。
データに漢字は無いとか、固定長だとか、制限された条件があれば高速化の余地はありますが。

 Sub test()
    Const CFILE = "C:\test\test.csv"
    Dim F1 As Integer
    Dim bDim() As Byte
    Dim vDim1 As Variant
    Dim vDim2 As Variant
    Dim i As Long

    ReDim bDim(FileLen(CFILE) - 1)
    F1 = FreeFile
    Open CFILE For Binary As #F1
    Get #F1, , bDim
    Close #F1

    Application.ScreenUpdating = False
    vDim1 = Split(StrConv(bDim, vbUnicode), vbCrLf)
    For i = 0 To UBound(vDim1) - 1
        vDim2 = Split(vDim1(i), ",")
        Cells(i + 1, 1).Resize(, UBound(vDim2) + 1) = vDim2
    Next i
    Application.ScreenUpdating = True
 End Sub
(???) 2015/04/10(金) 12:46

 ???さんありがとうございます

 頂いた記述で実行してみましたところ
 メモリ不足にて動きませんでした

 1111さんありがとうございます
 リンク先のseiyaさんの書かれてある記述をお借りしてみました

 Sub test()
  Dim fn As String, delim As String, temp As String, myColumns
  Dim x, y, a() As String, i As Long, ii As Long
  myColumns = VBA.Array(1,3)  '<- 取り出したい列
  fn = "c:\test.csv"     '<- ファイルパス 

 しかし、ここ↓で随分と時間がかかり(最終開くまでの確認はしていませんが…)
 temp = CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll
 使えていない状態です

 いったん、状況報告のみで失礼します
(ゆ) 2015/04/13(月) 09:34

やはりメモリ足りませんでしたか。一気読みせず、ブロック化すれば良いのですが、かなり面倒。
代わりに、1行ずつ読み込む例なぞ。これだとI/O回数分時間がかかってしまいますが、大きなサイズでも動きます。
ただ、Excel標準の読み込み速度に勝てるかどうか…。

 Sub test()
    Const CFILE = "C:\test\test.csv"
    Dim F1 As Integer
    Dim cw As String
    Dim vDim As Variant
    Dim i As Long

    Application.ScreenUpdating = False
    F1 = FreeFile
    Open CFILE For Input As #F1
    While EOF(F1) = False
        Line Input #F1, cw
        vDim = Split(cw, ",")
        Cells(i + 1, 1).Resize(, UBound(vDim) + 1) = vDim
        i = i + 1
    Wend
    Close #F1
    Application.ScreenUpdating = True
 End Sub
(???) 2015/04/13(月) 11:53

 ???さんありがとうございます

 作ってもらっておいて、すみませんが
 速度としては、そういえば早いかな?で
 効果が出たとはあまり体感できない状況です

 しかし、きちんと必要な動作は得られました
 ありがとうございます

 ぼんやりとした質問になりますが、頂いた記述を理解させてもらいたいので
 質問させてください

 一つ前に頂いた記述は、
 #F1に、全てのデータを落として
 カンマ毎にセルに書き出すといった記述で

 (#F1に、全てのデータを落として)←ここがメモリ不足の原因でしょうか

 今回のは、
 #F1に、行毎のデータを落とし
 カンマ毎にセルに書き出し
 次の行毎にデータを落とし
 カンマ毎にセルに書き出し
 の繰り返しをしているのでしょうか

 きちんと説明が出来なくてすみません
(ゆ) 2015/04/13(月) 13:48

最初の例だと、ファイルサイズ丸ごとをまずbDim配列に1回で読み込んでます。
更に、StrConvでUniCode化するのも丸ごと。それをvDim1に分割格納するのも丸ごとなので、
一時的に元のファイルサイズの数倍のメモリを使う訳ですね。

速度が遅い一番の原因は、ファイルI/Oする回数です。普通にInputでカンマ毎に読み込むと、
データ数分のI/Oが発生します。1つ目の例では、これを1回にすることで高速化を図った訳です。
2つ目の例の場合、Line Inputなので1回1行読み込みます。行数分のI/O回数になりますね。
(セルに書き出すのも1回で1行全部書いてます)
1つずつ読むより速いけど、Excelだって1行単位で読むくらいはしているだろうから、大差ない、ってところです。

あと、Excel標準の読み込みが遅いのは、セルの書式等の枠を拡張しつつ読み進めるためではないかと思います。
マクロでセットしても、ここは変わらないかな。

最速にできそうな別案もありますが、作成がかなり面倒なので、考え方だけ。
xlsxファイルって、実はzip圧縮されたxmlファイルなんです。中をみると、書式等のファイル複数と、
データの入ったファイルが別れていたりします。これらは全て、普通のテキストファイルなのです。

そこで、xlsxが保存するレイアウトに合わせて、csvファイルからこれらのテキストファイルに変換。
Excelシートを経由せずにxlsxファイルを作ってしまえば、とても速いはずです。C言語等で作れば、更に速いですね。
(???) 2015/04/13(月) 14:14


 ???さん教えていただきありがとうございます
 とてもよく理解できました

 考え方も頂きありがとうございました

 勉強になります
(ゆ) 2015/04/13(月) 15:08

 Line Inputって、結構速いステートメントですが、260Mbとなると、かなり時間がかかるでしょうねえ

 CSVの形式などの説明をされると、方法もあるかもしれませんが、パッと浮かんだ方法は・・・。

 1 ???さんが 「ブロック化すれば良いのです」と記述されていますが、これが
   一度でデータを読み込むと メモリエラーになるのですから、もう少し少量で読み込む
   例えば、260MBのファイルなら、26Mぐらいずつ読み込んで処理する。
   処理が終わったらまた26M読み込む。
  このようにすれば 読み込みは約10回で済みますよね!!
  このような意味で記述されたのであれば、試してみてください。これ問題は、次のデータを読み込むときの
   繋ぎ方ですね!!
   一回の読込が行の終わりで終わるわけではないのでここをどのように辻褄をあわせるかです。

   昔は、メモリなどが少ないので頻繁にこういう方法が使われていました。

 2 ADOを使ってCSVに接続してデータを取得する方法

    ファイルの形式がわかれば、速い場合もあります。ちょっと試した限りでは、
    Insert Into等で全部ADOで対処すると意外に時間がかかりました。
    ADOで取得したレコードセットをExcel上で CopyFromRecordset で展開すると速く処理されました。

 
(ichinose) 2015/04/15(水) 06:23


こんにちは

分割して処理しています。

Splitは遅いのでカンマの位置でフィールドを区切っています。

カンマを含むフィールドが有ると破綻します。

Sub test()

    Dim fName     As String
    Dim buf()     As Variant
    Dim temp      As Variant
    Dim Rec       As Integer
    Dim i         As Long
    Dim j         As Long
    Dim k         As Long
    Dim m         As Long
    Dim n         As Long
    Dim flg       As Boolean
    Dim s         As Single

    s = Timer

    fName = "c:\test.csv" '←適当に変えてください(フルパス)

    Rec = 10000 '分割レコード数
    ReDim buf(0)

    i = 2

    Open fName For Input As #1

    Application.ScreenUpdating = False

    Cells.ClearContents
    Cells.NumberFormatLocal = "@"

    'フィールドカウント用に一行目を取り込み
    If EOF(1) = False Then Line Input #1, buf(0)
    If InStr(1, buf(0), """") = 0 Then
        flg = False
    Else
        flg = True
    End If

    temp = Split(buf(0), ",")

    k = UBound(temp)

    Cells(1, 1).Resize(, k) = temp

    If flg = True Then
        Cells(1, 1).Resize(, k).Replace """", ""
    End If

    ReDim buf(1 To Rec, 1 To k)

    On Error Resume Next

    Do Until EOF(1) = True

        For j = 1 To Rec
            Line Input #1, temp
            n = 1
            If flg = True Then
                For m = 1 To k
                    buf(j, m) = Replace(Mid(temp, n, InStr(n, temp, ",") - n), """", "")
                    n = InStr(n, temp, ",") + 1
                Next
            Else
                For m = 1 To k
                    buf(j, m) = Mid(temp, n, InStr(n, temp, ",") - n)
                    n = InStr(n, temp, ",") + 1
                Next
            End If
            If EOF(1) = True Then Exit For
        Next j

        Cells(i, 1).Resize(10000, k).Value2 = buf

        i = i + 10000

        ReDim buf(1 To Rec, 1 To k)

        DoEvents

    Loop

    Close #1

    On Error GoTo 0

    Application.ScreenUpdating = True

    Debug.Print Timer - s

End Sub

たいして速くないです・・・・

(ウッシ) 2015/04/15(水) 11:03


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.