Faylasha qoraalka ku salaysan waa mid ka mid ah hababka ugu caansan ee lagu kaydiyo xogta adduunka maanta. Tani waa sababta oo ah faylasha qoraalka, guud ahaan, waxay isticmalaan booska ugu dambeeya oo ay ugu fudud yihiin in la kaydiyo. Mahadsanid, aad bay u fududahay in la geliyo CSV midkood ama TSV (qiimaha kala soocan) ama faylasha Microsoft Excel .

Haddii aad rabto inaad geliso CSV ama TSV xaashida shaqada ee Excel , kaliya waxaad u baahan tahay inaad si gaar ah u ogaato sida xogta faylka loo kala saaro. Looma baahna inaad ogaato faahfaahinta ku saabsan xogta, ilaa aad rabto inaad dib u habayn ku sameyso qiimayaashaas xargaha, tirooyinka, boqolleyda iyo ka badan.

Maqaalkan waxaad ku baran doontaa sida loo geliyo faylka CSV ama TSV midkood xaashidaada ^(TSV)Excel iyo sida dib loogu habeeyo xogtaas habka soo dejinta, si aad naftaada u badbaadiso waqti.

Sida Loo Geliyo Faylka CSV^{(A CSV File Into)} Xaashida Shaqada ee Excel^{(Excel Worksheet)}

Kahor intaadan gelin faylka CSV xaashidaada ^(CSV)Excel , waxaad u baahan tahay inaad xaqiijiso in faylka xogta uu dhab ahaantii kala go'ay (sidoo kale loo yaqaan "comma-delimited").

Hubi inuu yahay Fayl kala Saaran

Si tan loo sameeyo, fur Daaqadda Explorer^{(Window Explorer)} oo u gudub hagaha meesha uu faylka ku kaydsan yahay. Dooro menu-ka View oo hubi in muqaalka Horudhac^{(Preview pane)} la doortay.

Kadib, dooro faylka aad aaminsantahay in uu ku jiro xog aan hakad lahayn. Waa inaad ku aragtaa hakadka u dhexeeya qayb kasta oo xog ah oo ku jirta faylka qoraalka.

Tusaalaha hoose waxa uu ka yimid xog-ururin dawladeed oo ka kooban natiijooyinka buundooyinka ardayda ee Guddida Kulliyada SAT ee 2010.^{(SAT College Board)}

Sida aad arki karto, xariiqda koowaad waa xariiqda madaxa. Goob kasta waxaa lagu kala saaraa comma. Xariiq kasta oo kale oo intaas ka dambeeya waa xariiq xog, iyada oo dhibic kasta oo xog ah ay kala soocdo comma.

Tani waa tusaale ahaan sida uu u ekaado faylka leh qiyamka kala-saaran. Hadda oo aad xaqiijisay habaynta xogta ishaada, waxaad diyaar u tahay inaad geliso xaashidaada Excel .

Geli faylka CSV xaashidaada shaqo^{(Insert A CSV File Into Your Worksheet)}

Si aad u geliso isha faylka xogta CSV xaashidaada ^(CSV)Excel , fur xaashi shaqo oo banaan. 

1. Ka dooro Xogta^(Data) liiska
2. Ka dooro Hel Xogta^{(Get Data)} kooxda Xogta Hel^(Get) & Beddel^{(Transform Data)} ee ku yaal ribbon
3. Ka Dooro Faylka^{(From File)}
4. Ka dooro From Text/CSV

Fiiro gaar ah: Beddel ahaan, waxaad sidoo kale ka dooran kartaa From Text/CSV si toos ah ribbon.

Tani waxay furi doontaa browserka faylka. Ka baadho^(Browse) meesha aad ku kaydisay faylka CSV , dooro oo dooro Soo dejinta^(Import) .

Tani waxay furi doontaa saaxirka xogta soo dejinta. Excel wuxuu falanqeeyaa xogta soo socota wuxuuna dejiyaa dhammaan sanduuqyada hoos u dhaca iyadoo loo eegayo qaabka faylka xogta gelinta, iyadoo lagu saleynayo 200 ee saf ee ugu horreeya. 

Waxaad hagaajin kartaa falanqayntan adiga oo beddela mid ka mid ah goobaha soo socda:

