How to Bulk Insert from XLSX file extension in SQL Server 2012?

858 Views Asked by At

Can anyone advise how to bulk insert from .xlsx file?

I can't manipulate the excel file, so I can't change it to .txt or .csv I have to execute the xlsx file as it is, the last query was:

 CREATE TABLE Table_Name(
    [ID_EQUIPMENT] [INT] NULL,
    [EQUIPMENTNAME] [NVARCHAR](50) NULL,
    [NAME] [NVARCHAR](100) NULL,
    [NAME_DESC] [NVARCHAR](100) NULL,
    [STARTDATE] [DATETIME] NULL DEFAULT (GETDATE()),
    [ENDDATE] [DATETIME] NULL DEFAULT (GETDATE()),
    [VOLUME] [INT] NULL
) ON [PRIMARY]
GO

SET @PATH = 'C:\testes\file.xlsx'

DECLARE @FILE_RESULTS TABLE
        (FILE_EXISTS INT,
     FILE_IS_A_DIRECTORY INT,
         PARENT_DIRECTORY_EXISTS INT
        )

INSERT INTO @FILE_RESULTS
(FILE_EXISTS, FILE_IS_A_DIRECTORY, PARENT_DIRECTORY_EXISTS)
EXEC MASTER.DBO.XP_FILEEXIST @PATH
SELECT @VALIDATION = FILE_EXISTS FROM @FILE_RESULTS

IF @VALIDATION = 1
    BEGIN
        PRINT 'FILE EXISTS'
        SET @SQL = "BULK 
                    INSERT Table_Name
                    FROM '"+@PATH+"' 
                    WITH (ROWTERMINATOR = '0x0a', FIRSTROW = 2); "
        SELECT @SQL
        EXEC (@SQL)
    END

after the execution I receive thee following errors:

(1 row(s) affected)
FILE EXISTS

(1 row(s) affected)
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB `provider >"BULK" for linked server "(null)".`
0

There are 0 best solutions below