You can open the file in Excel, or you can import the data using an Excel wizard. The two most common text file formats you’ll work with are .txt and .csv, where .csv stands for comma separated ...

You can keep the leading zeros in CSV files by using Excel's Text Import Wizard. Launch Excel Spreadsheet. Click the "Data" tab. Click the "From Text" option icon.

Now that you’ve automated the fetching of your flat file via FTP, the next step is to automatically import that file into SQL Server. To do this, we’ll use an SQL Server Agent Job.