Kutembenuza CSV kukhala Excel: Momwe Mungalowetse Mafayilo a CSV mu Excel Spreadsheets

M'nkhaniyi, mupeza njira ziwiri zosavuta zosinthira fayilo ya CSV kukhala Excel. Kuphatikiza apo, muphunzira momwe mungasinthire mafayilo angapo a CSV ku Excel komanso momwe mungachitire ndi zochitika zomwe gawo la data kuchokera pafayilo ya CSV silikuwoneka bwino pa pepala la Excel.

Posachedwapa, tidayamba kuphunzira mawonekedwe a CSV (Makhalidwe Opatukana ndi Koma) ndi zosiyanasiyana. Njira zosinthira fayilo ya Excel kukhala CSV. Lero tipanga njira yosinthira - kulowetsa CSV ku Excel.

Nkhaniyi ikuwonetsani momwe mungatsegule CSV mu Excel komanso momwe mungatengere mafayilo angapo a CSV nthawi imodzi. Tidzazindikiranso zovuta zomwe zingatheke ndikupereka mayankho ogwira mtima kwambiri.

• Momwe mungasinthire CSV kukhala Excel
• Kutembenuza CSV kukhala Excel: Mavuto ndi Mayankho

Momwe mungasinthire CSV kukhala Excel

Ngati mukufuna kukoka zambiri mu pepala la Excel kuchokera pankhokwe ya kampani yanu, lingaliro loyamba lomwe limabwera m'maganizo ndikutumiza nkhokwe ku fayilo ya CSV ndikulowetsa fayilo ya CSV ku Excel.

Pali njira zitatu zosinthira CSV kukhala Excel: Mutha kutsegula fayilo ndikuwonjezera . Csv molunjika ku Excel, dinani kawiri fayiloyo mu Windows Explorer, kapena lowetsani CSV mu Excel ngati gwero la data lakunja. M'munsimu, ndikufotokozera njira zitatuzi ndikulongosola ubwino ndi kuipa kwa iliyonse ya izo.

• Njira 1: Tsegulani fayilo ya CSV mu Excel
• Njira 2: Tsegulani fayilo ya CSV mu Excel pogwiritsa ntchito Windows Explorer
• Njira 3: Lowetsani CSV mu Excel

Momwe mungatsegule fayilo ya CSV mu Excel

Ngakhale fayilo ya CSV idapangidwa mu pulogalamu ina, mutha kuyitsegula nthawi zonse ngati buku la Excel pogwiritsa ntchito lamulo Open (Otsegula).

Zindikirani: Kutsegula fayilo ya CSV mu Excel sikusintha mawonekedwe a fayilo. Mwa kuyankhula kwina, fayilo ya CSV sidzasinthidwa kukhala fayilo ya Excel (.xls kapena .xlsx fomati), idzasunga mtundu wake woyambirira (.csv kapena .txt).

1. Kukhazikitsa Microsoft Excel, tabu Kunyumba (Kunyumba) dinani Open (Otsegula).
2. A dialog box adzaoneka Open (Kutsegula chikalata), pamndandanda wotsikira pansi pakona yakumanja, sankhani Zolemba Zolemba (Mafayilo alemba).
3. Pezani fayilo ya CSV mu Windows Explorer ndikutsegula ndikudina kawiri.

Ngati mutsegula fayilo ya CSV, Excel idzatsegula nthawi yomweyo ndikuyika deta mu bukhu la Excel latsopano. Ngati mutsegula fayilo (.txt), Excel idzayambitsa Wizard ya Text Import Wizard. Werengani zambiri za izi pakulowetsa CSV mu Excel.

Zindikirani: Microsoft Excel ikatsegula fayilo ya CSV, imagwiritsa ntchito makonda osasintha kuti adziwe momwe mungalowetsere gawo lililonse la data.

Ngati deta ikufanana ndi chimodzi mwazinthu zotsatirazi, gwiritsani ntchito Text Import Wizard:

• Fayilo ya CSV imagwiritsa ntchito ma delimiters osiyanasiyana;
• Fayilo ya CSV imagwiritsa ntchito mitundu yosiyanasiyana ya deti;
• Mukusintha deta yomwe ili ndi ziro zotsogola, ndipo mukufuna kusunga ziro;
• Mukufuna kuwona momwe deta yochokera ku fayilo ya CSV idzalowetsedwera ku Excel;
• Mukufuna kusinthasintha kwambiri pantchito yanu.

