Kushandura CSV kuExcel: Maitiro ekupinza CSV Mafaira muExcel Spreadsheets

Muchikamu chino, iwe unowana mbiri dziri nyore nzira dzekushandura CSV faira kuita Excel. Pamusoro pezvo, iwe unozodzidza kupinza akawanda CSV mafaera muExcel uye maitiro ekuita nemamiriro ezvinhu apo chikamu che data kubva kuCSV faira chisingaratidzi nenzira kwayo paExcel sheet.

Munguva pfupi yapfuura, takatanga kudzidza maficha eCSV (Comma-Separated Values) fomati uye akasiyana nzira dzekushandura faira reExcel kuita CSV. Nhasi tiri kuzoita reverse process - kupinza CSV muExcel.

Ichi chinyorwa chinokuratidza maitiro ekuvhura CSV muExcel uye maitiro ekutumira akawanda CSV mafaera panguva imwe chete. Tichaonawo zvipingamupinyi zvinogona kuitika uye nekupa mhinduro dzinoshanda.

• Maitiro ekushandura CSV kuita Excel
• Kushandura CSV kuExcel: Matambudziko uye Mhinduro

Maitiro ekushandura CSV kuita Excel

Kana iwe uchida kudhonza rumwe ruzivo muExcel sheet kubva mudura rekambani yako, zano rekutanga rinouya mupfungwa nderekutumira kunze dhatabhesi kune CSV faira wobva wapinza iyo CSV faira muExcel.

Pane nzira nhatu dzekushandura CSV kuita Excel: Unogona kuvhura iyo faira nekuwedzera . Csv zvakananga muExcel, tinya kaviri faira iri muWindows Explorer, kana pinza iyo CSV muExcel seyokunze data sosi. Muzvinotevera, ini ndichatsanangura nzira nhatu idzi uye ndoratidza zvakanakira nekuipira kweimwe neimwe yadzo.

• Method 1: Vhura iyo CSV faira muExcel
• Method 2: Vhura CSV faira muExcel uchishandisa Windows Explorer
• Nzira 3: Ngenisa CSV muExcel

Maitiro ekuvhura CSV faira muExcel

Kunyangwe iyo CSV faira yakagadzirwa mune imwe chirongwa, unogona kugara uchivhura seExcel bhuku rebasa uchishandisa murairo Open (Vhura).

Cherechedza: Kuvhura CSV faira muExcel hakuchinji iyo faira fomati. Nemamwe manzwi, faira reCSV harishandurwe kuita faira reExcel (.xls kana .xlsx fomati), richaramba richichengeta mhando yaro yepakutanga (.csv kana .txt).

1. Tanga Microsoft Excel, tab Home (Kumba) tinya Open (Vhura).
2. A dialog box ichaonekwa Open (Kuvhura gwaro), mune yekudonha-pasi rondedzero mukona yezasi yekurudyi, sarudza Rugwaro Mafaira (Mafaira ezvinyorwa).
3. Tsvaga iyo CSV faira muWindows Explorer uye vhura nekudzvanya kaviri pairi.

Kana iwe ukavhura CSV faira, Excel inoivhura nekukasira nekuisa iyo data muExcel workbook nyowani. Kana ukavhura text file (.txt), Excel ichavhura Text Import Wizard. Verenga zvakawanda nezve izvi muKupinza CSV muExcel.

Cherechedza: Kana Microsoft Excel inovhura CSV faira, inoshandisa iyo yekumisikidza yekumisikidza marongero kuti uone chaizvo nzira yekuunza imwe neimwe column yedata.

Kana iyo data ichienderana nechero chimwe chezvinhu zvinotevera, saka shandisa iyo Text Import Wizard:

• Iyo CSV faira inoshandisa delimiters dzakasiyana;
• Iyo CSV faira inoshandisa akasiyana mafomati emazuva;
• Iwe uri kushandura data inosanganisira nhamba ine inotungamira zero, uye iwe unoda kuchengeta iyo zero;
• Iwe unoda kuona kuti data kubva kuCSV faira ichapinzwa sei muExcel;
• Iwe unoda kuwedzera kushanduka mubasa rako.

