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.

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 BF UTF-8
FF FE UTF-16, little endian
FE FF UTF-16, big endian
FF FE 00 00 UTF-32, little endian
00 00 FE FF UTF-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.

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é.

Klikni a stahuj!