Hloov CSV rau Excel: Yuav Ua Li Cas Ntshuam CSV Cov Ntaub Ntawv rau hauv Excel Spreadsheets

Hauv tsab xov xwm no, koj yuav pom 2 txoj hauv kev yooj yim los hloov CSV cov ntaub ntawv rau Excel. Tsis tas li ntawd, koj yuav kawm paub yuav ua li cas import ntau cov ntaub ntawv CSV rau hauv Excel thiab yuav ua li cas nrog cov xwm txheej uas ib feem ntawm cov ntaub ntawv los ntawm CSV cov ntaub ntawv tsis tso tawm kom raug ntawm daim ntawv Excel.

Tsis ntev los no, peb pib kawm cov yam ntxwv ntawm CSV (Comma-Separated Values) hom thiab ntau yam txoj kev los hloov cov ntaub ntawv Excel rau CSV. Niaj hnub no peb yuav ua tus txheej txheem rov qab - import CSV rau hauv Excel.

Kab lus no yuav qhia koj yuav ua li cas qhib CSV hauv Excel thiab yuav ua li cas rau import ntau CSV cov ntaub ntawv tib lub sijhawm. Peb kuj tseem yuav txheeb xyuas qhov teeb meem tshwm sim thiab muab cov kev daws teeb meem zoo tshaj plaws.

• Yuav ua li cas Hloov CSV rau Excel
• Hloov CSV rau Excel: Teeb meem thiab kev daws teeb meem

Yuav ua li cas Hloov CSV rau Excel

Yog tias koj xav rub qee cov ntaub ntawv rau hauv Excel daim ntawv los ntawm koj lub tuam txhab cov ntaub ntawv, thawj lub tswv yim uas los rau hauv siab yog xa cov ntaub ntawv mus rau CSV cov ntaub ntawv thiab tom qab ntawd xa cov ntaub ntawv CSV rau hauv Excel.

Muaj 3 txoj hauv kev los hloov CSV rau Excel: Koj tuaj yeem qhib cov ntaub ntawv nrog txuas ntxiv . Csv ncaj qha hauv Excel, nyem ob npaug rau cov ntaub ntawv hauv Windows Explorer, lossis import CSV rau hauv Excel raws li cov ntaub ntawv sab nraud. Hauv qab no, kuv yuav piav qhia txog peb txoj hauv kev no thiab taw qhia qhov zoo thiab qhov tsis zoo ntawm txhua tus ntawm lawv.

• Txoj Kev 1: Qhib cov ntaub ntawv CSV hauv Excel
• Txoj Kev 2: Qhib CSV cov ntaub ntawv hauv Excel siv Windows Explorer
• Txoj Kev 3: Import CSV rau hauv Excel

Yuav ua li cas qhib CSV cov ntaub ntawv hauv Excel

Txawm hais tias CSV cov ntaub ntawv raug tsim nyob rau hauv lwm qhov kev pab cuam, koj tuaj yeem qhib nws ua ib phau ntawv ua haujlwm Excel siv cov lus txib Qhib (Qhib).

Nco ntsoov: Qhib cov ntaub ntawv CSV hauv Excel tsis hloov cov hom ntaub ntawv. Hauv lwm lo lus, cov ntaub ntawv CSV yuav tsis raug hloov mus rau Excel cov ntaub ntawv (.xls lossis .xlsx hom), nws yuav khaws nws hom qub (.csv lossis .txt).

1. Tua tawm Microsoft Excel, tab Tsev (Lub Tsev) nyem Qhib (Qhib).
2. Ib lub dialog box yuav tshwm sim Qhib (Qhib ib daim ntawv), nyob rau hauv cov npe drop-down nyob rau sab xis ces kaum, xaiv Ntawv Cov Ntaub Ntawv (Cov ntawv nyeem).
3. Nrhiav CSV cov ntaub ntawv hauv Windows Explorer thiab qhib nws los ntawm ob-nias ntawm nws.