Kuita kuti Excel itange iyo Text Import Wizard, unogona kushandura faira rekuwedzera kubva . Csv on .txt (usati wavhura faira), kana pinza iyo CSV muExcel sezvakatsanangurwa pazasi.

Maitiro ekuvhura CSV faira uchishandisa Windows Explorer

Iyi ndiyo inokurumidza nzira yekuvhura CSV muExcel. MuWindows Explorer, tinya kaviri pafaira . Csv, uye ichavhura sebhuku idzva reExcel.

Nekudaro, iyi nzira inongoshanda chete kana Microsoft Excel ikaiswa seyakagadzika chirongwa chekuvhura mafaera. . Csv. Kana zvakadaro, iwe uchaona iyo yakajairika icon muWindows Explorer padivi pezita refaira.

Kana Excel isiri iyo default chirongwa, heino maitiro aungaigadzirisa:

1. Kurudyi tinya pane chero faira . Csv muWindows Explorer uye mune menyu yemukati inovhura, tinya Vhura ne (Vhura ne) > Sarudza purogiramu yakarongeka (Sarudza chirongwa).
2. sarudza Excel mune runyorwa rwezvirongwa zvinokurudzirwa, ita shuwa kuti pane cheki chesarudzo Nguva dzose shandisa purogiramu yakasarudzwa kuti vhura rudzi urwu rwefaira (Gara shandisa iyo yakasarudzwa application yerudzi urwu rwefaira) uye tinya OK.

Maitiro ekupinza CSV kuExcel

Nenzira iyi unogona kuunza data kubva kune faira . Csv kune iripo kana nyowani Excel sheet. Kusiyana nenzira mbiri dzakapfuura, haingovhuri CSV muExcel, asi inoshandura fomati . Csv в . Xlsx (kana uri kushandisa Excel 2007, 2010 kana 2013) kana .xls (mune shanduro dzeExcel 2003 uye yapfuura).

1. Vhura pepa reExcel raunoda uye tinya pane sero kwaunoda kupinza data kubva mufaira . Csv or .txt.
2. Pa Advanced tabo Data (Data) muchikamu Wana External Data (Tora data rekunze) tinya Kubva paMashoko (Kubva muzvinyorwa).
3. Tsvaga faira . Csvyaunoda kuunza kunze kwenyika, sarudza wobva wadzvanya Tumira (Import), kana kungodzvanya kaviri pane yaunoda CSV faira.
4. Iyo Text Import Wizard ichavhura, iwe unongoda kutevera matanho ayo.

Tisati taenderera mberi, ndapota tarisa skrini pazasi, inoratidza iyo yekutanga CSV faira uye mhedzisiro yaunoda muExcel. Ndinovimba izvi zvinokubatsira kuti unzwisise zviri nani kuti nei tichisarudza mamwe marongero mumuenzaniso unotevera.

