Shandura CSV kuExcel setafura

Zviri mukati medatabase zvinowanzotorwa se .csv faira. Nekudaro, ingori faira remavara, risingaverengeki zvakanyanya. Kuti ushande nezviri mukati medhatabhesi, zvinodikanwa kuti uzviratidze mune imwe fomati - kazhinji maExcel sheets anonyanya nyore. Nezve nzira dzipi dziripo dzekuita izvi, ndeipi iri nani, uye ndezvipi zvikanganiso zviripo pakuendesa data, zvichakurukurwa munyaya ino.

Maitiro ekushandura CSV kuita Excel Format

Pane nzira dzakasiyana idzo gwaro reCSV rakatorwa kubva kudhatabhesi rinogona kuendeswa kuExcel. Tichakurukura zvitatu zvacho:

1. Kuvhura kwakananga muExcel.
2. Kuvhura kuburikidza neWindows Explorer.
3. Pinza gwaro rine shanduko yefomati.

Kuvhura gwaro reCSV muExcel

Excel inogona kuvhura .csv magwaro zvakananga pasina kutendeuka. Chimiro hachichinje mushure mekuvhurwa nenzira iyi, iyo .csv yekuwedzera inochengetwa - kunyangwe iyo yekuwedzera inogona kuchinjwa mushure mekugadzirisa.

1. Tanga Excel, tinya "faira", Zvino"Open".
2. Muhwindo rinoonekwa, sarudza sarudzo "Nyora mafairakubva pane yakawedzerwa runyorwa.

3. Tsvaga gwaro raunoda uye tinya kaviri pariri.

.csv magwaro anovhurwa muExcel nekukasika, pasina mamwe mamanipulations. Asi .txt mafaira anoda kushandurwa - hwindo richaonekwa "Text Import Wizards".

Pane zviitiko apo zviri nani kuti usazarura gwaro racho zvakananga, asi kudana tenzi. Iyi nzira inobvumirwa muzviitiko zvinotevera:

• hunhu hwekuparadzanisa hunoshandiswa mugwaro hahuna mwero, kana kuti kune akati wandei marudzi awo;
• gwaro rine mazuva mumhando dzakasiyana;
• uri kushandura nhamba dzinotanga nemaziro uye unoda kudzichengeta zvakadaro;
• usati waendesa data, iwe unoda kuona kuti mhedzisiro ichaita sei;
• iwe kazhinji unoda kutakurika kwakawanda.

tenzi ichatanga kana ukashandura kuwedzera kwegwaro ku .txt. Iwe unogona zvakare kutanga kupinza faira zvakasiyana, izvo zvichakurukurwa gare gare, mushure mekutsanangura nzira yechipiri yekuvhura.

Kana ukaita shanduko kugwaro wapedza kurivhura, Excel inozokuudza kuti urichengetedze se .xls (kana .xlsx) nekuti ukasadaro mamwe mafomati anorasika. Ipapo pachava nemukana wekuchinja maitiro kumashure, zvisinei, chimwe chikamu chemukati chinogona kurasikirwa - zero dzinogona kunyangarika pakutanga kwenhamba, mamwe marekodhi anogona kuchinja chitarisiko chavo.

Kuvhura gwaro reCSV kuburikidza neWindows Explorer

Iyi nzira haina kunyanya kusiyana neyakapfuura. Kuti uvhure gwaro, ingobaya kaviri pairi muWindows Explorer.

Usati wavhura, ita shuwa kuti padivi pezita regwaro pane chiratidzo chechirongwa cheExcel, uye kwete chimwe - izvi zvinoreva kuti Excel inosarudzwa sechirongwa chinofanira kuvhura mafaera akadaro. Zvikasadaro, imwe purogiramu ichavhura. Unogona kuishandura kuExcel seizvi:

1. Dana menyu yemukati nekudzvanya-kurudyi pane chero .csv faira woisa Vhura Na…> Sarudza Chirongwa.
2. sarudza Excel (desktop) of Zvirongwa Zvinokurudzirwa, iite sechirongwa chinofanirwa kugara chichishandiswa pamafaira akadaro (tarisa bhokisi riri pazasi), uye vhara hwindo nekudzvanya OK.

Ngenisa CSV kuExcel

