Import from Excel - Named Sheet

This article is to let you know how to import Excel data into SQL Server.

First of all download a set of components i.e OLE DB support that can be used to facilitate the transfer of data between Microsoft Office System files and non-Microsoft Office applications.

Please click here for the Microsoft download link.

The excel file has only one sheet and it is named as Sheet1 by default. Use the below script to import this data into SQL Server.

USE TestDB1

GO

CREATE TABLE SampleTable(IDCol INT, Col2 VARCHAR(10))

INSERT INTO SampleTable
SELECT * FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0; Database=D:\TestDataFolder\sample.xlsx',
                 'SELECT * FROM [Sheet1$]')
GO

SELECT * FROM SampleTable

If the excel file's tab has a specific name, i.e. named sheet then -

USE TestDB1
GO

CREATE TABLE SampleTable(IDCol INT, Col2 VARCHAR(10))

INSERT INTO SampleTable
SELECT * FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                 'Excel 12.0; Database=D:\TestDataFolder\sample.xlsx',
                 'SELECT * FROM [Sample Data$]')
Go

SELECT * FROM SampleTable

Hope you find this article helpful.

Generate multiple CSV data files from SQL

Assume that there are thousands of rows in a table and the rows need to be split and saved in different CSV files based on date or category or status. Have you ever come across such a requirement?

If so, here is the code to accomplish it.

USE TestDB1
GO

DECLARE @TestData20 TABLE(IntValCol INT, DateCol DATETIME)

INSERT INTO @TestData20 (IntValCol, DateCol) VALUES
(1,'09/05/2020'), (2,'09/05/2020'), (3,'09/06/2020'), (4,'09/06/2020'),
(5,'09/07/2020'), (6,'09/07/2020'), (7,'09/08/2020'), (8,'09/08/2020'),
(9,'09/09/2020'), (10,'09/09/2020'),(11,'09/10/2020'), (12,'09/10/2020') 

-- Declaring Variables
DECLARE @MinDate DATETIME,
              @MaxDate DATETIME,
              @FileName VARCHAR(30),
              @FilePath VARCHAR(100),
              @BCPCommand VARCHAR(4000) 

-- Assigning Values To Variables
SELECT @MinDate = MIN(DateCol) FROM @TestData20
SELECT @MaxDate = MAX(DateCol) FROM @TestData20

--Creating the loop
WHILE @MinDate <= @MaxDate
BEGIN
       --Setting up the filename and filepath
       SET @FileName = 'TempData'+'_'+CONVERT(VARCHAR(8),@MinDate,112)+'.csv'
       SET @FilePath = 'D:\TestDataFolder\'+@FileName

       --Fetching the data into a table
       SELECT * INTO Temp FROM @TestData20 WHERE DateCol = @MinDate 

       --Assigning the BCP statement to the variable
       SET @BCPCommand = 'BCP  TestDB1.dbo.Temp out '+@FilePath+' -T -w -t, '

       --Executing the BCP command through command-shell
       EXEC master..xp_cmdshell @BCPCommand

       --Dropping the table, to utilize it again
       DROP TABLE Tempf

       SET @MinDate = DATEADD(D,1,@MinDate)

END

Hope you find this article helpful.

SQL Server - Parsing, Casting and Conversion

Sometimes the data stores in the table in a different format in comparison to the real data type it belongs to. The date might be stored as an integer value or a string value, numerical values might be stored as a character string, the currency might be stored in number format. The reason or explanation could be whatever requested by the design or business. One cannot expect the same as an output, there we need casting, parsing, and conversion methods.

This article focuses on the following functions -

1) CAST

2) CONVERT

3) PARSE

4) TRY_PARSE

5) TRY_CAST

6) TRY_CONVERT

CAST:

Cast function converts an expression from one data type to another. This is ANSI-SQL specification.

Examples:

SELECT CAST(25.65 AS INT);
The query returns the value of 25.65 as 25 by converting the 'decimal' into an integer. 

