LOADING/DRAG DATA FROM CSV TO EXCEL

Loading/drag data from CSV to Excel file is the same as TXT format wizard when we use delimiter to separate table columns. We can do this kind of work in two ways, first, when we can use Open (choose the CSV file type), which has existed at least since version 2003, and second, when we use PowerQuery , which has existed since version 2010 as an additional, and since 2016 as a default functionality.

In  versions : 2003, 2007, 2010, 2013, 2016, 2019, 2021, 365, for  Mac  and for  the web

Loading a CSV file with a simple OPEN function

CSV (Comma-Separated Values) is basically a TXT format in which column values ​​are separated by commas. This is true if we have the regional setting in Windows set to English spelling.

When exporting data, these characters will default to the current setting. If by any chance we need a temporary change of those characters, it is necessary to select Options in the File tab , and in the Advanced tab, uncheck the Use system separators option , when cells for user settings become available.

The size of the CSV file is determined by the operating system and can be 2GB for FAT16B, 4GB for FAT32, 2GB for HFS, etc. Which allows you to record a table of 10 columns (each column of 10 bytes) and almost 20 million rows, of course if you have a program that can generate or Notepad that can print such a file.

We certainly didn’t forget the delimiter, which can be: Tab (TAB character – ASCII character code 009), semicolon, comma, space, or user-defined character. Also, let’s not forget that the maximum allowed table in Excel is 1,048,576 rows and 16,384 columns.

This is also a limitation for this type of data upload for further processing.

It means that we do all the processing of more than a million rows using PowerQuery and PowerPivot functionalities.

Drag data wizard

You can open a text file that you created in another program as an Excel workbook using the Open command. Opening a text file in Excel does not change the file format — you can see this in the Excel title bar, where the file name retains the text file extension.

• Go to  File >  Open  and browse to the location that contains the text file.
  Select  a CSV file from the File type drop-down list in the Open dialog  .

• The dragged data will be as in the previous picture.
• After clicking the Open button, a three-step help wizard will be launched.

The first step

• You are expected to determine whether you will use a special character to separate the columns (delimiter is a synonym for separator) or whether you will determine the width for all rows by the number of characters.
• The wizard recommends the code layout of the text (UTF-8) as well as the option to check if the table header is in the first row (My data has headers.)

• When you have finished selecting options, click Next> .

Second step

• It asks you to specify a separator character such as Tab ( Tab ), Semicolon ( Semicolon ), Comma ( Comma ), Space ( Space ) or some other character you specify ( Other ).
• In case you have consecutive delimiters, for example, duplicated, tripled, check the independent box to treat consecutive delimiters as one ( Treat consecutive delimiters as one ).
• And finally set the quotation marks for texts ( Text qualifier ).

• When you have finished selecting options, click Next> .

The third step

• You are now expected to specify a data type for each column from the CSV file.
• The default value is General format ( General ), it can also be text , date ( Date ) in a certain orthographic form ( DMY , day, month and year), or simply do not drag and import a column ( Do not import column (skip) ).
• Here you can simply leave everything in the existing general format.

• When you have finished selecting the options, click Finish .

• You can format the resulting content as in the previous image by selecting all the columns and by moving the mouse to the line between the columns (red arrow), you can double-click to format the width of the columns as in the following image.

• Now simply select any cell and use the shortcut Ctrl+T to create a table.

• Without touching any option by selecting OK, you get a table object, which you can read more about in the text How to create Excel tables .

Drag using PowerQuery

In case we work with more than a million rows in the table (recorded in CSV format), the only way to do it in Excel is to use the PowerQuery functionality. PowerQuery is an add-on in versions 2010 to 2016 and must be installed afterwards, and in versions 2019, 2021 and 365 it is an indispensable function on the Data tab.

When you open the Data tab , the first tool on the ribbon is Get Data . In the first From File option , select From Text/CSV , as in the following image.

In the file selection dialog (Files), select the folder and the CSV format file you want to upload. After the information window for connecting to the file, the content opens as in the following image:

When we load data from CSV format and we do not know which character separates the data by column, the separator will be recognized in the Delimiter column as in the following image.

Now it is enough to click on Load to load the data.

If someone still wants to transform the data before loading, they should call the PowerQuery Editor, the use of which we explained in more detail in the attachment Dragging-loading-importing from TXT format .