CSV file Saving Leading zero

Hello Player521345199.

Thanks for coming to the Microsoft Community

If you format the cells in Excel with the leading zero, and save as .csv, the leading zeros will be saved to the .csv file.
You can verify this by opening the .csv file in Notepad or another text editor, but the moment you open the .csv file in Excel, the format will be lost.

So: if you want to keep the formatting, don't open the .csv file in Excel.
That way when you open in notepad, you can see that the leading zeros are there and use the file as you need.

Best Regards
Fuad.

Excel does not "do" leading zeros in numbers.

When you are opening the CSV file in Excel you have to define that column as text

.

"Account numbers" are not used in arithmetical calculations they can be treated as text!

.

If possible one way to force keeping leading zeros is when the CSV is being created, save the "Account number" with a leading apostrophe. That forces the data to be treated as text every time it is loaded into

.

Use PowerQuery to open the CSV!

PowerQuery give you more control over the incoming data than the old import "Wizard"

Once you define the query it can be "reused" on new files, ie PowerQuery can be defined to import all files (ie CSV) in a specific folder, new files will be automagically picked up.

.

Remove the automatically generated change type step

Make sure the account number column is defined as Text

do any other data cleanup you need

Close and load

.

PowerQuery is fantastic once you get past the fear of trying it. This simple import is a good excuse to start learning how to use it.

.

What is Power Query

The question is “What s PowerQuery”

The shortest answer is “Amazingly powerful!”

A BRIEF description of what you can do in Power Query:

1.   Connect    Make (one or more) connections to data in the cloud, on a service, or locally

2.   Transform    Shape data to meet your needs, while the original source remains unchanged

3.   Combine    Integrate data from multiple sources to get a unique view into the data (perform SQL like “Joins”)

4.   Load   Complete your query and load it into a worksheet or Data Model and periodically refresh it
.

After the data is loaded to Excel, in form of Excel Tables on a worksheet, the data can be manipulated in the table, or fed into a PivoTable and then to a PivotChart.

PowerQuery allows you to use a “Click and Drag” user interface from the Ribbon and right click context menus to generate the required code in the background.
.

PowerQuery automation is a “reducing agent”. It slims down a LOT of things in Excel:

.  *  Reduces time by:

.      *  Click and Drag command/functions to automatically generate code in the background  (no hand coding, for simple code)

.      *  Some functions / changes are generated “by example”, like flash fill

.      *  The effects of changes are immediately visible

.      *  Each change is a separate “step” in a query. Don’t like the effect of a change, delete the step. Move steps to other places in query

.      *  Creating a “Query” is generally much faster than hand coding equivalent formulas

.      *  importing new data is a simple matter of using the right click “refresh” function

.      *  Complexity of connecting to outside data sources is hidden

.      *  PowerQuery in Excel is same as PQ in PowerBI. Learn it in one place to use it in both

.     *  the output of any query or step within a query can be used as a “variable” to pass that output to another step or query.

.  *  Reduce size:

.      *  tests have shown that using PQ the resulting file size is much smaller than the equivalent formula version

.      *  combine PQ with the DataModel feature and it will apply data compression to imported data. Examples have been documented with 100’s of MILLIONs rows data, much larger than number of rows Excel can handle natively.

Power Query exists in many Microsoft tools and services such as Power BI, Excel, Dataflows, Power Automate, Azure Data Factory, etc. Through the years, this engine became more powerful. Learning and mastering PQ is essential learning for anyone who wants to do data analysis with Microsoft technology.
.

.
Here are several articles and youtubes that expand on the description of what Power Query is and what it can do:
. Intro Webinars about "Automation" built in to Excel Since “Excel Tables” automation was added in Excel 2007 MS has been working on ‘programmerless programming”. Or “Advanced automation”. Since then MS has added other feature such as:

.  *  Excel Tables

.  *  PowerQuery

.  *  Data Model / PowerPivot

.  *  PivotTables

