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)".`