• Asal ahaan faylka^{(File Origin)} : Haddii faylka uu yahay nooc kale oo xog ah sida ASCII ama UNICODE , waxaad taas ka beddeli kartaa halkan.
• Xaddidaad^(Delimiter) : Haddii semicolons ama boosaska loo isticmaalo xadaynta beddelka ah, taas waxaad ka dooran kartaa halkan.
• Ogaanshaha Nooca Xogta^{(Data Type Detection)} : Waxaad ku qasbi kartaa Excel inuu falanqeeyo iyadoo lagu saleynayo xogta oo dhan halkii aad ka ahaan lahayd 200 oo saf ee ugu horreeya.

Markaad diyaar u tahay inaad soo dejiso xogta, dooro Load xagga hoose ee daaqadan. Tani waxay keeni doontaa kaydka xogta oo dhan xaashidaada Excel ee banaan .

Markaad xogtaas ku haysato xaashida shaqada ee Excel , waxaad dib u habeyn kartaa xogtaas, safafka kooxda iyo tiirarka^{(group rows and columns)} , ama waxaad samayn kartaa hawlaha Excel^{(Excel functions)} ee xogta.

Soo rar Faylka CSV^{(CSV File Into)} ee Qaybaha kale ee Excel

Xaashida shaqadu maaha dhammaan inaad xogta CSV soo geliso. ^(CSV)Daaqadda ugu dambeysa, haddii aad doorato Load To halkii Load , waxaad arki doontaa liiska xulashooyinka kale.

Ikhtiyaarada daaqadan waxaa ka mid ah:

• Shaxda^(Table) : Kani waa goobta ugu habboon ee xogta u soo dejisa xaashi shaqo oo banaan ama jirta
• Warbixinta PivotTable^{(PivotTable Report)} : Keen xogta warbixinta miiska Pivot^{(Pivot Table report)} taasoo kuu ogolaanaysa inaad soo koobto xogta soo socota
• PivotChart : Ku muuji xogta jaantus la soo koobay, sida garaafka bar ama jaantuska pie
• Kaliya Abuur Xiriirinta^{(Only Create Connection)} : Wuxuu abuuraa isku xirka faylka xogta dibadda, kaas oo aad isticmaali karto hadhow si aad u abuurto jadwal ama warbixinno waraaqo shaqo oo badan

Doorashada PivotChart waa mid aad u awood badan. Waxay kuu ogolaanaysaa inaad ka gudubto tillaabooyinka kaydinta xogta miis ka dibna aad doorato goobo si aad u abuurto jaantusyo ama garaafyo. 

Iyada oo qayb ka ah habka soo dejinta xogta, waxaad dooran kartaa beeraha, filtarrada, halyeeyada, iyo xogta dhidibka si aad u abuurto sawiradaas hal tallaabo.

Sida aad arki karto, waxaa jira dabacsanaan badan marka ay timaado gelinta CSV xaashida shaqada ee Excel .

Sida Loo Geliyo Faylka TSV Xaashiyaha^{(A TSV File Into)} Shaqada ee Excel^{(Excel Worksheet)}

Maxaa dhacaya haddii faylkaaga soo galaya uu yahay tab xaddidan halkii uu ka ahaan lahaa mid kala xaddidan? 

Nidaamku inta badan waa la mid sidii qaybta hore, laakiin waxaad u baahan doontaa inaad isticmaasho sanduuqa hoos u dhigista Xaddidaadda^(Delimiter) si aad u doorato Tab .

Sidoo kale, xusuusnow marka aad baadhayso faylka xogta, Excel wuxuu si toos ah u qaadanayaa inaad raadinayso faylka * .csv. Markaa daaqada biraawsarka faylka, xasuuso inaad u beddesho nooca faylka una beddelo All Files (*.*) si aad u aragto faylka nooca * .tsv.

Marka aad dooratid xadeeyaha saxda ah, soo dejinta xogta xaashida shaqada ee Excel , PivotChart , ama Warbixinta Pivot waxay^{(Pivot Report)} u shaqeysaa si isku mid ah.

Sida ay u shaqeyso Xogta Beddelka

Daaqadda Import Data , haddii aad doorato Transform Data halkii aad ka dooran lahayd Load , waxay furi doontaa daaqada Tafatirka Query Query .^{(Power Query Editor)}

Daaqadani waxay ku siinaysaa aragti ku saabsan sida Excel si toos ah ugu beddelo xogta ay soo dejinayso. Tani sidoo kale waa meesha aad ka beddeli karto sida xogtaas loo beddelo inta lagu jiro soo dejinta.

