Jak ukládat tabulku z Excelu do CSV formátu

Úvodní stránka | Blog | Excel VBA | Jak ukládat tabulku z Excelu do CSV formátu

    • Formát CSV, jak se dočtete i na Wikipedii (odkaz) nemá oficiální specifikaci, jen určitý předpis (odkaz). Jsou aplikace, které dokáží na tomto formátu postavit datové OLAP kostky (Jedox base) a jsou takové, které vám pod tímto formátem drze předhodí luštěnky (několikařádkové hlavičky, stránkování po nestejně dlouhých blocích se sumačními řádky a čísly stránek, znakové grafické symboly aj.). Právě mám před sebou kupříkladu XLS soubor typu „local file“ vyexportovaný ze SAPu, za kterým je ve skutečnosti něco jen vzdáleně připomínajícího CSV. Ach jo, proč mi zkratka SAP přijde jako SaveAsPaskvil…

    Říkáte si, co je tak těžkého na ukládání excelové tabulky do formátu CSV? Vcelku nic, Excel nabízí v dialogu volby CSV (oddělený středníkem), CSV (Macintosh) a CSV (MS-DOS). Prvně, Excel nezobrazuje možnost uložení v americkém formátu, tedy v pravém „zkratky smyslu“ CSV (Comma separated values, čárkou oddělená data), a ve světě nikoho nezajímá náš lokalizovaný formát CSV (středníkem oddělená data). Americký formát podporuje naštěstí v rámci ukládání alespoň VBA. Pojďme se podívat na kód.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    Sub UlozitVyberJakoCSV(ByVal rngOblast As Range, ByVal strCestaSoubor As String, _
        Optional ByVal boolWindowsFormat As Boolean = True, Optional ByVal _
        boolCeskyFormat As Boolean = True)

        'zákaz překreslování obrazovky
        Application.ScreenUpdating = False

        Dim wbSesitTemp As Workbook

        'nový sešit
        Set wbSesitTemp = Workbooks.Add

        'kopie oblasti do nového sešitu
        rngOblast.Copy wbSesitTemp.Worksheets(1).Cells(1)

        'potlačení varovných hlášek
        Application.DisplayAlerts = False

        'uložení do souboru CSV
        'uložení sešitu ve formátu CSV
        'xlCSV, xlCSVMSDOS, xlCSVWindows
        'Local:=True ... český formát CSV
        'Local:=False ... americký formát CSV

        With wbSesitTemp
            .SaveAs strCestaSoubor, IIf(boolWindowsFormat, xlCSVWindows, _
                xlCSVMSDOS), Local:=boolCeskyFormat
            'přepis souboru bez dotazu
            .Close SaveChanges:=False
        End With

        'povolení varovných hlášek
        Application.DisplayAlerts = True

        'vyčištění paměti
        Set wbSesitTemp = Nothing

        'povolení překreslování obrazovky
        Application.ScreenUpdating = True

    End Sub

    Může se vám zdát divné, proč neukládám sešit přímo, ale používám nový soubor. Ukládání do CSV totiž nerespektuje vaše skryté sloupce, do výsledných dat se promítnou! A až to zjistíte, bude pravděpodobně pozdě (asi víte, proč je skrýváte, že). Proto do ukládací procedury – jak později uvidíte – posílám pouze viditelné buňky výběru. Samotný formát souboru řídí metoda SaveAs a především parametry FileFormat a Local. No jo, jenže…

    K sepsání toho článku mě přiměla potřeba převést tabulku Excelu do MySQL databáze (CSV je v tu chvíli prostředník). To je běžně možné u databázových tabulek s primárním klíčem po aplikaci výběrového (SELECT) dotazu v MySQL Workbench.

    Import CSV souboru do MySQL
    Import CSV souboru do MySQL

    A v čem je jádro pudla? Běžně dnes už pracuji s databázovými tabulkami v UTF-8 a ne ANSI (Windows 1250, tj. CP1250), které generuje Excel. MySQL při importu CSV očekává americký UTF-8 a navíc bez BOM (Byte order mark). Je to volitelných pár bajtů zkraje textového souboru, které říkají, jak ho číst.

    EF BB BFUTF-8
    FF FEUTF-16, little endian
    FE FFUTF-16, big endian
    FF FE 00 00UTF-32, little endian
    00 00 FE FFUTF-32, big-endian

    Jestliže tedy aplikace očekává soubor bez BOM, pak se těchto nadbytečných bajtů musíte zbavit. Na internetu převažují tipy na konvertování souboru prostřednictvím dvojího streamu (unicode a binárního) v rámci ADO pod VBA. Mně se to nepodařilo. Ručně jsem kdysi měnil kódování v Notepadu++, ale to není zrovna pohodlné, a dodnes nevím o spolehlivé aplikaci pro hromadné zpracování (zdarma). Jednou z možností je převzít kód Martina Bertenshawa, využívající API funkce (v příloze je uveden). Já se vydal cestou utilitky iconv.exe řízené z příkazového řádku, jejíž výsledky konverze BOM neobsahují. V kódu níže se neděste množství uvozovek ve spouštěcím příkazu. Jsou nutné z důvodu uvádění cest k souborům, v nichž se vyskytují mezery.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    Sub TestUlozitVyberJakoCSV()

        Dim strCestaSoubor As String

        'cesta a název souboru (podle listu, na kterém se nachází výběr)
        strCestaSoubor = ThisWorkbook.Path & "" & Selection.Parent.Name & _
                         "-ANSI-CP1250.csv"

        'formát souboru: Windows (ANSI, CP1250), čárkou oddělené hodnoty
        Call UlozitVyberJakoCSV(Selection.SpecialCells(xlCellTypeVisible), _
                                strCestaSoubor, True, False)

        'konverze do UTF-8 bez BOM
        'první způsob, API, Mark Bertenshaw
        'Call FileConvert(strCestaSoubor)

        'druhý způsob
        'http://gnuwin32.sourceforge.net/packages/libiconv.htm
        'http://www.gnu.org/software/libiconv/
        'cesta ... c:\Program Files (x86)\GnuWin32\bin\
        'příkaz ... iconv -f CP1250 -t UTF-8 soubor.csv >> soubor_utf8.csv

        Dim wshShell As Object
        Dim strCestaIconvExe As String

        'WSH pro spuštění "commandline" aplikace
        Set wshShell = CreateObject("WScript.Shell")
        'cesta k iconv.exe
        strCestaIconvExe = "c:\Program Files (x86)\GnuWin32\bin"

        Prikaz = "%COMSPEC% /c """"" & strCestaIconvExe & _
                 "iconv.exe"" -f CP1250 -t UTF-8 """ & strCestaSoubor & """ >> """ & _
                 Replace(strCestaSoubor, "ANSI-CP1250", "UTF-8-BEZ-BOM") & """"""

        'běh na pozadí
        wshShell.Run Prikaz, 0, True

    End Sub

    O čtení souborů CSV si řekneme zase něco jindy. A nebude to věřím vůbec nudné.

    Uložit článek jako PDF