Kuti mupangitse Excel kukhazikitsa Text Import Wizard, mutha kusintha kukulitsa mafayilo kuchokera . Csv on .ndilembereni (Musanatsegule fayilo), kapena lowetsani CSV mu Excel monga tafotokozera pansipa.

Momwe mungatsegule fayilo ya CSV pogwiritsa ntchito Windows Explorer

Iyi ndiye njira yachangu kwambiri yotsegulira CSV mu Excel. Mu Windows Explorer, dinani kawiri pa fayilo . Csv, ndipo idzatsegulidwa ngati buku latsopano la Excel.

Komabe, njirayi idzagwira ntchito ngati Microsoft Excel yakhazikitsidwa ngati pulogalamu yotsegulira mafayilo. . Csv. Ngati ndi choncho, muwona chizindikiro chodziwika bwino mu Windows Explorer pafupi ndi dzina la fayilo.

Ngati Excel si pulogalamu yokhazikika, nayi momwe mungakonzere:

1. Dinani kumanja pa fayilo iliyonse . Csv mu Windows Explorer ndi menyu yankhani yomwe imatsegulidwa, dinani Tsegulani ndi (Tsegulani ndi) > Sankhani pulogalamu yokhazikika (Sankhani pulogalamu).
2. Sankhani Excel pamndandanda wamapulogalamu ovomerezeka, onetsetsani kuti pali cholembera cha zomwe mwasankha Nthawi zonse gwiritsani ntchito pulogalamu yosankhidwa kuti tsegulani fayilo yamtunduwu (Nthawi zonse gwiritsani ntchito pulogalamu yosankhidwa yamtundu uwu wa fayilo) ndikudina OK.

Momwe mungatengere CSV ku Excel

Mwa njira iyi mukhoza kuitanitsa deta kuchokera wapamwamba . Csv ku pepala lomwe lilipo kapena latsopano la Excel. Mosiyana ndi njira ziwiri zam'mbuyomu, sikuti imangotsegula CSV mu Excel, koma imatembenuza mawonekedwe . Csv в . Xlsx (ngati mukugwiritsa ntchito Excel 2007, 2010 kapena 2013) kapena .xls (m'mabaibulo a Excel 2003 ndi oyambirira).