SELECT CAST(25.65 AS VARCHAR);
The query returns the value of 25.65 as 25.65 but the later one is a character string. 

SELECT CAST('2017-08-25' AS DATETIME);
The query returns the value of "2017-08-25" as "2017-08-25 00:00:00.000" by converting the 'decimal' values into an integer.

Let's see one more example. 

USE AdventureWorks2017;
GO

SELECT Name,
             sellstartdate,
             CAST(sellstartdate AS VARCHAR) converteddate,
             ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

CONVERT:

Convert is SQL implementation-specific function. Though it does the same the Cast function does and there isn't any difference in performance however Convert function allows a more noteworthy broadness of flexibility when converting among date and time values, fractional numbers, and monetary signifiers.

Examples:

SELECT CONVERT (INT, 25.65)
The query returns "25" by converting the 'decimal' into an integer. 

SELECT CONVERT(DATETIME, '2017-08-25')
The query returns "2017-08-25 00:00:00.000" a proper DateTime format. 

SELECT CONVERT(VARCHAR(10), GETDATE(), 101)

The query returns "09/02/2020" by converting the DateTime value into a character string.

Let's see one more example. 

USE AdventureWorks2017;
GO

SELECT Name,
             sellstartdate,
             CONVERT(VARCHAR(10),sellstartdate,101) converteddate,
             ListPrice
FROM Production.Product
WHERE CONVERT(INT, ListPrice) LIKE '3%';
GO

Please click here for more information about the "CONVERT" function.

PARSE:
Returns the result of an expression translated to the requested data type in SQL Server. This function is not an alternative to CAST or CONVERT function though it converts the data type one to another.

Let's see this date - "Wednesday, 2 September 2020". If you see there isn't any issue with this date or its format as it's human-readable and we know that in some places people use such date format. However, built-in functions like 'CAST' or 'CONVERT' cannot convert such data that people of different timezones or different geo-locations used.

Let us test.

SELECT CAST('Wednesday, 2 September 2020' AS datetime)

SELECT CAST('Wednesday, 2 September 2020' AS datetime2)

SELECT CONVERT(DATETIME,'Wednesday, 2 September 2020')

SELECT CONVERT(DATETIME,'Wednesday, 2 September 2020')

All the above statements return 'conversion failed' error since CAST or CONVERT cannot convert such data into other formats.

SELECT PARSE('Wednesday, 2 September 2020' AS datetime)

SELECT PARSE('Wednesday, 2 September 2020' AS datetime2)

SELECT PARSE('Wednesday, 2 September 2020' AS datetime2 USING 'en-US');

The above three statements return the converted data as shown below.

Let's see one more example.

Above is a test data. EmployeeID usually is an integer value however intensionally it was assigned as VARCHAR. The same with Salary, "joindate" and "updatedate".

In the SELECT statement, +1 (plus one) at the end of the first parse statement is to check if it is convertible to a numeric value. If the outcome is not a number then addition will return an error. 

PARSE will not proceed further and throws an error if there is any null value. 

If the PARSE is used in a transaction and if there is any null, the whole transaction will be rolled back. This led to the introduction of TRY_PARSE. 

TRY_PARSE:

TRY_PARSE function is as same as PARSE; the only difference is it will return a null value when the conversion fails instead of canceling the whole conversion process. 

USE dbTEST
GO

CREATE TABLE EmpTest(
                           EmpID VARCHAR(4),
                           Ename VARCHAR(50),
                           Salary varchar(5),
                           joindate VARCHAR(40),
                           updateddate varchar(20))

INSERT INTO EmpTest VALUES
('A123','Kate','21000','Tuesday, 1 September 2020','09/01/2020'),
('B199','Jamon','23000','Wednesday, 2 September 2020','09/02/2020'),
('','Sandra',NULL,'Tuesday, 1 September 2020','09/01/2020') 

SELECT TRY_PARSE(RIGHT(EmpID,3) AS INT)+1,
             TRY_PARSE(Salary AS DECIMAL(15,2)),
             TRY_PARSE(joindate AS date),
             TRY_PARSE(updateddate AS DATETIME)
