Jak skopiować wybrane kolumny z pliku CSV do pliku programu Excel?

Skrypciarze odpowiadają na Wasze pytania

Jak skopiować wybrane kolumny z pliku CSV do pliku programu Excel?

Cześć, Skrypciarze! Jak skopiować wybrane kolumny z pliku CSV do pliku programu Excel?

-- MdP

Cześć, MdP. Słyszeliście kiedyś o takim zjawisku, jak pracoholizm? Padło podejrzenie na jednego z naszych, że właśnie na to zachorował. Bo jak inaczej wytłumaczyć fakt, że podczas gdy większość Skrypciarzy udaje pracujących na konferencji TechEd 2007, i pewnie wyśmienicie się przy tym bawi, jeden siedzi w biurze i nawet mu do głowy nie przyjdzie, żeby sobie odpuścić? Czy to normalne? Oczywiście, że nie. Do tej pory było tak, że wszelkie obowiązki były skrupulatnie wypełniane przez każdego, ale nikomu nawet do głowy nie przyszło, żeby zrobić coś nadprogramowo. Teraz cała ekipa się bawi, rozdając na prawo i lewo laleczki Dr. Scripto, a piszący te słowa Skrypciarz cały ranek nie myślał o niczym innym, jak tylko o tym, jak skombinować skrypt dzięki któremu można skopiować wybrane kolumny z pliku CSV do pliku programu Excel. Ponadto, piszącemu te słowa Skrypciarzowi przyszło nawet do głowy opublikowanie pierwszego wydania podręcznika Windows PowerShell Owner’s Manual (j.ang.), ale w ostatniej chwili się opamiętał i stwierdził, że poczeka z tym na kolegów.

Odpowiedzi na pytanie sobie nie odpuścił i oto efekt porannych rozmyślań:

Const ForReading = 1



Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



objExcel.Workbooks.Add



i = 1



Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForReading)



Do Until objFile.AtEndOfStream

    strLine = objFile.ReadLine

    arrLine = Split(strLine, ",")



    objExcel.Cells(i, 1).Value = arrLine(0)

    objExcel.Cells(i, 2).Value = arrLine(2)



    i = i + 1

Loop



objFile.Close

Wyjaśnijmy teraz może, jak to cudo działa. Na początek, musimy się przyznać do jednej rzeczy: nie importujemy pliku tekstowego bezpośrednio do Excela. (Cóż, dla świętego spokoju sprawdzimy jeszcze raz, ale to naprawdę nie jest możliwe). Wygląda na to, że być może jest możliwe wykorzystanie metody programu Excel OpenText do otwarcia pliku tekstowego i określenia, które kolumny powinny zostać skopiowane, a które nie. To podejście wyglądało jednak na bardzo skomplikowane, zwłaszcza w obliczu tego, jak proste jest napisanie skryptu, który otworzy plik tekstowy, a następnie wstawi określone kolumny do arkusza kalkulacyjnego programu Excel. Zatem jak zwykle Skrypciarz piszący te słowa wybrał tę prostszą drogę.

Co dokładnie się tu dzieje? No cóż, zaczynamy od określenia stałej o nazwie ForReading i nadania jej wartości 1; potrzebna nam ona będzie do podczas otwierania pliku. Następnie stosujemy poniższe dwa wiersze kodu w celu utworzenia wystąpienia obiektu Excel.Application i wyświetlenia go na ekranie:

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

Następnie używamy metody Add w celu dodania nowego, pustego skoroszytu do naszego wystąpienia programu Excel, a skoro już przy tym jesteśmy, przypisujemy także wartość 1 do zmiennej licznika o nazwie i. Za kilka minut zastosujemy tę zmienną do kontroli bieżącego wiersza w arkuszu.

Teraz skupmy się na pliku tekstowym. Dla potrzeb naszego skryptu zakładamy, że mamy plik zawierający wartości oddzielone od siebie za pomocą przecinków, wyglądający tak:

Ken Myer,Finance,Senior Accountant,New York

Pilar Ackerman,Research and Development,Consultant,Des Moines

Jonathan Haas,Research and Development,Scientist II,Des Moines

Jak widzimy, ten przykładowy plik zawiera cztery „kolumny” (pola):

W naszym arkuszu chcemy zaimportować tylko pierwszą i trzecią kolumnę: Nazwisko i Stanowisko. A oto, jak to zrobimy:

Na początek, tworzymy wystąpienie obiektu Scripting.FileSystemObject, a następnie stosujemy metodę OpenTextFile do otwarcia pliku C:\Scripts\Test.txt do odczytu:

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForReading)

Następnie uruchamiamy pętlę Do Untils, która będzie działać dopóki właściwość AtEndOfStream pliku ma wartość True; to taki zawoalowany sposób powiedzenia, że chcemy, aby pętla działała, dopóki nie odczytamy całego pliku. Wewnątrz tej pętli najpierw stosujemy metodę ReadLine w celu odczytania wiersza nr 1 w pliku, a następnie zachowujemy tę wartość w zmiennej strLine:

strLine = objFile.ReadLine

Przy pierwszym przejściu pętli oznaczać to będzie, że strLine będzie miała wartość:

Ken Myer,Finance,Senior Accountant,New York

Niestety niewiele nam to daje, przynajmniej nie w takiej postaci. Dlatego też naszym następnym krokiem jest zastosowanie funkcji Split w celu zamiany tej wartości na tablicę, która będzie zawierała wartości pojedyncze. Rozdzielając zmienną strLine z użyciem przecinka jako delimitera, otrzymamy tablicę o nazwie arrLines. Tablica ta posiada następujące elementy:

Ken Myer

Finance

Senior Accountant

New York

Możecie wierzyć lub nie, ale to naprawdę dużo nam pomaga. Chcemy wpisać do naszego arkusza kalkulacyjnego nazwisko i stanowisko, a nazwisko to przecież element 0 w naszej tablicy. (Pamiętajmy, że pierwszy element w tablicy VBScript to zawsze 0.) Jakby tego było mało, stanowisko to element 2 w tablicy. (Jeżeli pierwszy element to 0, to drugi element to 1, a trzeci element – stanowisko – to 2.) Fajne, prawda? Teraz wystarczy wpisać wartości elementów tablicy 0 oraz 2 w odpowiednie miejsca w arkuszu kalkulacyjnym:

objExcel.Cells(i, 1).Value = arrLine(0)

objExcel.Cells(i, 2).Value = arrLine(2)

W pierwszym wierszu ustawiamy wartość wiersza 1, kolumny 1 (tj. komórki A1) na Ken Meyer, czyli nazwisko użytkownika. A skąd mamy wiedzieć, że jest to komórka 1, 1? To proste: ponieważ przypisaliśmy zmiennej licznika wartość i. W następnym wierszu ustawiamy wartość wiersza 1, kolumny 2 (B1) na Senior Accountant. W tym momencie zwiększamy wartość zmiennej i o 1 (tak, żeby przy następnym przejściu pętli wpisywać dane do komórek w wierszu 2), następnie uruchamiamy pętlę i powtarzamy proces dla następnego wiersza w pliku tekstowym. To już nie jest takie fajne, ale działa:

To by było na tyle. Skrypciarz-pracoholik nie ma już na dziś żadnych obowiązków. Ciekawe, co będzie teraz robił.

Do początku strony