środa, 27 kwietnia 2011

CSV rozdzielany przecinkami - eksport pliku Excel .xls do tekstowego .csv

Jeśli chcemy fragment arkusza (jak wyżej) wyeksportować do pliku tekstowego sformatowanego - .csv pojawi się spora trudność. Nie dla wszystkich pól dane są otaczane cudzysłowami (nie dla wszystkich jest to konieczne) - Excel minimalizuje wielkość pliku wyjściowego i omija wpisywanie tych nadmiarowych znaków.
przykład:
a;b;c
x;a\;6
y;a\;"spac ja;"


Niestety wiele aplikacji wymaga od nas by w plikach .csv poszczególne pola były zamknięte cudzysłowami i oddzielone przecinkami. Na próżno szukać takiej opcji w okienku "zapisz jako".

A więc co zrobić, żeby uzyskać wynik w postaci:

"a";"b";"c"
"x";"a\";"6"
"y";"a\";"spac ja;"
?

Bardzo przyda się makro VBA:

Sub ZapiszJakoCSV()

Dim plikWyjsciowy As Variant

Dim zakresZrodlowy As Range
Dim wiersz As Range
Dim komorka As Range
Dim tekst As String
Dim separator As String

plikWyjsciowy = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")

' z ustawień Excela - średnik
separator = Application.International(xlThousandsSeparator) 
' lub przecinek (webrunner1981 wielkie dzięki!)
' separator = Application.International(xlDecimalSeparator)
' rodzaje separatorów http://msdn.microsoft.com/en-us/library/bb177675(v=office.12).aspx
' xlAlternateArraySeparator, xlColumnSeparator, xlDecimalSeparator, xlListSeparator, xlRowSeparator, xlThousandsSeparator
  
' jeżeli jest coś zaznaczone to eksportu to
If Selection.Cells.Count > 1 Then
  Set zakresZrodlowy = Selection
Else
  Set zakresZrodlowy = ActiveSheet.UsedRange
End If

Open plikWyjsciowy For Output As #1

' dla każdego wiersza
For Each wiersz In zakresZrodlowy.Rows
 tekst = ""
 
 ' po wszystkich komorkach wiesza
 For Each komorka In wiersz.Cells
   tekst = tekst & """" & komorka.Value & """" & separator
 Next
 
 ' za ostatnim polem nie powinno być separatora
 While Right(tekst, 1) = separator
   tekst = Left(tekst, Len(tekst) - 1)
 Wend
 
' wypisz do pliku
Print #1, tekst
Next

Close #1

End Sub
To samo w arkuszu Calc, wersja z polami numerycznymi bez cudzysłowów

16 komentarzy:

  1. Działa, tylko chcę, żeby były oddzielone przecinkiem, a nie średnikiem. Jak to zrobić?

    OdpowiedzUsuń
  2. Znalazłem rozwiązanie : separator = Application.International(xlDecimalSeparator)

    OdpowiedzUsuń
  3. xlseparatorarator - coś tu nie gra :((

    OdpowiedzUsuń
  4. Poprawione. Dostępne rodzaje spearatorów:

    Separators

    xlAlternateArraySeparator String Alternate array item separator to be used if the current array separator is the same as the decimal separator.

    xlColumnSeparator String Character used to separate columns in array literals.

    xlDecimalSeparator String Decimal separator.

    xlListSeparator String List separator.

    xlRowSeparator String Character used to separate rows in array literals.

    xlThousandsSeparator String Zero or thousands separator.

    OdpowiedzUsuń
    Odpowiedzi
    1. A jak uruchomić to Makro w Open Office, bo to nie działa. Jest je ktoś w stanie przerobić?

      Usuń
    2. Powinno pomóc, zrobiłem na ten temat oddzielny wpis:
      http://excel-calc-vba-basic.blogspot.com/2012/05/csv-rozdzielany-przecinkami-eksport.html

      Usuń
  5. Czy ktoś potrafi pomóc dopisać kod tak aby makro zapisywało wszystkie arkusze w excelu jako kolejne pliki (np. dodatkowy indeks na końcu).

    OdpowiedzUsuń
  6. Proszę o pomoc jak zmienić makro aby tylko komorki z tekstem były w cudzysłowiu, natomiast z liczbami bez

    Dziękuję i pozdrawiam

    OdpowiedzUsuń
  7. Podczas porzucenia zapisu (anulowania) po uruchomieniu makra tworzony jest plik false. Aby tego uniknąć należy dodać kod:

    If plikWyjsciowy = "False" Then Exit Sub

    tuż pod:

    plikWyjsciowy = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")

    OdpowiedzUsuń
  8. Proszę o pomoc, jak zmienić to makro aby plik zapisywał się jako txt(notatnik), rozdzielany znakiem tabulacji (bez przecinków, średników itp)- żeby dokładnie przenosiły się dane (będą to tekst i liczby) do pliku txt tak jak są w excelu. Super by było aby nowy plik txt zapisywał się do konkretnego miejsca na komputerze.
    Z góry bardzo dziękuje i pozdrawiam

    OdpowiedzUsuń
  9. Włącz nagrywanie makra, wykonaj czynności, czyli zapis do pliku txt z tekstem rozdzielanym tabulacją, a później Alt+F11 podejrzyj kod makra jak to się zapisało.

    OdpowiedzUsuń
  10. Witam,
    Bardzo mi się przyda powyższe makro.
    Sprawdzałem je na moim pliku i działa.
    Ale..
    Zawsze jest ale.
    Pomimo użycia w makrze: separator = Application.International(xlThousandsSeparator)
    w pliku stworzonym nie ma rozdzielników (średników ) pomiędzy poszczególnymi wyrażeniami z komórek. Czyli nie mam czegoś takie go jak autor umieści powyżej makra.
    I dodatkowo pierwsze wyrażenie nie jest w cudzysłowach. Pozostałe oczywiście są.
    I jak tak wybrzydzam to było by fajnie aby to makro zapisywało poszczególne zakładki z nazwą zakładki: xxxx_yyyy_aktualna data (RRRRMMDD).csv (xxxx i yyyy to stałe nazwy dla każdej zakładki)
    Rafał

    OdpowiedzUsuń
  11. Miałem u siebie problem z separatorami. Prawdopodobnie spowodowany angielską wersją windy - zamiast średników pojawiały się przecinki, zamiast przecinków kropki...

    Znalazłem rozwiązanie. Zamiast wpisywać separator = application...
    klepiemy separator = Chr(59) i mamy średnik
    separator = Chr(44) to przecinek

    Możemy też korzystać z każdego innego znaku jeżeli znamy jego kod decymalny. Najprościej skorzystać z jakiegoś translatora online (np paulschoucom).

    OdpowiedzUsuń