INTRODUCTION:
CSV files or Comma Separated Value files is a variety of spreadsheet. Its extension is .csv. It contains data files in plain text format. CSV files can be used with spreadsheet programs like Microsoft Excel or Google spreadsheets.
PROBLEM WITH LARGE FILES:
Spreadsheet programs like Microsoft Excel are used to arrange data in tabular format. Excel the maximum rows number that Excel can offer is 1,048,576. Files having more than 1,048,576 rows is considered to be large files. While opening these files system shows a message telling that “File not loaded completely”. If you click “ok” and move to the last row you will find the spreadsheet is full till the last row.
SOLUTION WITH IMPORT THE CSV:
The big files of Excel datasheet cannot be opened in the standard way although with CSV files you may do it. This follows the way to import the CSV file. Keeping connection with the original CSV file load all data into the Data Model. Then millions of rows will get loaded. The following are the few steps for the process of loading big spreadsheet files.
Step1- First navigate your data with the following options –
Data>>Get & transform Data>>From File>>From Text/CSV
It will then import the CSV.
Step2- Then a file preview window will open. There will be a little triangle beside the “Load” button. Click that triangle.
Step3- Then select “Load to” and check the options “Only create connection” and “Add this data to the Data model” and click “Ok”.
Step4- Then it will load data. You will get file name along with the number of rows on the “Queries & Connections” window. Open Power Query Editor by double-clicking on the window.
Step5- Now you will find newly added rows. To replace any cell value just right click on the particular cell and choose “Replace Values” option.
CONCLUSION:
This is the simple process to open big files of Excel datasheet. Just a few steps and your file is loaded completely.