Yog tias koj qhib CSV cov ntaub ntawv, Excel yuav qhib nws tam sim ntawd los ntawm kev ntxig cov ntaub ntawv rau hauv phau ntawv ua haujlwm tshiab Excel. Yog tias koj qhib cov ntawv nyeem (.txt), Excel yuav tso tawm Cov Ntawv Ntshuam Wizard. Nyeem ntxiv txog qhov no hauv Importing CSV rau hauv Excel.

Nco ntsoov: Thaum Microsoft Excel qhib CSV cov ntaub ntawv, nws siv lub neej ntawd formatting nqis los xyuas kom meej yuav ua li cas import txhua kab ntawm cov ntaub ntawv.

Yog tias cov ntaub ntawv sib phim yam tsawg kawg yog ib qho ntawm cov khoom hauv qab no, ces siv Cov Ntawv Ntshuam Wizard:

• Cov ntaub ntawv CSV siv tus lej sib txawv;
• Cov ntaub ntawv CSV siv ntau hom hnub;
• Koj tab tom hloov cov ntaub ntawv uas suav nrog cov lej nrog tus lej xoom, thiab koj xav kom tus lej ntawd;
• Koj xav saib ua ntej yuav ua li cas cov ntaub ntawv los ntawm CSV cov ntaub ntawv yuav raug xa mus rau hauv Excel;
• Koj xav tau kev hloov pauv ntau dua hauv koj txoj haujlwm.

Txhawm rau ua kom Excel tso Cov Ntawv Ntshuam Wizard, koj tuaj yeem hloov cov ntaub ntawv txuas ntxiv los ntawm . Csv on .txt (ua ntej qhib cov ntaub ntawv), lossis import CSV rau hauv Excel raws li tau piav qhia hauv qab no.

Yuav ua li cas qhib CSV cov ntaub ntawv siv Windows Explorer

Nov yog txoj hauv kev sai tshaj plaws los qhib CSV hauv Excel. Hauv Windows Explorer, ob npaug nias rau ntawm cov ntaub ntawv . Csv, thiab nws yuav qhib raws li phau ntawv ua haujlwm Excel tshiab.

Txawm li cas los xij, txoj kev no tsuas yog ua haujlwm yog tias Microsoft Excel tau teeb tsa raws li qhov kev zov me nyuam rau qhib cov ntaub ntawv. . Csv. Yog tias muaj, koj yuav pom lub cim paub hauv Windows Explorer ib sab ntawm cov ntaub ntawv npe.

Yog tias Excel tsis yog qhov program ua ntej, ntawm no yog li cas koj tuaj yeem kho nws:

1. Txoj nyem rau ntawm tej ntaub ntawv . Csv hauv Windows Explorer thiab hauv cov ntawv qhia zaub mov uas qhib, nyem Qhib nrog (Qhib nrog) > Xaiv qhov kev zov me nyuam (Xaiv qhov program).
2. xaiv Excel nyob rau hauv daim ntawv teev cov kev pab cuam pom zoo, xyuas kom meej tias muaj ib tug checkmark rau qhov kev xaiv Ib txwm siv qhov kev xaiv xaiv rau qhib hom ntaub ntawv no (Txuas siv daim ntawv thov xaiv rau hom ntaub ntawv no) thiab nyem OK.

Yuav ua li cas Import CSV rau Excel

Ua li no koj tuaj yeem import cov ntaub ntawv los ntawm cov ntaub ntawv . Csv rau daim ntawv Excel uas twb muaj lawm lossis tshiab. Tsis zoo li ob txoj kev dhau los, nws tsis yog qhib CSV hauv Excel xwb, tab sis nws hloov cov hom ntawv . Csv в . Xlsx (yog tias koj siv Excel 2007, 2010 lossis 2013) lossis .xl ua (hauv versions ntawm Excel 2003 thiab ua ntej).

1. Qhib cov ntawv Excel uas xav tau thiab nyem rau ntawm lub xov tooj ntawm tes uas koj xav import cov ntaub ntawv los ntawm cov ntaub ntawv . Csv or .txt.
2. Hauv Advanced tab Cov ntaub ntawv (Cov ntaub ntawv) hauv ntu Tau cov ntaub ntawv sab nraud (Tau txais cov ntaub ntawv sab nraud) nyem Los ntawm Text (Los ntawm cov ntawv nyeem).
3. Nrhiav cov ntaub ntawv . Csvuas koj xav import, xaiv nws thiab nias Ntshuam (Ntshuam), los yog cia li ob-nias ntawm qhov xav tau CSV cov ntaub ntawv.
4. Lub Text Import Wizard yuav qhib, koj tsuas yog yuav tsum ua raws nws cov kauj ruam.