1. Tsegulani pepala la Excel lomwe mukufuna ndikudina pa cell komwe mukufuna kulowetsa deta kuchokera ku fayilo . Csv or .ndilembereni.
2. Pa Advanced tabu Deta (Deta) mu gawo Pezani Zowonjezera (Pezani deta yakunja) dinani Kuchokera ku Mawu (Kuchokera m'mawu).
3. Pezani fayilo . Csvzomwe mukufuna kuitanitsa, sankhani ndikudina Lowani (Tengani), kapena ingodinani pafayilo ya CSV yomwe mukufuna.
4. Text Import Wizard idzatsegulidwa, muyenera kungotsatira njira zake.

Tisanapitirire, chonde onani chithunzi chomwe chili pansipa, chikuwonetsa fayilo yoyambirira ya CSV ndi zotsatira zomwe mukufuna mu Excel. Ndikukhulupirira kuti izi zikuthandizani kumvetsetsa chifukwa chomwe timasankhira zokonda mu chitsanzo chotsatirachi.

4. 
   • Khwerero 1. Sankhani mtundu wa data ndi nambala ya mzere komwe mungayambire kuitanitsa. Nthawi zambiri osankhidwa Malire (Ndi olekanitsa) ndi kuchokera ku chingwe 1. Malo owonetseratu omwe ali pansi pa bokosi la zokambirana za wizard akuwonetsa zolemba zingapo zoyambirira za fayilo ya CSV yotumizidwa kunja.
   • Khwerero 2. Mu sitepe iyi, muyenera kusankha delimiters ndi mzere terminator. delimiter (Delimiter) ndiye munthu yemwe amalekanitsa zomwe zili mufayilo ya CSV. Ngati fayilo yanu ya CSV ikugwiritsa ntchito chizindikiro chomwe sichili pamndandanda womwe mukufuna, chongani bokosilo Zina (Zina) ndikulowetsani zomwe mukufuna. Mu chitsanzo chathu, tafotokoza Tab (Tab khalidwe) ndi Sakanizani (Koma) kotero kuti chinthu chilichonse (cholekanitsidwa ndi tabu) chiyambe pamzere watsopano, ndipo zambiri zamalonda, monga ID ndi data yogulitsa (zolekanitsidwa ndi koma), zimayikidwa m'maselo osiyanasiyana.Woyenerera malemba (line terminator) ndi chikhalidwe chomwe chimatsekereza zikhalidwe zapayekha. Zolemba zonse zomwe zili pakati pa zilembo zotere, monga "text1, text2", zidzatumizidwa ngati mtengo umodzi, ngakhale malembawo atakhala ndi zilembo zomwe mwatchula kuti ndi zochepetsera. Muchitsanzo ichi, tatchula koma monga chotsiliza ndi ma quotation marks ngati chomalizira mzere. Zotsatira zake, manambala onse okhala ndi cholekanitsa decimal (chomwe chilinso comma kwa ife!) chidzatumizidwa mu selo imodzi, monga momwe zikuwonekera m'malo owonetseratu pachithunzichi pansipa. Ngati sitinatchule mawu ngati chomaliza cha zingwe, ndiye kuti manambala onse adzatumizidwa m'maselo osiyanasiyana.

     

   • Khwerero 3. Yang'anani m'deralo Chiwonetsero cha data (Sample data parsing). Ngati ndinu okondwa ndi momwe deta yanu ikuwonekera, ndiye dinani batani chitsiriziro (Okonzeka).

Tip: Ngati fayilo yanu ya CSV imagwiritsa ntchito koma motsatizana kapena zilembo zina zotsatizana, chongani bokosilo. Chitani zodulira motsatizana ngati m'modzi (Werengetsani zotsatizana zotsatizana ngati imodzi) kupewa ma cell opanda kanthu.

5. Sankhani ngati muyike zomwe zatumizidwa kutsamba lomwe lilipo kapena patsamba latsopano, ndikudina OKkuti mumalize kutumiza fayilo ya CSV ku Excel.

Tip: Mutha kukanikiza batani Zida (Properties) kuti mukonze makonda apamwamba monga kukonzanso, masanjidwe, ndi masanjidwe a data yochokera kunja.

Zindikirani: Ngati fayilo yanu ya CSV ili ndi manambala kapena madeti, Excel mwina sangasinthe molondola. Kuti musinthe mawonekedwe a data yomwe yatumizidwa kunja, sankhani magawo omwe ali ndi zolakwika, dinani pomwepa ndikusankha kuchokera pazosankha Maselo amtundu (mtundu wa cell).

Kutembenuza CSV kukhala Excel: Mavuto ndi Mayankho

Mtundu wa CSV wakhala ukugwiritsidwa ntchito kwa zaka zopitilira 30, koma ngakhale mbiri yake yayitali, sinalembedwepo. Dzina la CSV (Makhalidwe Osiyanitsidwa ndi Koma) amachokera ku kugwiritsa ntchito koma kupatutsa magawo a data. Koma izo ziri mu chiphunzitso. M'malo mwake, mafayilo ambiri otchedwa CSV amagwiritsa ntchito zilembo zina kulekanitsa deta, mwachitsanzo:

• Ma tabu - mafayilo a TSV (makhalidwe olekanitsidwa ndi tabu)
• Semicolon - mafayilo a SCSV (magawo olekanitsidwa a semicolon)

Mafayilo ena a CSV amasiyanitsa magawo a data okhala ndi mawu amodzi kapena awiri, ena amafunikira Unicode Byte Sequence Marker (BOM), monga UTF-8, kuti amasulire Unicode molondola.

Kusowa kwa miyezo uku kumabweretsa mavuto osiyanasiyana omwe mungakumane nawo poyesera Sinthani fayilo ya Excel kukhala csv, makamaka pakulowetsa fayilo ya CSV ku Excel. Tiyeni tione nkhani zodziwika, kuyambira ndi zofala kwambiri.

• Fayilo ya CSV ikuwonetsedwa molakwika mu Excel (deta yonse imayikidwa pamzere woyamba)
• Ziro zotsogola zidatayika mukatsegula fayilo ya CSV mu Excel
• Makhalidwe amasinthidwa kukhala masiku pakulowetsa fayilo ya CSV ku Excel

Fayilo ya CSV sikuwoneka bwino mu Excel

Zizindikiro: Mukuyesera kutsegula fayilo ya CSV ku Excel ndipo deta yonse imathera pamzere woyamba.

Chifukwa: Mzu wavuto ndikuti zosintha zanu za Windows ndi zilankhulo zanu ndi fayilo yanu ya CSV ili ndi malire osiyanasiyana. Ku North America ndi maiko ena, olekanitsa mndandanda wokhazikika ndi koma. Pomwe m'maiko aku Europe koma imagwiritsidwa ntchito ngati cholekanitsa malo ndipo cholekanitsa mndandanda ndi semicolon.

Kusankha: Pali njira zingapo zothetsera vutoli. Mutha kuyang'ana mwachangu zomwe zili pansipa ndikusankha zoyenera kwambiri pantchito yanu.

1. Tchulani delimiter yolondola mwachindunji mufayilo ya CSV. Tsegulani fayilo ya CSV muzolemba zilizonse (ngakhale notepad yokhazikika idzachita) ndikumata mawu otsatirawa pamzere woyamba. Dziwani kuti uwu uyenera kukhala mzere wosiyana pamaso pa data ina iliyonse:
   • Kukhazikitsa cholekanitsa koma: Sep
   • Kukhazikitsa cholekanitsa kukhala semicolon: sep=;

   Monga momwe mungaganizire, mwanjira iyi mutha kuyika munthu wina aliyense ngati cholekanitsa mwa kungochitchula nthawi yomweyo chizindikiro chofanana.

2. Sankhani delimiter yomwe mukufuna mu Excel. Mu Excel 2013 kapena 2010 pa tabu Deta (Deta) mu gawo Zida Za data (Gwirani ntchito ndi data) dinani Text To Columns (Zolemba ndi mizati).Pamene Column Text Wizard iyamba, mu sitepe yoyamba, sankhani mtundu wa deta Malire (ndi olekanitsa) ndikusindikiza Ena (Zowonjezera). Mu gawo lachiwiri, sankhani delimiter yomwe mukufuna ndikudina chitsiriziro (Okonzeka).

   

3. Sinthani kuwonjezera kuchokera . Csv on .ndilembereni. Kutsegula fayilo .ndilembereni mu Excel idzakhazikitsa Wizard Yolowetsa Malemba, ndipo mudzatha kusankha delimiter yomwe mukufuna, monga momwe tafotokozera m'gawo Momwe mungalowetse CSV ku Excel.
4. Tsegulani fayilo ya CSV yokhala ndi semicolon ngati cholekanitsa pogwiritsa ntchito VBA. Nayi chitsanzo cha VBA chomwe chimatsegula fayilo ya CSV ku Excel yomwe imagwiritsa ntchito semicolon ngati cholekanitsa. Khodiyo idalembedwa zaka zingapo zapitazo pamatembenuzidwe am'mbuyomu a Excel (2000 ndi 2003), koma ngati mumaidziwa bwino VBA, simuyenera kukhala ndi vuto kuyikonzanso kapena kuyisintha kuti igwire ntchito ndi mafayilo a CSV omwe amachotsedwa koma.

Zindikirani: Mayankho onse omwe awonetsedwa amangosintha delimiter ya fayilo ya CSV yoperekedwa. Ngati mukufuna kusintha olekanitsa osasintha kamodzi, ndiye yankho lotsatirali lidzakuyenererani.

5. Timasintha olekanitsa pamikhalidwe ya madera. atolankhani Kunyumba (kuyamba) ndi kuthamanga gawo lowongolera (Control Panel), dinani chinthucho Dera ndi Chilankhulo (Miyezo yachigawo) > Zowonjezera Machitidwe (Zosankha zowonjezera). Bokosi la zokambirana lidzatsegulidwa Sinthani Mwamakonda Anu Format (Kuyika kwa Format) komwe muyenera kusankha kadontho (.) kwa parameter Chizindikiro cha decimal (Integer/Decimal Separator), ndikukhazikitsa koma (,) ya parameter Cholekanitsa mndandanda (Olekanitsa mndandanda wazinthu).

Zolemba Zomasulira: Zokonda izi zimaperekedwa kuti zilembedwe mu Chingerezi ku Excel (ndi mayiko ena angapo). Pakutanthauzira, kudzakhala kofala kugwiritsa ntchito koma monga cholekanitsa decimal ndi semicolon kulekanitsa zinthu zamndandanda.

5. Dinani kawiri OKkuti mutseke mabokosi a zokambirana - mwatha! Kuyambira pano, Microsoft Excel idzatsegula ndikuwonetsa mafayilo onse a CSV (comma delimited) molondola.

Zindikirani: Kukhazikitsa Windows Control Panel kukhala olekanitsa ma decimal ndi zinthu zandandanda zisintha makonda amtundu wa mapulogalamu onse pakompyuta yanu, osati Microsoft Excel yokha.

Ziro zotsogola zimatayika mukatsegula fayilo ya CSV mu Excel

Zizindikiro: Fayilo yanu ya CSV ili ndi ma ziro otsogola ndipo zerozo zimatayika mukatsegula fayilo ya CSV ku Excel.

Chifukwa: Mwachikhazikitso, Microsoft Excel imawonetsa fayilo ya CSV mumpangidwe General (Wamba), momwe ziro zotsogola zimadulidwa.

Kusankha: M'malo motsegula fayilo ya .csv mu Excel, yendetsani Text Import Wizard monga tinachitira poyamba kuti mutembenuzire fayilo ya CSV kukhala Excel.

Mu gawo 3 la wizard, sankhani mizati yomwe ili ndi ma zero otsogola ndikusintha mawonekedwe amizereyi kukhala mawu. Umu ndi momwe mumasinthira fayilo yanu ya CSV kukhala Excel, kusunga ziro m'malo.

Excel imatembenuza zikhalidwe zina kukhala masiku mukatsegula fayilo ya CSV

Zizindikiro: Zina mwazinthu zomwe zili mufayilo yanu ya CSV zimawoneka ngati masiku, ndipo Excel imangosintha izi kuchokera pamawonekedwe alemba mpaka pano.

Chifukwa: Monga tafotokozera pamwambapa, Excel imatsegula fayilo ya CSV mumtundu General (General), yomwe imatembenuza zikhalidwe zonga ngati tsiku kuchokera pamawu kupita ku mtundu watsiku. Mwachitsanzo, ngati mutsegula fayilo ya CSV yokhala ndi zolembera za ogwiritsa ntchito, cholembera cha "Apr23" chidzasinthidwa kukhala tsiku.

Kusankha: Sinthani fayilo ya CSV kukhala Excel pogwiritsa ntchito Text Import Wizard. Mu gawo 3 la wizard, sankhani mizati yokhala ndi zolembedwa zomwe zimawoneka ngati madeti ndikusintha mtundu wagawo kukhala mawu.

Ngati mukufuna kukwaniritsa zotsatira zosiyana, ndiye kuti, mugawo linalake, sinthani zikhalidwe kukhala masiku, kenako ikani mtunduwo. Date (Date) ndikusankha mtundu woyenera wa tsiku kuchokera pamndandanda wotsitsa.

Momwe mungasinthire mafayilo angapo a CSV ku Excel

Ndikuganiza kuti mukudziwa kuti Microsoft Excel imakulolani kuti mutsegule mafayilo angapo a CSV pogwiritsa ntchito lamulo Open (Otsegula).

1. Pa Advanced tabu Filamu (Fayilo) dinani Open (Tsegulani) ndi kuchokera pamndandanda wotsitsa pansi kumanja kwa bokosi la zokambirana, sankhani Zolemba Zolemba (Mafayilo alemba).
2. Kuti musankhe mafayilo angapo motsatana, dinani fayilo yoyamba, kenako dinani ndikugwira kosangalatsa, dinani pa fayilo yomaliza. Mafayilo onsewa, komanso chilichonse chomwe chili pakati, chidzasankhidwa. Kuti musankhe mafayilo omwe sali pamzere, dinani batani Ctrl ndikudina pa fayilo iliyonse . Csvmukufuna kutsegula.
3. Mafayilo onse a CSV akasankhidwa, dinani batani Open (Otsegula).

Njirayi ndi yosavuta komanso yachangu, ndipo titha kuyitcha yabwino kwambiri, ngati sichochitika chimodzi - fayilo iliyonse ya CSV imatsegulidwa motere ngati buku lapadera la Excel. M'malo mwake, kusinthana mmbuyo ndi mtsogolo pakati pa mafayilo angapo otseguka a Excel kumatha kukhala kovutirapo komanso kulemetsa.

Ndikukhulupirira tsopano mutha kusintha fayilo iliyonse ya CSV kukhala Excel. Ngati muli ndi mafunso kapena zovuta, omasuka kundilembera mu ndemanga. Ndipo zikomo kwambiri chifukwa cha kuleza mtima kwa aliyense amene anakhoza bwino kuŵerenga nkhani yaitali imeneyi mpaka mapeto! 🙂