Izvo zvakare zvinogoneka kushandura gwaro rakavhurwa kuita Excel workbook. Mafomati achachinja kuita .xls kune dzimwe shanduro dzekare dzeExcel (2000, 2003) uye .xlsx kune dzimwe dzose. Zvese zvirimo zvicharatidzwa pane rimwe pepa.

1. Dzvanya pasero riri pashizha panofanira kutanga kupinza. Iri rinowanzova sero rekutanga patafura, A1. Kutanga kubva pairi, mitsara yakawanda sezvairi mufaira rakavhurwa, uye akawanda makoramu sezvaaine kukosha mune yega yega achazadzwa.
2. Mu tab "Data” mugroup”Kuwana External Data” sarudza "Kubva pane zvinyorwa".

3. Muhwindo rinoonekwa, tsvaga gwaro rinodiwa uye tinya kaviri pairi (unogona kushandisa bhatani Tumira pazasi pehwindo).

4. Tevere, iwe unofanirwa kutevedzera nhanho-ne-nhanho mirairo yeakavhurwa Text Import Wizards.

Mufananidzo uri pazasi unoratidza gwaro rekutanga uye mhedzisiro inotarisirwa. Kuti zvese zvitaridzike sezvizvi mushure mekupinza, unofanirwa kushandisa marongero akasiyana, ayo anozokurukurwa gare gare.

Step 1. Iyo wizard ichakukurudzira kuti usarudze rudzi rwefaira - sezvaungafungidzira, zvichave "nevaparadzi” (muChirungu - delimited), uye mutsara unotanga kutamiswa kwemukati - kazhinji, iwe unofanirwa kutanga mazwi 1kana iwe usingade kuendesa chete chikamu chemukati. Iwindo riri pazasi richaratidza mitsetse yekutanga kubva pagwaro rakasarudzwa.

Step 2. Zvino unofanira kunzwisisa kuti ndeipi inoshandiswa separator (panogona kunge paine anopfuura mumwe) mufaira, uye tsanangura chimiro ichi mukati Masters. Iyo ine sarudzo dzekusarudza kubva kune akajairwa delimiters, asi kana atypical character yakashandiswa mugwaro, unogona kusarudza mwe uye isa chimiro chaunoda. Mufaira rinoshandiswa pakuratidzira, madhilimita ari - Comma и Tab. Makoma anopatsanura maseru ane hunhu hwechigadzirwa, senge serial nhamba nenhamba yemakopi akatengeswa, uye ma tabo anoparadzanisa chigadzirwa kubva kune chimwe - ruzivo nezve chimwe nechimwe chinofanira kutanga pamutsara mutsva.

Izvo zvinodiwawo kutsanangura uye kutsanangura text delimiter. Iri ivara rinoiswa pamberi uye mushure mechikamu chega chega chemavara chinofanira kunge chiri musero rimwe. Kutenda kune delimiter, chikamu chimwe nechimwe chakadaro chinoonekwa sechinhu chakasiyana, kunyange mukati maro mune mavara akasarudzwa kuti aparadzanise maitiro. Mugwaro redu, kukosha kwega kwega kwakarongwa nekotesheni - naizvozvo, kunyange kana iine comma (somuenzaniso, "kutanga, wozoenderera mberi"), zvinyorwa zvayo zvose zvichaiswa musero rimwe chete, uye kwete mumaviri akatevedzana.

Step 3. Pano zvakakwana kuti utarise preview uye, kana isingaratidzi chero kukanganisa kusingagamuchirwi, tinya pedza. Zvinogona kuitika kuti mamwe maitiro achaparadzaniswa kwete nemuparadzi mumwe, asi nevanoverengeka, semhedzisiro, maseru asina kukosha achaonekwa pakati pavo. Kuti izvi zvisaitike, sarudza bhokisi Bata delimiters dzinotevedzana seimwe.

5. Sarudza nzira yekuenda (inogona kunge iri pepa idzva kana pepa riripo) wobva wadzvanya OKkupedzisa hurongwa hwekuunza kunze kwenyika.

Unogonawo kuvhara Materials - kune zvimwe zvingangoitika. Semuenzaniso, unogona kufomatidza zvirimo, kugadzirisa markup, uye kuti ruzivo rwunovandudzwa sei.

Dzimwe nguva mhedzisiro yekutendeuka iri kure nekutarisirwa. Kuti mugumisiro uyu ungashandurwa sei zvichakurukurwa muchikamu chinotevera chechinyorwa.