Ua ntej peb txav mus, thov saib cov screenshot hauv qab no, nws pom cov ntaub ntawv CSV thawj thiab cov txiaj ntsig xav tau hauv Excel. Kuv vam tias qhov no yuav pab koj nkag siab zoo dua yog vim li cas peb xaiv qee qhov chaw hauv qhov piv txwv hauv qab no.

4. 
   • Kauj ruam 1. Xaiv cov ntaub ntawv hom thiab kab xov tooj uas yuav pib importing. Feem ntau xaiv Txwv tsis pub (Nrog cov separators) thiab los ntawm ib txoj hlua 1. Qhov chaw saib ua ntej nyob rau hauv qab ntawm tus wizard lub dialog box qhia thawj ob peb cov ntaub ntawv ntawm cov ntaub ntawv CSV tuaj.
   • Kauj ruam 2. Hauv cov kauj ruam no, koj yuav tsum xaiv tus delimiters thiab kab terminator. tus lej (Delimiter) yog tus cwj pwm uas cais cov txiaj ntsig hauv CSV cov ntaub ntawv. Yog tias koj cov ntaub ntawv CSV siv lub cim uas tsis nyob hauv daim ntawv thov, ces kos lub thawv lwm yam (Lwm yam) thiab nkag mus rau tus yam ntxwv xav tau. Hauv peb qhov piv txwv, peb tau teev tseg Tab (Tab cim) thiab Comma (Comma) kom txhua yam khoom (lawv yog tab-sib cais) pib ntawm kab tshiab, thiab cov ntaub ntawv cov khoom lag luam, xws li ID thiab cov ntaub ntawv muag khoom (lawv yog comma-sib cais), muab tso rau hauv cov hlwb sib txawv.Text qualifier (line terminator) yog ib tug cwj pwm uas encloses tus nqi ntawm tus kheej. Tag nrho cov ntawv uas muaj nyob nruab nrab ntawm cov cim, xws li "text1, text2", yuav raug coj los ua ib qho nqi, txawm tias cov ntawv muaj cov cim koj tau teev tseg raws li tus lej. Hauv qhov piv txwv no, peb tau teev tus lej cim raws li tus lej cim thiab cov lus hais raws li kab lus terminator. Raws li qhov tshwm sim, txhua tus lej nrog tus lej sib cais (uas kuj yog ib qho comma hauv peb rooj plaub!) yuav raug xa mus rau hauv ib lub xovtooj, raws li tuaj yeem pom hauv thaj chaw saib ua ntej hauv daim duab hauv qab no. Yog hais tias peb tsis qhia cov quotes raws li ib txoj hlua terminator, ces tag nrho cov lej yuav raug xa mus rau hauv cov hlwb sib txawv.

     

   • Kauj ruam 3. Saib rau hauv cheeb tsam Cov ntaub ntawv saib ua ntej (Sample data parsing). Yog tias koj txaus siab rau qhov koj cov ntaub ntawv zoo li cas, ces nyem lub pob Tag (Npaj).

Tswv yim: Yog tias koj cov ntaub ntawv CSV siv ntau tshaj ib lub cim sib law liag lossis lwm tus cim delimiter hauv ntu, kos lub thawv Kho cov kab sib law liag ua ib qho (Suav sib law liag delimiters ua ib qho) kom tsis txhob muaj cov hlwb khoob.

5. Xaiv seb puas yuav muab cov ntaub ntawv nkag mus rau hauv ib daim ntawv uas twb muaj lawm lossis ib daim ntawv tshiab, thiab nyem OKkom tiav import cov ntaub ntawv CSV rau hauv Excel.

Tswv yim: Koj tuaj yeem nias lub khawm Properties (Properties) los teeb tsa cov kev teeb tsa siab tshaj xws li hloov kho, teeb tsa, thiab formatting rau cov ntaub ntawv nkag.