Haddii aad doorato tiir ka mid ah tifaftirahan, waxaad arki doontaa nooca xogta loo maleeyay ee hoos timaada qaybta Beddelka^(Transform) ee ribbon. 

Tusaalaha hoose, waxaad arki kartaa in Excel uu u qaatay inaad rabto inaad xogta ku jirta tiirkaas u beddesho qaab Nambar^(Number) dhan ah.

Waxaad tan ku beddeli kartaa adiga oo dooranaya falaarta hoose ee ku xigta nooca xogta oo aad doorato nooca xogta ee aad doorbidayso.

Waxa kale oo aad dib u habayn kartaa tiirarka tifaftirahan adiga oo dooranaya tiir oo u jiida meesha aad rabto in uu ku galo xaashida shaqada.

Haddii faylkaaga xogta soo galaya aanu lahayn safka hore, waxaad bedeli kartaa Isticmaalka Safka Koowaad sida Madax^{(Use First Row as Headers)} si aad u isticmaasho Madax-yada safka koowaad^{(Use Headers as First Row)} .

Caadi ahaan, waa inaadan waligaa isticmaalin Tafatiraha Query Query^{(Power Query Editor)} , maadaama Excel uu aad ugu fiican yahay falanqaynta faylasha xogta soo galaya. 

Si kastaba ha noqotee, haddii faylalka xogtaas aysan ku habboonayn sida xogta loo qaabeeyey ama aad rabto inaad dib u habayn ku sameyso sida xogtu ugu muuqato xaashidaada shaqo, markaa Tifaftiraha Query Query^{(Power Query Editor)} ayaa kuu oggolaanaya inaad taas sameyso.

Xogtaadu ma ku jirtaa xogta MySQL ? Baro^(Learn) sida loogu xidho Excel iyo MySQL^{(connect Excel to MySQL)} si loo keeno xogtaas. Haddii xogtaadu ay hore ugu jirtay faylal kale oo Excel ah, waxa kale oo jira habab lagu mideeyo xogta faylal badan oo Excel ah^{(merge data in multiple Excel files)} hal fayl.

How To Insert CSV or TSV Into An Excel Worksheet

Τext basеd dаta files are one of the most common methods of storing data in the world today. This is becaυse text files, in general, consume the last space and they’re easiest to storе. Thankfully, it’s νery easy to insert either CSV (comma separated values) or TSV (tab separated values) files intо Microsoft Excel.

If you want to insert CSV or TSV into an Excel worksheet, you only need to know specifically how the data in the file is separated. You don’t necessarily have to know the details about the data, unless you want to reformat those values into strings, numbers, percentages and more.

In this article you’ll learn how to insert either a CSV or TSV file into your Excel worksheet and how to reformat that data in the process of importing, to save yourself some time.

How To Insert A CSV File Into An Excel Worksheet

Before you can insert a CSV file into your Excel worksheet, you need to confirm that the data file is actually comma separated (also known as “comma-delimited”).

Verify It’s A Comma Separated File

To do this, open Window Explorer and navigate to the directory where the file is stored. Select the View menu and make sure the Preview pane is selected.

Then, select the file that you believe contains comma separated data. You should see a comma between each piece of data in the text file.

The example below comes from a government dataset containing 2010 SAT College Board student score results.

As you can see, the first line is the header line. Each field is separated by a comma. Every other line after that is a line of data, with each data point separated by a comma.

This is an example of what a file with comma separated values looks like. Now that you’ve confirmed the formatting of your source data, you’re ready to insert it into your Excel worksheet.

Insert A CSV File Into Your Worksheet

To insert the source CSV data file into your Excel worksheet, open a blank worksheet. 

1. Select Data from the menu
2. Select Get Data from the Get & Transform Data group on the ribbon
3. Select From File
4. Select From Text/CSV

Note: As an alternative, you could also select From Text/CSV directly on the ribbon.

This will open the file browser. Browse to where you’ve stored the CSV file, select it, and select Import.

This will open the data import wizard. Excel analyzes the incoming data and sets all of the dropdown boxes according to the format of the input data file, based on the first 200 rows. 

You can adjust this analysis by changing any of the following settings:

• File Origin: If the file is of another data type like ASCII or UNICODE, you can change that here.
• Delimiter: If semicolons or spaces are used as an alternative delimiter, you can select that here.
• Data Type Detection: You can force Excel to analyze based on the entire dataset rather than just the first 200 rows.

