Elektroda.pl
Elektroda.pl
X

Search our partners

Find the latest content on electronic components. Datasheets.com
Elektroda.pl
Please add exception to AdBlock for elektroda.pl.
If you watch the ads, you support portal and users.

Import pliku csv do Excela - Niepoprawne numery kart bankowych

hibou1 02 Sep 2021 20:11 483 15
  • #1
    hibou1
    Level 3  
    Dzień dobry,

    dostaję cotygodniowe raporty z banku z wykazem transakcji dokonanych kartami kredytowymi, muszę je obrobić w excelu i wysłać do przejrzenia. Plik dostaję w formacie .csv, który pokazuje błędne numery kart (ostatnia cyfra, to wszędzie "0"). Żeby uzyskać poprawne numery kart w excelu, muszę ten plik zaciągnąć przez Power Query, w którym w kolumnie z kartami zmieniam format danych na tekstowe i dopiero wtedy wklejam plik do exela z poprawnymi już numerami kart.

    Mam w związku z tym dwa pytania:
    - czy da się prawidłowe numery kart wyciągnąć w excelu bez PQ? Za pomocą jakiejś funkcji/formatowania lub VBA?
    - jeśli nie, to czy można za pomocą VBA zautomatyzować zaciąganie przez PQ?

    Będę wdzięczna za pomoc.
  • #2
    kszczepanex
    Level 15  
    Hej, zobacz, czy tak zadziała:
    - otwórz czysty plik excel,
    - wybierzesz Dane -> Z tekstu,
    - wskaż ten plik CSV, jeśli to tylko jedna kolumna to nie przejmuj się separacją
    - po zaimportowaniu zaznacz całą kolumnę
    - prawym klawiszem myszy na kolumnę i "Formatuj komórki"
    - Liczbowe, miejsca dziesiętne 0
  • Helpful post
    #3
    Prot
    Level 37  
    hibou1 wrote:
    czy da się prawidłowe numery kart wyciągnąć w excelu bez PQ?

    Podobny problem :cry: , ba chyba nawet gorszy bo z 26 cyfrowymi numerami kont :D rozwiązuje sobie przy imporcie do excela poprzez proste makro :spoko:

    Code: vbscript
    Log in, to see the code


    opartego o funkcję zamiany RegEx

    Code: vbscript
    Log in, to see the code


    które mi do każdego 26 cyfrowego numeru w tekstowym wyciągu bankowym dodaje przedrostek "PL" :D
  • #4
    hibou1
    Level 3  
    Prot wrote:
    ...imporcie do excela poprzez proste makro

    Dzięki za odpowiedź, ale niestety przerosło mnie to zadanie. :-( Przygotowałam plik z niewielką ilością danych i czy mogłabym prosić o zrobienie tej magii na pierwszej kolumnie?
  • Helpful post
    #5
    Prot
    Level 37  
    hibou1 wrote:
    Przygotowałam plik :D z niewielką ilością danych

    Jeśli chcesz pomocy to musisz załączyć surowy plik csv :cry: lub dokonać jego modyfikacji w edytorze tekstowym :!: Twój plik był chyba modyfikowany w Excelu i program ten już zniszczył dane na temat nr kart - w notepad++ widzę tylko 9,88E+15 :cry:
  • Helpful post
    #6
    Maciej Gonet
    VBA, Excel specialist
    hibou1, nie piszesz, w której wersji Excela pracujesz.
    Generalnie, nie należy takich plików otwierać przez dwuklik na ikonce/nazwie pliku, bo wtedy zdajemy się wyłącznie na domyślność Excela - a Excel wszystko co przypomina liczbę traktuje jak liczbę, a dokładność pamiętania liczb to 15 cyfr. Wszystko co ponad to jest zastępowane zerami.
    Dlatego zanim dopuścimy te dane do Excela należy zapewnić, żeby te numery kart traktował jak tekst.
    W starszych wersjach Excela był bardzo wygodny (moim zdaniem) kreator importu danych z plików tekstowych. Teraz (w nowych Excelach) zastąpiono go domyślnie przez Power Query, który jest bardziej rozbudowany, ma więcej opcji, ale jest trudniejszy w obsłudze (moim zdaniem).
    Generalnie jeśli nie jesteś oblatana w obsłudze Power Query, a Twój komputer chce Ci go domyślnie zaaplikować, to trzeba w opcjach ustawić, żeby używać kreatora importu starszego typu.

    Procedura wygląda tak: otwieramy Excela i na karcie Dane wybieramy import z danych zewnętrznych/z tekstu.
    Potem (w starym kreatorze) kolejno zaznaczamy: Rozdzielany (domyślne)>Dalej, na drugiej stronie Ogranicznik: Średnik>Dalej, na trzeciej stronie w pierwszej kolumnie zaznaczamy format: Tekst, ewentualnie w dalszych kolumnach - tam gdzie są kropki dziesiętne można zmienić na polskie liczby. W tym celu w opcji Zaawansowane należy zmienić separator dziesiętny na kropkę. Nie ma znaczenia, która kolumna jest zaznaczona w danej chwili, to ustawienie dotyczy wszystkich kolumn. Format będzie Ogólny, a nie liczbowy.
    Na końcu klikasz Zakończ i jest jeszcze jedno okno, w którym trzeba wskazać w którym miejscu umieścić dane po imporcie.
    Wszystko to można sobie zarejestrować jako makro, ewentualnie dopasować szczegóły i używać wielokrotnie jak potrzeba.
  • #8
    hibou1
    Level 3  
    Maciej Gonet wrote:
    hibou1, nie piszesz, w której wersji Excela pracujesz.
    Generalnie, nie należy takich plików otwierać przez dwuklik na ikonce/nazwie pliku, bo wtedy zdajemy się wyłącznie na domyślność Excela - a Excel wszystko co przypomina liczbę traktuje jak liczbę, a dokładność pamiętania liczb to 15 cyfr. Wszystko co ponad to jest zastępowane zerami.
    Dlatego zanim dopuścimy te dane do Excela należy zapewnić, żeby te numery kart traktował jak tekst.
    W starszych wersjach Excela był bardzo wygodny (moim zdaniem) kreator importu danych z plików tekstowych. Teraz (w nowych Excelach) zastąpiono go domyślnie przez Power Query, który jest bardziej rozbudowany, ma więcej opcji, ale jest trudniejszy w obsłudze (moim zdaniem).
    Generalnie jeśli nie jesteś oblatana w obsłudze Power Query, a Twój komputer chce Ci go domyślnie zaaplikować, to trzeba w opcjach ustawić, żeby używać kreatora importu starszego typu.

    Procedura wygląda tak: otwieramy Excela i na karcie Dane wybieramy import z danych zewnętrznych/z tekstu.




    W pracy korzystam z 2019, a możliwość automatycznej obróbki pliku przygotowuję dla kogoś innego, kto słabo zna excela, o PQ nie wspominając, dlatego odpada zmienianie opcji programu, próbuję jak najbardziej zautomatyzować obrabianie danych bo poza samym plikiem z banku, w tym samym skoroszycie jest jeszcze arkusz z danymi z naszego firmowego programu do ewidencjonowania rozliczeń i dane z tych dwóch arkuszy muszą ze sobą "pracować". Z tym jednak już sobie poradziłam za pomocą bardzo prostego makra, nie udało mi się jednak pominąć obróbki numerów kart przez PQ. Dzięki za podpowiedź dotyczącą starej wersji importu plików, w domu korzystam z wersji 2013, nagram sobie makro i spróbuję podziałać na nim w pracy. Bardzo dziękuję.

    Dodano po 17 [minuty]:

    Prot wrote:
    Jeśli chcesz pomocy to musisz załączyć surowy plik csv lub dokonać jego modyfikacji w edytorze tekstowym Twój plik był chyba modyfikowany w Excelu i program ten już zniszczył dane na temat nr kart - w notepad++ widzę tylko 9,88E+15



    Z oczywistych względów nie mogę udostępnić pliku z prawdziwymi danymi, ale tym razem zmodyfikowałam go w notatniku, pozmieniałam numery kart, nazwiska i takie tam. :-) Chyba się udało. Dzięki bardzo jeszcze raz za zainteresowanie. Gdyby była taka opcja, żeby to magiczne makro działało z moimi danymi, byłoby super, chętnie bym sobie przeanalizowała jak ta magia się dzieje. Z góry dziękuję!
  • Helpful post
    #9
    Prot
    Level 37  
    hibou1 wrote:
    żeby to magiczne makro działało z moimi danymi, byłoby super

    Na podstawie Twojego pliku utworzyłem jeszcze drugi podobny plik csv i przetestowałem z nimi import za pomocą załączonego pliku z makrami :D

    Po uruchomieniu makra zmiana_numerów (skrót ctrl+i ) - wszystkie numery kart uzyskują przedrostek CN - uzyskując w ten sposób cechy wartości tekstowej.
    Po modyfikacji dane importowane są do nowego arkusza o nazwie zgodnej z nazwą pliku csv :spoko:


    ProtMod..v.7z Download (25.17 kB)Points: 3.5 for user
  • #10
    hibou1
    Level 3  
    Prot wrote:
    Na podstawie Twojego pliku utworzyłem jeszcze drugi podobny plik csv i przetestowałem z nimi import za pomocą załączonego pliku z makrami


    Dzięki bardzo za pomoc! :-)
  • #11
    hibou1
    Level 3  
    Prot wrote:
    Po uruchomieniu makra zmiana_numerów (skrót ctrl+i ) - wszystkie numery kart uzyskują przedrostek CN - uzyskując w ten sposób cechy wartości tekstowej.



    Niestety, to rozwiązanie nie sprawdzi się. Po pierwsze, numery kart nie stają się wartościami tekstowymi i przy próbie usunięcia przedrostka "CN" (a muszę go usunąć, bo potrzebne mi "surowe" numery kart) natychmiast wyświetlają się w formacie naukowym, z zerem zamiast ostatniej cyfry. Po drugie, oryginalny plik (csv) również zapisuje się z przedrostkiem "CN", a ten plik nie może się zmienić. I w końcu po trzecie, przy kolejnym imporcie pliku przedrostek "CN" dodaje się kolejny raz i pojawia się "CNCN" przed numerem karty, a jeśli zaimportuję go trzeci raz, to pojawi się "CNCNCN". Da się z tym coś zrobić?
  • #12
    Prot
    Level 37  
    hibou1 wrote:
    przy kolejnym imporcie pliku przedrostek "CN" dodaje się kolejny raz

    Nie przewidywałem, że ktoś będzie chciał dwukrotnie lub więcej razy importować ten sam plik :cry: Ponieważ nazwy arkuszy przyjmują nazwę importowanego pliku csv - to Excel natychmiast sygnalizował by błąd powtórzenia nazwy arkusza :cunning:
    Fakt, że dołożenie przedrostka CN (Card Number) realizowane jest już w pliku źródłowym mnie nie przeszkadza bo wykorzystuje je tylko jednorazowo :D

    Proponuję zatem przetestowanie innego mechanizmu importu danych wg kodu:
    Code: vbscript
    Log in, to see the code
  • Helpful post
    #13
    Maciej Gonet
    VBA, Excel specialist
    Zarejestrowałem Ci ten import jako makro. Uruchamiasz skrótem Ctrl+i.
    Sprawdź, czy będzie działać w Twojej wersji Excela (rejestracja była w 2007).
    Kolumny z kwotami są sformatowane po polsku. Jeśli wolisz, żeby były kropki dziesiętne, usuń te 3 instrukcje na końcu makra:
    Code: vbscript
    Log in, to see the code


    Załączony plik ma format .xls ze względu na ograniczenia tego Forum. Po odczytaniu zapisz go w formacie .xlsm.
  • #14
    hibou1
    Level 3  
    Prot wrote:
    Ponieważ nazwy arkuszy przyjmują nazwę importowanego pliku csv - to Excel natychmiast sygnalizował by błąd powtórzenia nazwy arkusza


    Oj tam, oj tam, zgłaszał, ale nie ze mną te numery Bruner. ;-P

    Wypróbowałam nowy kod i vbe zgłasza błędy: nrwrs - brak zdefiniowanej zmiennej, zadeklarowałam jako zmienną variant, to wówczas wyskakuje błąd, że LineFromFile jest niezadeklarowaną zmienną, zadeklarowałam, jako variant, to w linii "Do Until EOF(1)" zgłasza błąd, że jest błędna nazwa lub numer pliku. Się mi chyba nie udało się.

    Dodano po 7 [minuty]:

    Maciej Gonet wrote:
    Sprawdź, czy będzie działać w Twojej wersji Excela (rejestracja była w 2007).
    Kolumny z kwotami są sformatowane po polsku. Jeśli wolisz, żeby były kropki dziesiętne...


    Hula, że aż. :-) A kwoty wolę (a raczej potrzebuję) sfromatowane po Polsku, i z tym sobie poradziłam w makrze, które "obrabiało" mi plik po zaciągnięciu z PQ, ale jeśli robi się to za jednym zamachem w trakcie zaciągania pliku, tym lepiej. Dziękuję bardzo za pomoc. :-)
  • #16
    hibou1
    Level 3  
    Prot wrote:
    To na początku wykasuj Option Explicit. Ja wyłączyłem tą kontrolę (patrz kod powyżej) i bez deklaracji zmiennych wszystko hula


    A teraz, to rzeczywiście "pykło" elegancko, dzięki bardzo! :-)