4. 
   • Step 1. Sarudza iyo data format uye mutsara nhamba kubva kwaunotanga kuunza kunze. Kazhinji kazhinji inosarudzwa Delimited (Nevanoparadzanisa) uye kubva patambo 1. Iyo yekutarisa nzvimbo pazasi peiyo wizard's dialog box inoratidza mashoma ekutanga marekodhi eiyo kunze kwenyika CSV faira.
   • Step 2. Mune ino nhanho, iwe unofanirwa kusarudza delimiters uye mutsara terminator. delimiter (Delimiter) ndiye hunhu hunoparadzanisa kukosha muCSV faira. Kana yako CSV faira ichishandisa chiratidzo chisiri mune yakarongwa runyorwa, wobva watarisa bhokisi mwe (Zvimwe) uye isa chimiro chaunoda. Mumuenzaniso wedu, takatsanangura Tab (Tab hunhu) uye Comma (Comma) kuitira kuti chigadzirwa chega chega (ivo-vakaparadzaniswa ne-tab) vatange pamutsara mutsva, uye ruzivo rwechigadzirwa, senge ID uye data yekutengesa (ivo vakaparadzaniswa nemakoma), vanoiswa mumaseru akasiyana.Mavara anokodzera (line terminator) chimiro chinovharira hunhu hwemunhu. Mavara ese akavharirwa pakati pemavara akadai, sekuti “text1, text2”, anozopinzwa kunze sechinhu chimwe chete, kunyangwe chinyorwa chacho chiine mavara awakadoma seanogumira. Mumuenzaniso uyu, tadoma koma semuganho uye maquotation mamaki semutsetse weterminator. Nekuda kweizvozvo, nhamba dzese dzine decimal separator (inovawo comma mune yedu!) ichaunzwa musero rimwe chete, sezvinoonekwa munzvimbo yekutarisa mumufananidzo uri pazasi. Kana tisina kudoma makotesheni setambo terminator, ipapo nhamba dzese dzinozounzwa mumaseru akasiyana.

     

   • Step 3. Tarisa munzvimbo Data preview (Sample data parsing). Kana iwe uchifara nematarisiro ako data, wobva wadzvanya bhatani pedza (Tagadzirira).

Tip: Kana faira rako reCSV richishandisa comma inoteedzana kana imwe delimiter character mukutevedzana, tarisa bhokisi. Bata delimiters dzinotevedzana seimwe (Verenga akatevedzana delimiters seimwe) kudzivirira masero asina chinhu.

5. Sarudza kana uchinamira iyo data inotengeswa kunze kwenyika mupepa riripo kana shizha idzva, wobva wadzvanya OKkupedzisa kupinza iyo CSV faira muExcel.

Tip: Unogona kudzvanya bhatani Properties (Properties) kugadzirisa zvigadziriso zvepamberi senge kuvandudza, marongerwo, uye fomati yedata rakatengwa kunze kwenyika.

Cherechedza: Kana faira rako reCSV riine nhamba kana misi, Excel inogona kusazvishandura nemazvo. Kuti uchinje fomati yedata rakatengwa kunze kwenyika, sarudza iyo column (s) ine zvikanganiso, tinya-kurudyi pazviri uye sarudza kubva pane yemukati menyu. Fomati masero (cell format).

Kushandura CSV kuExcel: Matambudziko uye Mhinduro

Iyo CSV fomati yanga ichishandiswa kweanopfuura makore makumi matatu, asi kunyangwe nhoroondo yayo refu, haina kumbobvira yanyorwa zviri pamutemo. Zita rokuti CSV (Comma-Separated Values) rinobva mukushandiswa kwemakoma kupatsanura nzvimbo dzedata. Asi izvozvo zviri mudzidziso. Muchokwadi, akawanda anonzi CSV mafaera anoshandisa mamwe mavara kuparadzanisa data, semuenzaniso:

• Tabs - TSV mafaera (tab-yakaparadzaniswa tsika)
• Semicolon - SCSV mafaera (semicolon akaparadzaniswa kukosha)

Mimwe misiyano yemafaira eCSV inopatsanura minda yedata ine makotesheni ega kana maviri, mamwe anoda Unicode Byte Sequence Marker (BOM), senge UTF-8, kududzira Unicode nemazvo.

Uku kushaikwa kwezviyero zvinogadzira akasiyana ematambudziko aungasangana nawo paunenge uchiedza shandura excel faira kuita csv, uye kunyanya kana uchipinza faira reCSV muExcel. Ngatitarisei nyaya dzinozivikanwa, tichitanga nezvakawanda.

• CSV faira inoratidzwa zvisizvo muExcel (data rese rinoiswa mukoramu yekutanga)
• Inotungamira zero yakarasika pakuvhura CSV faira muExcel
• Hwaro dzakashandurwa kuita mazuva kana uchipinza CSV faira muExcel

CSV faira hariratidzi zvakanaka muExcel

Zviratidzo: Iwe uri kuyedza kuvhura CSV faira muExcel uye data rese rinopera mukoramu yekutanga.

