Microsoft has added a new wizard to SSMS for importing flat files (.csv, .txt) into SQL Server tables. This new addition is available in the version 17.3 and later versions. SSMS makes importing flat files to SQL Server very easy with this additions. All you need to do is to provide the file and table name and SSMS will take care of the rest.
I am going to import a CSV file in the following example by using this new feature. To start, you need to right click on a database you want to use, click on the Tasks option and you should see the Import Flat File option.
If you don't see the Import Flat Files option, check the version of SSMS, as I mentioned earlier you need the version 17.3 or later versions to use this function.
I have the following CSV file to import.
I am going to pick this file location and give a name to the new table to continue.
After clicking on the Next button, you will see a preview view of the data in table format.
You can modify the new table's data type and keys in the following window.
That's all, next two windows are going to summarize the import and import the flat file to the table.
Here is the data from the table.
There are many ways but the quick and dirty way to do it is as follows from your local DB instance:
ReplyDeleteselect top 1000 *
from opendatasource('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\;Extended Properties="Text;HDR=YES;FMT=Delimited()" ')...Sample#txt
And just insert the result wherever you require.
This even works for XLSX files with a slightly different pattern