Matambudziko panguva yekutendeuka uye mhinduro dzawo

Kwenguva yese iyo CSV fomati yanga iripo, hapana akambozvinetsa kuinyora zviri pamutemo. Nokudaro, kunyange zvazvo zvinofungidzirwa kuti commas inofanira kushandiswa kuparadzanisa tsika, chaizvoizvo, dhatabhesi dzakasiyana-siyana dzinoshandisa zvakasiyana-siyana - semicolons, tabs, nezvimwe.

Zvinyorwa delimiters zvinogonawo kusiyana - kazhinji kazhinji anenge ari maquotation mamaki kana byte order mark. Panogona kunge pasisina kana delimiters zvachose - ipapo hunhu hunoshandiswa semupatsanuri hunogara huchionekwa sehukadaro (saka kazhinji haisi koma - inoshandiswa zvakanyanya muzvinyorwa - asi akasiyana, asina kujairika hunhu).

Mafaira asina-standard angasavhura zvakanaka - kuitira kuti aonekwe sezvaanofanira, unofanirwa kushandura zvirongwa zvekombuta kana chirongwa chekuvhura. Ngationei kuti ndeapi matambudziko aripo uye kuti ndeapi magadzirirwo aripo pazviri.

Iyo faira haina kuvhurika zvakanaka

Uchapupu. Zvose zviri mugwaro zvinoiswa muchikamu chekutanga.

Cause. Gwaro rinoshandisa hunhu sedelimiter isina kusarudzwa seyakadaro mumasetin'i emakombuta, kana kuchengeterwa rimwe basa rakasiyana. Semuenzaniso, comma inogona kuchengetwa kuti iparadzanise zvikamu zvechiverengero zvenhamba, uye nekudaro haigone kuparadzanisa kukosha mufaira.

Solutions. Pane nzira dzakasiyana dzekugadzirisa dambudziko iri:

1. Shandura mutsara wekuparadzanisa mugwaro pacharo. Ivhure muNotepad kana mupepeti wakafanana, uye mumutsara wekutanga (isina chinhu, data rese rinofanira kunge riri mumitsetse iri pazasi), nyora zvinotevera:

• kushandura separator kuita comma: Sep
• kuchinja kuita semicolon: sep=;

Imwe hunhu yakanyorwa mushure sep = mumutsetse wekutanga, ichavawo delimiter.

2. Iyo yekuparadzanisa hunhu inoshandiswa mufaira inogona zvakare kutsanangurwa muExcel pachayo. Mune shanduro 2016, 2013 kana 2010, nokuda kweizvi unoda kuzarura tab. Data uye sarudza "Mavara nezvikamu" mugroup”Kushanda nedata".

Izvi zvichavhura hwindo"Wizards yekugovera zvinyorwa mumakoramu". Ikoko, kubva kune akarongwa mafomu e data, iwe unofanirwa kusarudza iwo ane vapatsanura. Ipapo unofanira kudzvanya Next uye, mushure mekusarudza delimiter, pedza.

3. Kuvhura otomatiki gwaro ne Kunze Wizard, uye kwete muExcel sheet chete, iyo yekuwedzera inogona kuchinjwa kubva .csv kuenda .txt. AT Masters zvinokwanisika kutsanangura chero chimiro semupatsanuri - kuti ungaita sei izvi, chinyorwa chakatsanangurwa kare.
4. Shandisa VBA. Unogona kushandisa, semuenzaniso, - inokodzera Excel 2000 kana 2003. Iko code inogona kuchinjwa kuitira kuti ive yakakodzera kune dzimwe shanduro.

Mhinduro dziri pamusoro dzakagadzirirwa magwaro ega ega, marongero ayo anosiyana kubva kune akajairwa. Aya anotevedzana ezviito anozoda kuiswa kune yega yega faira risingavhure nemazvo. Kana akawanda emagwaro asingazaruri zvakarurama, zvichida mhinduro yakanakisisa ndeyokushandura zvirongwa zvekombiyuta - izvi zvinokurukurwa muchishanu mhinduro.

5. Shandura separator uye decimal point muzvirongwa zvekombuta

