Mayar da CSV zuwa Excel: Yadda ake Shigo da Fayilolin CSV zuwa Fayilolin Excel

A cikin wannan labarin, zaku sami hanyoyi masu sauƙi guda 2 don canza fayil ɗin CSV zuwa Excel. Bugu da ƙari, za ku koyi yadda ake shigo da fayilolin CSV da yawa zuwa cikin Excel da kuma yadda ake magance yanayin da wani ɓangare na bayanai daga fayil ɗin CSV ba ya nunawa daidai akan takardar Excel.

Kwanan nan, mun fara nazarin fasalulluka na tsarin CSV (Wakafi-Raba Ƙimar) da iri-iri. Yadda za a canza fayil ɗin Excel zuwa CSV. Yau za mu yi tsarin baya - shigo da CSV cikin Excel.

Wannan labarin zai nuna muku yadda ake buɗe CSV a cikin Excel da yadda ake shigo da fayilolin CSV da yawa a lokaci guda. Za mu kuma gano ramummuka masu yuwuwa kuma mu ba da mafita mafi inganci.

• Yadda ake Canza CSV zuwa Excel
• Canza CSV zuwa Excel: Matsaloli da Magani

Yadda ake Canza CSV zuwa Excel

Idan kana buƙatar cire wasu bayanai a cikin takardar Excel daga ma'aunin bayanan kamfanin ku, ra'ayin farko da ke zuwa a zuciya shi ne fitar da bayanan zuwa fayil ɗin CSV sannan kuma shigo da fayil ɗin CSV cikin Excel.

Akwai hanyoyi 3 don canza CSV zuwa Excel: Kuna iya buɗe fayil ɗin tare da tsawo . Csv kai tsaye a cikin Excel, danna fayil sau biyu a cikin Windows Explorer, ko shigo da CSV cikin Excel azaman tushen bayanan waje. A cikin wadannan hanyoyi guda uku, zan yi karin bayani kan wadannan hanyoyi guda uku tare da nuna fa'ida da rashin amfanin kowannensu.

• Hanyar 1: Buɗe fayil ɗin CSV a cikin Excel
• Hanyar 2: Buɗe fayil ɗin CSV a cikin Excel ta amfani da Windows Explorer
• Hanyar 3: Shigo da CSV cikin Excel

Yadda ake buɗe fayil ɗin CSV a cikin Excel

Ko da an ƙirƙiri fayil ɗin CSV a cikin wani shirin, koyaushe kuna iya buɗe shi azaman littafin aikin Excel ta amfani da umarnin Bude (Bude).

lura: Bude fayil ɗin CSV a cikin Excel baya canza tsarin fayil ɗin. A wasu kalmomi, fayil ɗin CSV ba za a canza shi zuwa fayil ɗin Excel ba (.xls ko .xlsx tsarin), zai riƙe ainihin nau'insa (.csv ko .txt).

1. Kaddamar da Microsoft Excel, tab Gida (Gida) danna Bude (Bude).
2. Akwatin maganganu zai bayyana Bude (Buɗe daftarin aiki), a cikin jerin zaɓuka a cikin ƙananan kusurwar dama, zaɓi Fayilolin Rubutu (Faylolin rubutu).
3. Nemo fayil ɗin CSV a cikin Windows Explorer kuma buɗe shi ta danna sau biyu akan shi.

Idan ka buɗe fayil ɗin CSV, Excel zai buɗe shi nan da nan ta saka bayanai a cikin sabon littafin aikin Excel. Idan ka buɗe fayil ɗin rubutu (.txt), Excel zai ƙaddamar da Wizard Import na Rubutun. Kara karantawa game da wannan a Shigo da CSV cikin Excel.

lura: Lokacin da Microsoft Excel ya buɗe fayil ɗin CSV, yana amfani da saitunan tsarawa na asali don gano ainihin yadda ake shigo da kowane ginshiƙi na bayanai.

Idan bayanan sun yi daidai da aƙalla ɗaya daga cikin abubuwan masu zuwa, to, yi amfani da Mayen Shigo da Rubutun:

• Fayil ɗin CSV yana amfani da iyakoki daban-daban;
• Fayil ɗin CSV yana amfani da tsarin kwanan wata daban-daban;
• Kuna canza bayanan da suka haɗa da lambobi tare da sifilin jagora, kuma kuna son kiyaye wannan sifilin;
• Kuna son yin samfoti yadda za a shigo da bayanai daga fayil ɗin CSV zuwa cikin Excel;
• Kuna son ƙarin sassauci a cikin aikinku.

Don yin Excel ƙaddamar da Mayen Shigo da Rubutun, zaku iya canza tsawo na fayil daga . Csv on .txt (kafin buɗe fayil ɗin), ko shigo da CSV cikin Excel kamar yadda aka bayyana a ƙasa.

Yadda ake buɗe fayil ɗin CSV ta amfani da Windows Explorer

Wannan ita ce hanya mafi sauri don buɗe CSV a cikin Excel. A cikin Windows Explorer, danna sau biyu akan fayil ɗin . Csv, kuma zai buɗe a matsayin sabon littafin aikin Excel.

Koyaya, wannan hanyar za ta yi aiki ne kawai idan an saita Microsoft Excel azaman tsohuwar shirin buɗe fayiloli. . Csv. Idan haka ne, zaku ga gunkin da kuka saba a cikin Windows Explorer kusa da sunan fayil.

Idan Excel ba shine tsoho shirin ba, ga yadda zaku iya gyara shi:

1. Dama danna kowane fayil . Csv a cikin Windows Explorer kuma a cikin mahallin mahallin da ke buɗewa, danna Bude tare da (Bude da) > Zaɓi shirin tsoho (Zaɓi shirin).
2. zabi Excel a cikin jerin shirye-shiryen da aka ba da shawarar, tabbatar da cewa akwai alamar bincike don zaɓin Yi amfani da shirin da aka zaɓa koyaushe don bude irin wannan fayil (Koyaushe yi amfani da aikace-aikacen da aka zaɓa don irin wannan fayil ɗin) kuma danna OK.

Yadda ake Shigo da CSV zuwa Excel

Ta wannan hanyar zaku iya shigo da bayanai daga fayil . Csv zuwa wani data kasance ko sabon takardar Excel. Ba kamar hanyoyin biyu na baya ba, ba kawai buɗe CSV a cikin Excel ba, amma yana canza tsarin . Csv в . Xlsx (idan kuna amfani da Excel 2007, 2010 ko 2013) ko .xls (a cikin sigogin Excel 2003 da baya).

1. Bude takardar Excel da ake so kuma danna kan tantanin halitta inda kake son shigo da bayanai daga fayil ɗin . Csv or .txt.
2. A kan Babba shafin data (Data) a cikin sashe Samu bayanan waje (Samu bayanan waje) danna Daga Rubutu (Daga rubutu).
3. Nemo fayil ɗin . Csvwanda kuke son shigo da shi, zaɓi shi kuma danna Import (Shigo), ko kawai danna sau biyu akan fayil ɗin CSV da ake so.
4. Mayen Shigo da Rubutun zai buɗe, kawai kuna buƙatar bin matakansa.

Kafin mu ci gaba, da fatan za a kalli hoton da ke ƙasa, yana nuna ainihin fayil ɗin CSV da sakamakon da ake so a cikin Excel. Ina fatan wannan zai taimaka muku ƙarin fahimtar dalilin da yasa muke zaɓar wasu saitunan a cikin misali mai zuwa.

