Scenario:
You are working as SQL Server developer, you get text files such as comma separate values, Tab delimited or pipe delimited files. It is one time task and you need to generate insert statements from these files so you can load the data into dbo.Customer table in DEV,QA,UAT and Production environments by using those scripts.Solution:
I wrote a post , how to generate Insert statements from Excel file. Click here. It means if we can open the text files with excel then we can use excel formulas to build insert statements.If we have comma separate values file, you can directly open with excel and then use the steps shown in this post.
If you have tab delimited or pipe delimited file, first of all we need to open with excel and then use the formulas.
Step 1:
Let's say that I have Tab delimited Customer.txt file as shown below and want to open in excel.
 |
| How to generate Insert Statements from Tab delimited file for SQL Server Table |
Step 2:
Open Excel and then go to File and hit Open, Browse to customer.txt file
 |
| Generate Insert Statements from Text files for SQL Server Table |
Test Import Wizard will open, Choose Delimited and Click My Data has Headers if it does and then Click Next
 |
| How to open Tab Delimited File in Excel to generate Insert statements for SQL Server Table |
Choose the Delimiters, in our case it is Tab and hit Next.
 |
| How to Convert Tab Delimited File to Excel file and Generate Insert Statements for SQL Server Table |
 |
| How to load Tab delimited file into Excel and Create Insert Statements for SQL Server Table |
Once you will hit Finish, below excel sheet will be populated with flat file data.
 |
| How to generate Insert Statements from Excel File for SQL Server Table |
Now the data is in Excel file. we can use the excel formulas to generate insert statements as shown in this post.
Video Demo : How to generate Insert Statements from Flat File data for SQL Server Table
No comments:
Post a Comment