Loading the flat file data to SQL Server temporarily for reading and analyzing the data

Many situations we would like to read the flat file or simple text file but not so easy to analyze the data if we open the file in notepad or any other text editor.
As we know if data is in SQL server it is very easy to query and analyse the data.
Using temp table and Bulk insert functionality we can analyze the file data in SQL environment.

To use this we have to know following information about the flat file.

1.      Number of columns

2.      Column separator

And following access is required.

1.      Access to Flat file location

2.      Database access to run BULK INSERT command

Now, we will see sample file load. We will take one customer detail data file having following columns of data.

CustomerKey
FirstName
LastName
EmailAddress

First we will create a temporary table in the SQL Server

    CREATE TABLE #TEMPCUSTOMER

  (

       CUSTOMERKEY INT,

       FIRSTNAME VARCHAR(40),

       LASTNAME VARCHAR(40),

       EMAILADDRESS VARCHAR(250)

  )

Run above statement to create the temp table.

Now we will create BULK INSERT statement to load the flat file data into temp table.

Mention the full path of the Customer file.

Column separator Character value should be passed to FIELDTERMINATOR parameter

And

New Line character value to  ROWTERMINATOR parameter.

Starting row number should be passed to FIRSTROW parameter.

BULK INSERT #tempCustomer FROM 'C:\Customer.csv' WITH (

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n', FIRSTROW = 2 )

On Execution of above statement, file data will be loaded into Temp table.

Now it is easy to query the data and understand it better.

Happy coding.