4. 
   • Mataki 1. Zaɓi tsarin bayanai da lambar layin da za a fara sayo. Mafi sau da yawa zaba Iyakance (Tare da masu raba) kuma daga kirtani 1. Wurin samfoti a ƙasan akwatin maganganu na mayen yana nuna ƴan bayanan farko na fayil ɗin CSV da aka shigo da su.
   • Mataki 2. A cikin wannan mataki, kuna buƙatar zaɓar masu iyakancewa da ƙarshen layi. mai ba da agaji (Delimiter) shine halayyar da ke raba dabi'u a cikin fayil ɗin CSV. Idan fayil ɗin CSV ɗin ku yana amfani da alamar da ba ta cikin jerin da aka tsara, sannan duba akwatin Other (Sauran) kuma shigar da halin da ake so. A cikin misalinmu, mun ƙayyade tab (Tab hali) da Takaice (Wakafi) ta yadda kowane samfur (su ne tab-ware) ya fara a kan sabon layi, kuma samfurin bayanai, kamar ID da tallace-tallace data (sun kasance wakafi-rarra), ana sanya su a cikin daban-daban sel.Cancantar rubutu (line terminator) hali ne da ke tattare da ƙima guda ɗaya. Duk rubutun da ke kewaye tsakanin irin waɗannan haruffa, kamar "text1, text2", za a shigo da su azaman ƙima ɗaya, koda kuwa rubutun ya ƙunshi harafin da kuka ayyana azaman mai iyakancewa. A cikin wannan misalin, mun ayyana waƙafi azaman mai iyakancewa da alamar ambato azaman mai ƙare layin. Sakamakon haka, za a shigo da duk lambobin da ke da maɓalli na decimal (wanda kuma waƙafi ne a cikin yanayinmu!) zuwa cikin tantanin halitta ɗaya, kamar yadda ake iya gani a cikin samfoti a cikin hoton da ke ƙasa. Idan ba mu ƙididdige ƙididdiga a matsayin mai ƙare kirtani ba, to duk lambobi za a shigo da su cikin sel daban-daban.

     

   • Mataki 3. Duba cikin yankin Duban bayanai (Sample data parsing). Idan kuna farin ciki da yadda bayananku suke, to danna maɓallin Gama (Shirya).

tip: Idan fayil ɗin CSV ɗinku yana amfani da waƙafi fiye da ɗaya a jere ko wasu haruffa a jere, duba akwatin. Bi da iyakoki a jere a matsayin ɗaya (Kidaya masu iyakance a jere a matsayin ɗaya) don guje wa sel mara komai.

5. Zaɓi ko don liƙa bayanan da aka shigo da su a cikin takardar data kasance ko sabon takarda, sannan danna OKdon gama shigo da fayil ɗin CSV cikin Excel.

tip: Kuna iya danna maɓallin Properties (Properties) don daidaita saitunan ci-gaba kamar sabuntawa, shimfidawa, da tsara bayanan da aka shigo da su.

lura: Idan fayil ɗin CSV ɗin ku ya ƙunshi lambobi ko kwanan wata, Excel bazai canza su daidai ba. Don canza tsarin bayanan da aka shigo da su, zaɓi shafi(s) tare da kurakurai, danna-dama akan su kuma zaɓi daga menu na mahallin. Tsara Kwayoyin (tsarin salula).

Canza CSV zuwa Excel: Matsaloli da Magani

An yi amfani da tsarin CSV sama da shekaru 30, amma duk da dogon tarihinsa, ba a taɓa yin shi a hukumance ba. Sunan CSV (Wakafi-Raba Ƙimar) ya fito ne daga amfani da waƙafi don raba filayen bayanai. Amma wannan yana cikin ka'idar. A zahiri, yawancin abubuwan da ake kira fayilolin CSV suna amfani da wasu haruffa don raba bayanai, misali:

• Shafukan – Fayilolin TSV (Dabi'un da aka raba tab)
• Semicolon – Fayilolin SCSV (darajar da aka raba na semicolon)

Wasu bambance-bambancen fayilolin CSV suna raba filayen bayanai tare da ƙididdiga guda ɗaya ko biyu, wasu suna buƙatar Alamar Bidiyo ta Unicode (BOM), kamar UTF-8, don fassara Unicode daidai.

Wannan rashin ma'auni yana haifar da matsaloli iri-iri waɗanda za ku iya fuskanta yayin ƙoƙarin ku canza fayil ɗin Excel zuwa csv, kuma musamman lokacin shigo da fayil na CSV cikin Excel. Bari mu dubi abubuwan da aka sani, farawa da mafi yawan al'amuran.

• Ana nuna fayil ɗin CSV ba daidai ba a cikin Excel (duk bayanan an sanya su a cikin shafi na farko)
• Manyan sifilai sun ɓace lokacin buɗe fayil ɗin CSV a cikin Excel
• An canza dabi'u zuwa kwanan wata lokacin shigo da fayil na CSV cikin Excel

Fayil ɗin CSV baya nunawa daidai a cikin Excel

Kwayar cututtuka: Kuna ƙoƙarin buɗe fayil ɗin CSV a cikin Excel kuma duk bayanan sun ƙare a shafi na farko.

dalili: Tushen matsalar shine saitunan yanki na Windows da saitunan harshe da fayil ɗin CSV ɗin ku suna da mabambantan iyakoki. A Arewacin Amurka da wasu ƙasashe, tsohowar lissafin waƙafi ne. Yayin da a cikin ƙasashen Turai ana amfani da waƙafi azaman mai raba wuri na goma kuma mai raba filin jeri shine semicolon.

Yanke shawara: Akwai hanyoyin magance wannan matsala da yawa. Kuna iya bincika shawarwarin da ke ƙasa da sauri kuma ku zaɓi mafi dacewa don takamaiman aikinku.

1. Ƙayyade madaidaicin madaidaicin kai tsaye a cikin fayil ɗin CSV. Bude fayil ɗin CSV a kowane editan rubutu (ko da faifan rubutu na yau da kullun zai yi) kuma liƙa rubutu mai zuwa akan layin farko. Lura cewa dole ne wannan ya zama keɓaɓɓen layi kafin kowane bayanan:
   • Don saita mai raba waƙafi: Sep
   • Don saita mai raba zuwa wani yanki mai lamba: sep=;

   Kamar yadda ƙila kuka yi tsammani, ta wannan hanyar zaku iya saita kowane hali azaman mai rarrabawa ta hanyar ƙididdige shi nan da nan bayan alamar daidai.

2. Zaɓi abin da ake so a cikin Excel. A cikin Excel 2013 ko 2010 akan shafin data (Data) a cikin sashe Data Tools (Aiki tare da bayanai) danna Rubutu Zuwa Rubutu (Rubutu ta ginshiƙai).Lokacin da Mayen Rubutun Rukunin ya fara, a mataki na farko, zaɓi tsarin bayanai Iyakance (tare da separators) kuma latsa Next (Ƙari). A mataki na biyu, zaɓi iyakar da ake so kuma danna Gama (Shirya).

   

3. Canza tsawo daga . Csv on .txt. Buɗe fayil .txt a cikin Excel zai kaddamar da Wizard Import na Rubutu, kuma za ku iya zaɓar abin da ake so, kamar yadda aka bayyana a cikin sashin Yadda ake shigo da CSV cikin Excel.
4. Bude fayil ɗin CSV tare da ƙaramin yanki a matsayin mai rarrabawa ta amfani da VBA. Anan akwai samfurin VBA code wanda ke buɗe fayil ɗin CSV a cikin Excel wanda ke amfani da ƙaramin yanki azaman mai raba. An rubuta lambar a 'yan shekarun da suka gabata don sigogin Excel na baya (2000 da 2003), amma idan kun saba da VBA, bai kamata ku sami matsala sabuntawa ko canza shi don aiki tare da fayilolin CSV masu wakafi ba.

lura: Duk mafita da aka nuna suna canza mai iyakance ga fayil ɗin CSV da aka bayar. Idan kana son canza tsoho separator sau ɗaya kuma gaba ɗaya, to, mafita mai zuwa zata dace da kai.

5. Muna canza masu rarrabawa a cikin saitunan ma'auni na yanki. latsa Gida (fara) da gudu Control Panel (Control Panel), danna abu Yanki da Yare (Ma'aunin yanki) > Ƙarin Saituna (Ƙarin zaɓuɓɓuka). Akwatin maganganu zai buɗe Siffanta Tsarin (Tsarin tsarin) wanda a ciki kake buƙatar zaɓar digo (.) don siga Alamar Decimal (Integer/Decimal Separator), kuma saita waƙafi (,) don siga Mai raba lissafin (Mai raba abubuwan da aka jera).

Bayanan Fassara: An ba da waɗannan saitunan don ƙayyadaddun Ingilishi na Excel (da wasu ƙasashe). Don ganowa, zai zama gama gari don amfani da waƙafi azaman mai raba ƙima da maƙalli don raba abubuwan jeri.

5. Matsa sau biyu OKdon rufe akwatunan maganganu - kun gama! Daga yanzu, Microsoft Excel zai buɗe kuma ya nuna duk fayilolin CSV (wakafi masu iyaka) daidai.

lura: Saita Cibiyar Kula da Windows zuwa rarrabuwa na ƙima da jeri abubuwa zai canza saitunan halayen tsoho don duk shirye-shiryen da ke kan kwamfutarka, ba kawai Microsoft Excel ba.