Nco ntsoov: Yog tias koj cov ntaub ntawv CSV muaj cov lej lossis hnub, Excel yuav tsis hloov lawv kom raug. Txhawm rau hloov cov hom ntawv ntawm cov ntaub ntawv tuaj txawv teb chaws, xaiv kab (s) uas tsis raug, txoj cai-nias rau ntawm lawv thiab xaiv los ntawm cov ntawv qhia zaub mov. Format cell (cell format).

Hloov CSV rau Excel: Teeb meem thiab kev daws teeb meem

CSV hom tau siv rau ntau tshaj 30 xyoo, tab sis txawm tias nws muaj keeb kwm ntev, nws yeej tsis tau raug sau tseg. Lub npe CSV (Comma-Separated Values) los ntawm kev siv commas cais cov ntaub ntawv. Tab sis qhov ntawd yog nyob rau hauv txoj kev xav. Qhov tseeb, ntau cov ntaub ntawv CSV siv lwm cov cim los cais cov ntaub ntawv, piv txwv li:

• Tabs – TSV files (tab-separated values)
• Semicolon - SCSV cov ntaub ntawv (semicolon cais qhov tseem ceeb)

Qee qhov kev hloov pauv ntawm CSV cov ntaub ntawv cais cov ntaub ntawv nrog ib lossis ob nqe lus, lwm tus xav tau Unicode Byte Sequence Marker (BOM), xws li UTF-8, los txhais Unicode kom raug.

Qhov tsis muaj cov qauv no tsim ntau yam teeb meem uas koj yuav ntsib thaum sim ua Hloov ua lwm yam ntaub ntawv excel rau csv, thiab tshwj xeeb tshaj yog thaum importing CSV cov ntaub ntawv rau hauv Excel. Cia peb saib ntawm cov teeb meem paub, pib nrog ntau tshaj.

• CSV cov ntaub ntawv tau tshwm sim tsis raug hauv Excel (tag nrho cov ntaub ntawv tau muab tso rau hauv thawj kab)
• Ua tus lej poob thaum qhib CSV cov ntaub ntawv hauv Excel
• Cov nqi hloov mus rau hnub tim thaum importing CSV cov ntaub ntawv rau hauv Excel

CSV cov ntaub ntawv tsis tso tawm kom raug hauv Excel

Cov tsos mob: Koj tab tom sim qhib CSV cov ntaub ntawv hauv Excel thiab tag nrho cov ntaub ntawv xaus rau hauv thawj kab.

Vim li cas: Lub hauv paus ntawm qhov teeb meem yog tias koj lub Windows hauv cheeb tsam thiab cov lus teeb tsa thiab koj cov ntaub ntawv CSV muaj qhov sib txawv. Hauv North America thiab qee lub tebchaws, cov npe sib cais ua ntej yog comma. Thaum nyob rau hauv cov teb chaws Europe comma yog siv los ua zauv qhov chaw cais thiab cov npe teb cais yog semicolon.

Kev txiav txim siab: Muaj ntau ntau txoj kev daws teeb meem rau qhov teeb meem no. Koj tuaj yeem saib sai los ntawm cov lus pom zoo hauv qab no thiab xaiv qhov tsim nyog tshaj plaws rau koj txoj haujlwm tshwj xeeb.

1. Qhia meej qhov raug txiav ncaj qha rau hauv cov ntaub ntawv CSV. Qhib cov ntaub ntawv CSV hauv cov ntawv nyeem (txawm tias cov ntawv sau ib txwm yuav ua) thiab muab cov ntawv hauv qab no tso rau ntawm thawj kab. Nco ntsoov tias qhov no yuav tsum yog ib kab sib cais ua ntej lwm cov ntaub ntawv:
   • Txhawm rau teeb tsa lub comma separator: Sep
   • Yuav kom teem lub separator rau ib tug semicolon: sib =;

   Raws li koj tau twv, nyob rau hauv txoj kev no koj tuaj yeem teeb tsa lwm tus cim ua tus cais los ntawm kev qhia nws tam sim ntawd tom qab kos npe sib npaug.