FROM EmpTest

Similarly, there are two more functions i.e. TRY_CONVERT and TRY_CAST. These two will also return a null value when the conversion fails, instead of cancelling the transaction and returning an error.

TRY_CONVERT:

SELECT TRY_CONVERT(INT, RIGHT(EmpID,3)),
             TRY_CONVERT(DECIMAL(15,2), Salary),
             TRY_CONVERT(DATETIME, updateddate)
FROM EmpTest

TRY_CAST:

SELECT TRY_CAST(RIGHT(EmpID,3) AS INT),
             TRY_CAST(Salary AS DECIMAL(15,2)),
             TRY_CAST(updateddate AS DATETIME)
FROM EmpTest

Clearing Screen - HDFS Terminal, Hive, Impala

Sometimes we may need to clear the screen or console window of commands or any output generated from the previous task. 

The following commands are used to clear the contents of the screen from Hive, Impala, and HDFS terminal windows. If you have already worked in Ms-DOS, you would definitely search for such command in other terminals. 

By the way, CLS is the command in DOS.

HDFS:

clear; is the command to clean-up the contents of the screen in Impala.

Impala:

!clear; is the command to clean-up the contents of the screen in Impala.

Hive:

!clear; is the command to clean-up the contents of the screen in Hive.

Sqoop Complete Tutorial Part-5

This is the continuation part of "Sqoop Complete Tutorial". If you want to read -

Part-1, please click here. 

Part-2, please click here. 

Part-3, please click here. 

Part-4, please click here. 

14) Read the contents of the database without importing it. 

sqoop list-tables
--connect jdbc:mysql://localhost/empdept
--username root
--password cloudera

This above command will list out the tables from the given database.

15) Get the list of of the databases using Sqoop

sqoop list-databases
--connect jdbc:mysql://localhost/
--username root
--password cloudera

With the above command, we completed learning how to import data from MySQL to HDFS using Sqoop. The upcoming articles are on how to import data from MySQL to Hive.

Please click here for the next part

SQL Server Error Log - Search

The SQL Server error log contains user-defined events and certain system events you can use for troubleshooting. The errors and the informational data will be stored in the error logs. The data will be huge and sometimes it will be difficult to find the information or error from it. 

Below is the query to retrieve the complete log from SSMS.  

EXEC xp_ReadErrorLog

GO

If you want to search through this log, you can use the below syntaxes.

To know the SQL Server version, edition, and other information -

EXEC xp_ReadErrorLog 0, 1, N'Build', N'Edition', NULL, NULL, 'DESC'

GO

To know on which port SQL Server is listening on -

EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'

GO

To know the CLR version loaded in the SQL Server -

EXEC xp_ReadErrorLog 0, 1, N'CLR version', N'loaded', NULL, NULL, 'DESC'

GO

To know the authentication mode -

EXEC xp_ReadErrorLog 0, 1, N'Authentication Mode', NULL, NULL, NULL, 'DESC'

GO

To know the number of sockets and processors -

EXEC xp_ReadErrorLog 0, 1, N'sockets', N'processors', NULL, NULL, 'DESC'

GO

If you observe the syntax, I've provided two key-words to filter and fetch the required information. The count can be increased.

The same can be applied to find errors.

Find a specific column from the database objects

There are many ways to find out a specific column from the database objects like Tables and stored procedures.

1) Red Gate's SQL Search:

This tool will be integrated into SQL Server Management Studio once installed. This will help in searching across multiple object types and multiple databases.

2) Using a query:
The following query will find the tables and stored procedures that contain a certain field.  

SELECT sysobjects.name FROM syscolumns
LEFT JOIN sysobjects ON sysobjects.id = syscolumns.id
WHERE syscolumns.name like '%EmployeeID%'
ORDER BY 1

3) Using a query:
The following query will find the tables (only) that contain a certain field

SELECT * FROM information_schema.columns
WHERE
column_name LIKE '%EmployeeID%'