Sifilai masu jagora suna ɓacewa lokacin buɗe fayil ɗin CSV a cikin Excel

Kwayar cututtuka: Fayil ɗin ku na CSV ya ƙunshi ƙima tare da manyan sifilai kuma waɗannan sifilai sun ɓace lokacin buɗe fayil ɗin CSV a cikin Excel.

dalili: Ta hanyar tsoho, Microsoft Excel yana nuna fayil ɗin CSV a cikin tsari Janar (Na kowa), wanda a cikinsa ake yanke manyan sifilai.

Yanke shawara: Maimakon buɗe fayil ɗin .csv a cikin Excel, gudanar da Wizard Import na Rubutu kamar yadda muka yi a baya don canza fayil ɗin CSV zuwa Excel.

A mataki na 3 na maye, zaɓi ginshiƙan da ke ɗauke da ƙima tare da manyan sifilai kuma canza tsarin waɗannan ginshiƙan zuwa rubutu. Wannan shine yadda kuke canza fayil ɗin CSV ɗin ku zuwa Excel, kuna ajiye sifili a wurin.

Excel yana canza wasu dabi'u zuwa kwanan wata lokacin buɗe fayil ɗin CSV

Kwayar cututtuka: Wasu dabi'u a cikin fayil ɗin CSV ɗinku suna kama da kwanakin, kuma Excel zai canza irin waɗannan dabi'u ta atomatik daga tsarin rubutu zuwa tsarin kwanan wata.

dalili: Kamar yadda aka ambata a sama, Excel yana buɗe fayil ɗin CSV a cikin tsari Janar (General), wanda ke canza dabi'u-kamar kwanan wata daga tsarin rubutu zuwa tsarin kwanan wata. Misali, idan ka buɗe fayil ɗin CSV mai ɗauke da shiga mai amfani, shigar da “Apr23” za a canza zuwa kwanan wata.

Yanke shawara: Maida fayil ɗin CSV zuwa Excel ta amfani da Mayen Shigo da Rubutun. A mataki na 3 na maye, zaɓi ginshiƙai tare da bayanan da ke kama da kwanakin kuma canza tsarin shafi zuwa rubutu.

Idan kana buƙatar cimma akasin sakamako, wato, a cikin wani shafi, canza dabi'u zuwa kwanan wata, sannan saita tsarin. Rana (Kwanan wata) kuma zaɓi tsarin kwanan wata da ya dace daga jerin zaɓuka.

Yadda ake shigo da fayilolin CSV da yawa cikin Excel

Ina tsammanin kun san cewa Microsoft Excel yana ba ku damar buɗe fayilolin CSV da yawa ta amfani da umarnin Bude (Bude).

1. A kan Babba shafin Fillet (Fayil) danna Bude (Buɗe) kuma daga jerin zaɓuka a ƙasan dama na akwatin maganganu, zaɓi Fayilolin Rubutu (Faylolin rubutu).
2. Don zaɓar fayiloli da yawa a jere, danna fayil ɗin farko, sannan danna ka riƙe Motsi, danna kan fayil ɗin ƙarshe. Duk waɗannan fayilolin, da duk abin da ke tsakanin, za a zaɓi. Don zaɓar fayilolin da ba a jere ba, riƙe maɓallin Ctrl kuma danna kowane fayil . Csvkana so ka bude.
3. Lokacin da aka zaɓi duk fayilolin CSV da ake so, danna maɓallin Bude (Bude).

Wannan hanya ce mai sauƙi da sauri, kuma za mu iya kiranta mai kyau, idan ba don yanayi ɗaya ba - kowane fayil na CSV yana buɗewa ta wannan hanyar azaman littafin aikin Excel daban. A aikace, jujjuya baya da gaba tsakanin fayilolin Excel da yawa na iya zama da wahala da nauyi.

Ina fatan yanzu zaku iya canza kowane fayil ɗin CSV zuwa Excel cikin sauƙi. Idan kuna da tambayoyi ko matsaloli, jin daɗin rubuto min a cikin sharhi. Kuma na gode da hakurin duk wanda ya kware wajen karanta wannan doguwar labarin har zuwa karshe! 🙂