2. Xaiv qhov xav tau delimiter hauv Excel. Hauv Excel 2013 lossis 2010 ntawm lub tab Cov ntaub ntawv (Cov ntaub ntawv) hauv ntu Cov cuab yeej ntaub ntawv (Ua haujlwm nrog cov ntaub ntawv) nyem Text To Kum (Tau ntawv los ntawm kab).Thaum Kem Text Wizard pib, thawj kauj ruam, xaiv cov ntaub ntawv hom Txwv tsis pub (nrog cov khoom sib cais) thiab nias Tom ntej no (Ntxiv). Hauv kauj ruam thib ob, xaiv qhov xav tau tus lej thiab nyem Tag (Npaj).

   

3. Hloov qhov txuas ntxiv los ntawm . Csv on .txt. Qhib cov ntaub ntawv .txt hauv Excel yuav tso tawm Cov Ntawv Ntshuam Wizard, thiab koj tuaj yeem xaiv qhov xav tau delimiter, raws li tau piav qhia hauv ntu Yuav ua li cas import CSV rau hauv Excel.
4. Qhib cov ntaub ntawv CSV nrog lub semicolon ua tus cais siv VBA. Ntawm no yog ib qho piv txwv VBA code uas qhib CSV cov ntaub ntawv hauv Excel uas siv lub semicolon ua tus cais. Cov cai tau sau ob peb xyoos dhau los rau yav dhau los versions ntawm Excel (2000 thiab 2003), tab sis yog tias koj paub zoo nrog VBA, koj yuav tsum tsis muaj teeb meem hloov kho lossis hloov nws ua haujlwm nrog comma delimited CSV cov ntaub ntawv.

Nco ntsoov: Txhua qhov kev daws teeb meem pom tsuas yog hloov pauv tus lej rau CSV cov ntaub ntawv muab. Yog tias koj xav hloov lub default separator ib zaug thiab rau tag nrho, ces cov kev daws teeb meem hauv qab no yuav haum koj.

5. Peb hloov cov khoom sib cais hauv qhov chaw ntawm cov qauv hauv cheeb tsam. xovxwm Tsev (pib) thiab khiav Tswj vaj huam sib luag (Control Vaj Huam Sib Luag), nyem yam khoom Thaj Tsam thiab Lus (Cov qauv hauv cheeb tsam) > Chaw Ntxiv (Kev xaiv ntxiv). Ib lub dialog box yuav qhib Customize hom ntawv (Hom ntawv teeb tsa) uas koj yuav tsum xaiv lub dot (.) rau qhov parameter Decimal cim (Integer/Decimal Separator), thiab teeb lub cim (,) rau qhov parameter Sau npe cais (Sau cov khoom cais).

Tus neeg txhais lus ceeb toom: Cov chaw no tau muab rau cov lus Askiv hauv zos ntawm Excel (thiab ntau lub tebchaws). Rau qhov chaw hauv cheeb tsam, nws yuav siv ntau dua los siv tus lej cim raws li tus lej lej sib cais thiab tus lej cim cais cais cov npe.

5. Ob chav kais OKkaw cov dialog boxes - koj ua tiav! Txij no mus, Microsoft Excel yuav qhib thiab tso saib tag nrho cov ntaub ntawv CSV (comma delimited) kom raug.

Nco ntsoov: Teem lub Windows Control Vaj Huam Sib Luag rau cov lej sib cais thiab sau cov khoom yuav hloov pauv tus cwj pwm tsis zoo rau txhua qhov haujlwm hauv koj lub computer, tsis yog Microsoft Excel xwb.

Ua ntej xoom poob thaum qhib CSV cov ntaub ntawv hauv Excel

Cov tsos mob: Koj cov ntaub ntawv CSV muaj qhov tseem ceeb nrog cov lej ua ntej thiab cov xoom poob thaum qhib cov ntaub ntawv CSV hauv Excel.

Vim li cas: Los ntawm lub neej ntawd, Microsoft Excel qhia cov ntaub ntawv CSV hauv hom General (Common), nyob rau hauv uas coj xoom yog truncated.