.  *  PivotCharts

These features all incorporate LOTS of “automation” that reduces the need for users to write “code” by hand. 
.

In the following link, session #2 is a good overview example of how these features can all be used together to create “Dashboards”. Dashboards are impressive “summary” output that would be effectively impossible for the “average” user to create by writing formulas “by hand”.
.

3 free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI
https://www.myonlinetraininghub.com/excel-webinars
.
#2 Dashboards Using Power Query & Power Pivot   Excel 2010  - 2019 and Office 365In this webinar I'm going to show you how to use Power Query to get data from multiple sources, filter and clean it, send it to Power Pivot and create relationships between data tables. Then I'll mash it up in PivotTables to create this interactive Excel Dashboard.
.

Here are some introductory articles about PQ.

Change Your Life withExcel PQ
https://www.xelplus.com/excel-power-query-course-preview/
https://www.youtube.com/watch?v=6lBqYInBldk&list=PLmHVyfmcRKyyKV86N7i0q9TfYNN8bBjX-  9min
.
What if someone told you that there is a tool out there that can work magic on your data?
What if they said it can perform some of the most amazing data transformations you’ve ever seen?
What if they said you could reduce hours, if not days’ worth of work to mere minutes or seconds?
What if they said you can do this without writing a single formula or a single line of VBA code?
Interested?
Now, what if they told you that you already own this tool?
Let’s talk about Power Query.
.

What is Power Query?
https://powerquery.microsoft.com/en-us/
Microsoft’s Data Connectivity and Data Preparation technology that lets you seamlessly access data stored in hundreds of sources and reshape it to fit your needs—all with an easy to use, engaging, no-code experience.
.

What is PowerQuery-
https://docs.microsoft.com/en-us/power-query/power-query-what-is-power-query
Power Query is a data transformation and data preparation engine. Power Query comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations. Because the engine is available in many products and services, the destination where the data will be stored depends on where Power Query was used. Using Power Query, you can perform the extract, transform, and load (ETL) processing of data.
.

About Power Query in Excel-
https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a
With Power Query (known as Get & Transform in Excel), you can
.  *  Connect     Make connections to data in the cloud, on a service, or locally
.  *  Transform    Shape data to meet your needs, while the original source remains unchanged. for example remove a column, change a data type, or merge tables, in ways that meet your needs.
.  *  Combine    Integrate data from multiple sources to get a unique view into the data
.  *  Load   Complete your query and load it into a worksheet or Data Model and periodically refresh it.
Power Query is available in Excel for Windows, Excel for Mac and Excel for the Web. 
.

What is PowerQuery- https://corporatefinanceinstitute.com/resources/excel/study/power-query/
Power Query is a tool in Microsoft Excel that simplifies the process of importing data from different source files and sorting them into an Excel sheet in the most convenient and usable format.
It also eliminates the need to execute the same filtering techniques to transform the same data set at different points in time; the user only needs to set up a query, that is, the rules for sorting, once, and refresh the query every time the action is to be repeated.
.

How Power Query Will Change the Way You Use Excel- https://www.xelplus.com/excel-power-query-course-preview/
https://www.youtube.com/watch?v=6lBqYInBldk 9minIt can be argued that Power Query (also known as “Get & Transform”) is the single greatest feature in Excel.  Power Query can be used to help solve almost every Excel problem you encounter.
Can you think of any other feature in Excel that can make that claim?  Probably not.
One of the greatest selling points of Power Query is that the same problem can be solved with many different approaches.  It all comes down to your experience and creativity.
The more experience you gain, the more efficient your strategies become.  The more creatively you think, the greater the problems you can solve with ease.
.  *  Importing Data from Different Sources
.  *  Transforming and Cleaning Data
.  *  Load Your Data, create relationships
.  *  Power Query’s Best Kept Secret: With just a small investment in time, you can begin producing substantial results that will make any casual observer think you worked weeks to produce.
.