Reason: Mudzi wedambudziko nderekuti Windows yako yedunhu nemutauro marongero uye yako CSV faira ine akasiyana delimiters. KuNorth America nedzimwe nyika, iyo default list separator icomma. Nepo munyika dzemuEurope komma inoshandiswa semupatsanuri wenzvimbo yedesimali uye mupatsanuri wendima isemicolon.

Sarudzo: Pane zvakawanda zvinogoneka kugadzirisa dambudziko iri. Iwe unogona kukurumidza kutarisa kuburikidza nekurudziro pazasi uye sarudza iyo yakanyanya kufanira kune rimwe basa rako.

1. Nyora iyo chaiyo delimiter zvakananga mufaira reCSV. Vhura iyo CSV faira mune chero mavara edhita (kunyangwe yakajairwa notepad ichaita) uye unamate iwo anotevera mavara pamutsetse wekutanga. Ziva kuti uyu unofanirwa kuve mutsara wakasiyana pamberi peimwe data:
   • Kuseta comma separator: Sep
   • Kuseta kuparadzanisa kune semicolon: sep=;

   Sezvaungave wakafungidzira, nenzira iyi unogona kuseta chero humwe hunhu semupatsanuri nekungozvitsanangura pakarepo mushure mechiratidzo chakaenzana.

2. Sarudza delimiter yaunoda muExcel. MuExcel 2013 kana 2010 pane iyo tab Data (Data) muchikamu Data Tools (Shanda nedata) tinya Runyoro kuMakororamu (Chinyorwa nezvikamu).Kana iyo Column Text Wizard inotanga, padanho rekutanga, sarudza iyo data fomati Delimited (nezviparadzanisa) uye tinya Next (Kuwedzera). Padanho rechipiri, sarudza delimiter yaunoda wodzvanya pedza (Tagadzirira).

   

3. Chinja kuwedzera kubva . Csv on .txt. Kuvhura faira .txt muExcel ichavhura Text Import Wizard, uye uchakwanisa kusarudza delimiter yaunoda, sezvinotsanangurwa muchikamu Maitiro ekupinza CSV muExcel.
4. Vhura faira reCSV rine semicolon semupatsanuri uchishandisa VBA. Heino muenzaniso VBA kodhi inovhura CSV faira muExcel inoshandisa semicolon semupatsanuri. Iyo kodhi yakanyorwa makore mashoma apfuura kune ekare mavhezheni eExcel (2000 uye 2003), asi kana uchinyatsoziva VBA, haufanirwe kuve nedambudziko kusimudzira kana kuishandura kuti ishande nemafaera akatemerwa CSV.

Cherechedza: Mhinduro dzese dzinoratidzwa dzinongoshandura delimiter yefaira reCSV rakapihwa. Kana iwe uchida kushandura default separator kamwechete uye zvachose, zvino mhinduro inotevera ichakukodzera iwe.

5. Isu tinoshandura vanoparadzanisa muzvigadziro zvematunhu ematunhu. dzvanya Home (tanga) uye mhanya control Panel (Control Panel), tinya chinhu Dunhu neMutauro (Regional mwero) > Zvimwe Zvinyorwa (Dzimwe sarudzo). A dialog box richavhura Gadzirisa Format (Format setting) yaunoda kusarudza doti (.) yeparameter Decimal chiratidzo (Integer/Decimal Separator), uye isa komma (,) yeparameter List separator (Rondedzero yechinhu chinoparadzanisa).

Chiziviso cheMushanduri: Aya marongero anopihwa kuChirungu localization yeExcel (uye dzinoverengeka dzimwe nyika). Pakugarisana, zvinenge zvanyanya kushandiswa kushandisa koma semupatsanuri wedesimali uye semicolon kuparadzanisa zvinhu zvarongwa.

5. Tora kaviri OKkuvhara mabhokisi ehurukuro - wapedza! Kubva zvino zvichienda mberi, Microsoft Excel ichavhura uye kuratidza ese CSV (comma delimited) mafaera nenzira kwayo.