Kev txiav txim siab: Es tsis txhob qhib cov ntaub ntawv .csv hauv Excel, khiav Cov Ntawv Ntshuam Wizard raws li peb tau ua ua ntej los hloov cov ntaub ntawv CSV rau Excel.

Hauv kauj ruam 3 ntawm tus wizard, xaiv cov kab uas muaj qhov tseem ceeb nrog rau xoom thiab hloov cov hom ntawm cov kab ntawv no mus rau cov ntawv. Qhov no yog li cas koj hloov koj cov ntaub ntawv CSV rau Excel, khaws cov xoom nyob rau hauv qhov chaw.

Excel hloov qee qhov txiaj ntsig rau hnub thaum qhib CSV cov ntaub ntawv

Cov tsos mob: Qee qhov txiaj ntsig hauv koj cov ntaub ntawv CSV zoo li hnub, thiab Excel yuav cia li hloov cov txiaj ntsig zoo li no los ntawm cov ntawv nyeem mus rau hnub tim.

Vim li cas: Raws li tau hais los saum no, Excel qhib cov ntaub ntawv CSV hauv hom General (General), uas hloov cov hnub zoo li qhov tseem ceeb ntawm cov ntawv nyeem mus rau hom ntawv hnub. Piv txwv li, yog tias koj qhib CSV cov ntaub ntawv uas muaj cov neeg siv nkag mus, nkag "Apr23" yuav hloov mus rau hnub tim.

Kev txiav txim siab: Hloov cov ntaub ntawv CSV rau Excel siv Text Import Wizard. Hauv kauj ruam 3 ntawm tus wizard, xaiv kab nrog cov ntaub ntawv uas zoo li hnub thiab hloov kab ntawv rau kab ntawv.

Yog tias koj xav tau kom ua tiav cov txiaj ntsig rov qab, uas yog, nyob rau hauv ib kem, hloov cov txiaj ntsig rau hnub, ces teem lub hom ntawv Hnub (Hnub) thiab xaiv cov hnub tsim nyog ntawm daim ntawv teev npe.

Yuav ua li cas import ntau CSV cov ntaub ntawv rau hauv Excel

Kuv xav tias koj paub tias Microsoft Excel tso cai rau koj qhib ntau cov ntaub ntawv CSV siv cov lus txib Qhib (Qhib).

1. Hauv Advanced tab Fillet (Cov ntaub ntawv) nyem Qhib (Qhib) thiab los ntawm daim ntawv teev npe poob rau hauv qab txoj cai ntawm lub dialog box, xaiv Ntawv Cov Ntaub Ntawv (Cov ntawv nyeem).
2. Txhawm rau xaiv ntau cov ntaub ntawv hauv kab, nyem rau ntawm thawj cov ntaub ntawv, tom qab ntawd nias thiab tuav ua haujlwm, nyem rau ntawm cov ntaub ntawv kawg. Ob qho ntawm cov ntaub ntawv no, nrog rau txhua yam hauv nruab nrab, yuav raug xaiv. Txhawm rau xaiv cov ntaub ntawv uas tsis nyob hauv kab, tuav tus yuam sij Ctrl thiab nyem rau ntawm txhua cov ntaub ntawv . Csvkoj xav qhib.
3. Thaum txhua yam xav tau CSV cov ntaub ntawv raug xaiv, nyem lub pob Qhib (Qhib).

Txoj kev no yooj yim thiab ceev, thiab peb tuaj yeem hu nws zoo heev, yog tias tsis yog rau ib qho xwm txheej - txhua CSV cov ntaub ntawv qhib rau hauv txoj kev no raws li phau ntawv Excel cais. Hauv kev xyaum, kev hloov rov qab los ntawm ntau cov ntaub ntawv Excel qhib tuaj yeem ua rau tsis yooj yim thiab hnyav.

Kuv vam tias tam sim no koj tuaj yeem yooj yim hloov cov ntaub ntawv CSV rau Excel. Yog tias koj muaj lus nug lossis teeb meem, thov sau ntawv rau kuv hauv cov lus. Thiab ua tsaug rau kev ua siab ntev ntawm txhua tus neeg uas tau nyeem cov ntawv ntev no mus txog qhov kawg! 🙂