When  you’re ready to import the data, select Load at the bottom of this window. This will bring the entire dataset into your blank Excel worksheet.

Once you have that data in an Excel worksheet, you can then reorganize that data, group rows and columns, or perform Excel functions on the data.

Import CSV File Into Other Excel Elements

A worksheet isn’t all you can import CSV data into. In the last window, if you select Load To instead of Load, you’ll see a list of other options.

Options in this window include:

• Table: This is the default setting that imports the data into a blank or existing worksheet
• PivotTable Report: Bring data into a Pivot Table report that lets you summarize the incoming data set
• PivotChart: Display the data in a summarized chart, like a bar graph or pie chart
• Only Create Connection: Creates a connection to the external data file, which you can use later to create tables or reports on multiple worksheets

The PivotChart option is very powerful. It lets you skip over the steps of storing the data in a table and then selecting fields to create charts or graphs. 

As part of the data import process, you can choose the fields, filters, legends, and axis data to create those graphics in a single step.

As you can see, there’s a lot of flexibility when it comes to inserting CSV into an Excel worksheet.

How To Insert A TSV File Into An Excel Worksheet

What if your incoming file is tab delimited instead of comma delimited? 

The process is mostly the same as in the previous section, but you’ll want to use the Delimiter dropdown box to select Tab.

Also, remember that when you’re browsing for the data file, Excel automatically assumes you’re looking for a *.csv file. So in the file browser window, remember to change the file type to All Files (*.*) to see the *.tsv type file.

Once you’ve selected the correct delimiter, importing data into any Excel worksheet, PivotChart, or Pivot Report works exactly the same way.

How Transform Data Works

In the Import Data window, if you select Transform Data instead of selecting Load, it’ll open the Power Query Editor window.

This window gives you insight into how Excel automatically converts the data that it’s importing. This is also where you can tweak how that data gets converted during the import.

If you select a column in this editor, you’ll see the assumed data type under the Transform section in the ribbon. 

In the example below, you can see that Excel assumed you’d want to convert the data in that column into a Whole Number format.

You can change this by selecting the down arrow next to the data type and selecting the data type you prefer.

You can also reorder columns in this editor by selecting a column and dragging it to the position where you want it to go in your worksheet.

If your incoming data file doesn’t have a header row, you can change Use First Row as Headers to Use Headers as First Row.

Normally, you should never have to use the Power Query Editor, since Excel is pretty good at analyzing incoming data files. 

However, if those data files are inconsistent in how the data is formatted or you want to reorganize how the data appears in your worksheet, then the Power Query Editor lets you do that.

Is your data in a MySQL database? Learn how to connect Excel to MySQL to bring in that data. If your data is already in another Excel file, there are also ways to merge data in multiple Excel files into one file.

Related posts

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Safka Kooxda iyo Tiirarka ku jira xaashida shaqada ee Excel

• Geli xaashida shaqada ee Excel Word Doc

• Sida Dhakhso Loo Geliyo Safaf Badan gudaha Excel

• Xarunta Xogta Xaashida Shaqada ee Excel si loo daabaco

• Sida Loo Raadiyo Excel

• Sida Loo Wadaago Faylka Excel Si Fudud Layskula Shaqeeyo

• Sida loo isticmaalo Flash Fill ee Excel

• Sida loo raad raaco Ku-tiirsanaanta gudaha Excel

• Sida loo Hagaajiyo #N/A Khaladaadka gudaha Foomamka Excel sida VLOOKUP

• Google Sheets vs Microsoft Excel - Waa maxay faraqa u dhexeeya?

• Sida loo sameeyo shaxda Pie ee Excel

• Sida Loogu Mideeyo Xogta Faylal Badan oo Excel ah

• Sida looga saaro Gridlines gudaha Excel

• U isticmaal Magacyada Range Dynamic ee Excel si aad u Dejiso Dabcida

• Sida loo tirtiro khadadka banaan ee Excel

• 5 siyaabood oo loogu badalo qoraalka nambarada gudaha Excel

• 2 siyaabood oo loo Isticmaalo Shaqada Transpose ee Excel

• Sida loo isticmaalo Excel's What-If Analysis

• Hal-Column iyo Xog-urur badan oo Aasaaska ah oo ku kala soocida xaashida faafinta ee Excel