Cherechedza: Kuseta iyo Windows Control Panel kune zvipatsanuri zvedesimali uye zvinyorwa zvinoshandura zvimiro zvemaitiro ezvirongwa zvese pakombuta yako, kwete Microsoft Excel chete.

Mazero anotungamira anorasika pakuvhura CSV faira muExcel

Zviratidzo: Yako CSV faira ine makoshero ane anotungamira zero uye iwo mazero anorasika pakuvhura iyo CSV faira muExcel.

Reason: Nekumisikidza, Microsoft Excel inoratidza iyo CSV faira mufomati General (Common), umo mazero anotungamira anodimburwa.

Sarudzo: Pane kuvhura .csv faira muExcel, mhanyisa Text Import Wizard sezvatakaita pakutanga kushandura CSV faira kuExcel.

Munhanho 3 yewizard, sarudza makoramu ane kukosha ane mazero anotungamira uye shandura fomati yemakoramu aya kuita mavara. Aya ndiwo maitiro aunoshandura faira rako reCSV kuenda kuExcel, uchichengeta zero munzvimbo.

Excel inoshandura humwe hunhu kuve misi kana uchivhura CSV faira

Zviratidzo: Mamwe emhando mufaira rako reCSV anotaridzika semazuva, uye Excel inozoshandura otomatiki hunhu hwakadaro kubva kumavara fomati kusvika nhasi.

Reason: Sezvambotaurwa pamusoro, Excel inovhura iyo CSV faira mufomati General (General), iyo inoshandura misi-yakafanana tsika kubva kumavara mafomati kuenda kufomati yemazuva. Semuenzaniso, kana iwe ukavhura CSV faira rine mushandisi logins, yekupinda "Apr23" ichashandurwa kuita zuva.

Sarudzo: Shandura iyo CSV faira kuExcel uchishandisa Text Import Wizard. Munhanho 3 yewizard, sarudza makoramu ane marekodhi anoita semazuva uye shandura iyo column fomati kuti iite mavara.

Kana iwe uchida kuwana zvakapesana mhedzisiro, ndiko kuti, mune imwe koramu, shandura kukosha kune misi, wobva waisa iyo fomati. zuva (Date) uye sarudza iyo yakakodzera zuva fomati kubva pane yekudonha-pasi runyorwa.

Maitiro ekupinza akawanda CSV mafaera muExcel

Ini ndinofunga iwe unoziva kuti Microsoft Excel inokutendera iwe kuvhura akawanda CSV mafaera uchishandisa rairo Open (Vhura).

1. Pa Advanced tabo Fillet (Faira) tinya Open (Vhura) uye kubva pane yekudonha-pasi pasi pazasi kurudyi kwebhokisi rebhokisi, sarudza Rugwaro Mafaira (Mafaira ezvinyorwa).
2. Kuti usarudze mafaera akawanda mumutsara, tinya pane yekutanga faira, wobva wadzvanya uye ubate kusuduruka, tinya pafaira rekupedzisira. Mafaira ese aya, pamwe nezvose zviri pakati, zvichasarudzwa. Kuti usarudze mafaira asiri mumutsara, bata kiyi Ctrl uye tinya pane imwe neimwe faira . Csvunoda kuvhura.
3. Kana mafaera ese anodiwa eCSV asarudzwa, tinya bhatani Open (Vhura).

Iyi nzira iri nyore uye inokurumidza, uye isu tinogona kuidaidza kuti yakanaka, kana isiri yemamiriro ezvinhu - yega yega CSV faira inovhura neiyi nzira seyakasiyana Excel workbook. Mukuita, kuchinjika nekudzoka pakati peakawanda akavhurika Excel mafaera kunogona kuve kwakanyanya kusagadzikana uye kuremedza.

Ndinovimba ikozvino unogona kushandura nyore chero CSV faira kuExcel. Kana uine chero mibvunzo kana matambudziko, inzwa wakasununguka kunyora kwandiri mumhinduro. Uye ndinokutendai nekushivirira kwemunhu wese akagona kuverenga ichi chinyorwa chakareba kusvika kumagumo! 🙂