В Control Panel, akadanwa nebhatani kutanga, sarudza "Zvimwe zvingasarudzwa” kubva pamazita"Regional Standards”. Mushure mekudzvanya, hwindo rinoonekwaFormat setting” - mariri unogona kusarudza"List Separator” uye patsanuri yezvikamu zvakakwana nezvidimbu zvenhamba. Kana mafaera achida koma semuganho, tanga waisa nguva yacho senzvimbo yedesimali. Inogona kuve imwe nzira yakatenderedza - iwe unoda semicolon semupatsanuri hunhu. Zvadaro nokuda kwezvikamu, iwe unogona kusiya chero chiratidzo, izvi hazvizokonzeri kukakavara.

Kana zvigadziriso zvese zvapera, tinya OK pamahwindo maviri akazaruka - ivo vanovhara uye shanduko dzichachengetwa. Ramba uchifunga kuti ivo zvino vanoshanda kune ese mapurogiramu ari pakombuta.

Bvisa mazero anotungamira

chiratidzo. Humwe hunokosha huri mugwaro rezvinyorwa nhamba dzinotanga ne zero dzisina kupatsanurwa nechiratidzo chechidimbu (semuenzaniso, ciphers uye macode ane nhamba yakatarwa yemanhamba, logins uye passwords, mita uye chiridzwa kuverenga). MuExcel, mazero ekutanga kwenhamba dzakadaro anonyangarika. Kana ukagadzirisa faira wobva warichengeta sebhuku rekushanda reExcel, hazvichagoneke kuwana mubhuku rebasa rino maive nenhamba dziya dzine zero.

Cause. Excel ine mafomati akasiyana ezvinyorwa uye manhamba. Mumafaira emafaira, hapana kupatsanurwa kwakadaro, uye saka Excel inopa iyo General fomati kune ese kukosha. Zvinoreva kuti mavara anoratidzwa semavara, uye nhamba dzisina alfabheti dzinoratidzwa senhamba isingagoni kutanga ne zero.

mhinduro. Chinja kuwedzera ku .txt kuti uite Import Wizard paunovhura gwaro. Paunosvika paChikamu chechitatu, shandura mafomati emakoramu nenhamba dzinotanga nemaziro kuti dzinyore.

Zvimwe zvehutsika zvinotaridzika semazuva

chiratidzo. Mazuva anoratidza kukosha izvo pakutanga mavara asina kujeka kana manhamba.

Cause. Iyo yakajairika fomati inosanganisira kushandura kukosha kuita mazuva akafanana neayo eExcel. Kana iyo CSV gwaro riine imwechete kukosha senge May12, ipapo muExcel sheet icharatidzwa sezuva.

mhinduro. Zvakafanana nezviri munyaya yapfuura. Chinja kuwedzera kuti .txt, in Masters shandura fomati yemitengo yakashandurwa kuita misi kuita zvinyorwa.

Kana, pane zvinopesana, iwe uchida kuratidza zviri mukati meimwe column semazuva, isa iyo fomati yayo zuva. Kune akati wandei marudzi efomati yemazuva, saka sarudza iyo yaunoda kubva pane iyo rondedzero.

Maitiro ekupinza akawanda CSV mafaera muExcel

Excel inogona kuvhura akawanda CSV mafaera kamwechete.

1. dzvanya Faira> Vhura uye sarudza sarudzo Nyora mafaira kubva pakudonhedza pasi pasi.
2. Kusarudza mafaera akawanda padivi nepadivi, tanga wasarudza yekutanga, wobva wadzvanya kusuduruka wobva wadzvanya pane yekupedzisira. Mukuwedzera kune mafaira akasarudzwa, mafaira ose ari pakati achasarudzwa.
3. Click Open.

Kukanganisa kweiyi nzira ndeyekuti faira imwe neimwe yakasarudzwa ichavhurwa zvakasiyana muExcel. Kufamba kubva kune rimwe gwaro kuenda kune rimwe kunogona kuwedzera mari yenguva. Zvisinei, zvinokwanisika kukopa zviri mukati mazvo ose mumapepa ari mubhuku rimwe chete rebasa.

Tsananguro yacho yaive yakareba, zvisinei, ikozvino iwe unozogona kuvhura chero CSV faira muExcel pasina kuoma kwakawanda. Kana chimwe chinhu chichiita sechisinganzwisisi pakutanga, ingotevera matanho anoratidzwa uye zvese zvichave zvakajeka.