asked on

Apache Logs to SQL Server

Hi,

I am trying to import these Apache logs into an SQL Database but am unable to do so.

When I run the code below I get the following Error:

Msg 4860, Level 16, State 1, Line 4
Cannot bulk load. The file "C:\LOGS\access.2017.11.16.log" does not exist or you don't have file access rights.

Can I I Import all Files without checking for a date? 


(1 row affected)

Open in new window

/*****This is the share location of the log file in a variable*****/
DECLARE @FileLocation varchar(max)
SET @FileLocation = 'C:\LOGS\'

/*****This is the name of the start the file name in a variable********/
DECLARE @FileName varchar(max)
SET @FileName = 'access.'

/********This is the extention of the log file in a variable*******/
DECLARE @FileEXT varchar(4)
SET @FileEXT = '.log'

/*************This gets todays date in a variable*************/
Declare @date varchar(max)
set @date = (SELECT convert(varchar, getdate()-4, 102))

/********This puts all of the file share and info in one variable name************/
DECLARE @FilePath varchar(max)
Set @FilePath = @FileLocation + @FileName + @Date + @FileEXT

/*********** This is the SQL to insert and its written into a variable and executed************/
DECLARE @SQL nvarchar(max)

SET @SQL = N' use Downloads 
		   BULK 
		   INSERT BulkInsert 
		   FROM "' + @FilePath + 
		   '" WITH 
		   ( 
		   FIELDTERMINATOR = '','', 
		   ROWTERMINATOR = ''\n'')'
EXEC  sp_executesql @SQL


END

Open in new window

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi Pawan,

Thanks for the Reply.

my code is the following

USE [Downloads]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[ImportCSV_Cork]

SELECT	'Return Value' = @return_value

GO

Open in new window

tablerow.PNG
Apachelogs.PNG

Pawan Kumar

Are you getting any error. I am able to insert the data smoothly. You can check below -

BULK INSERT BulkInsert 
FROM [C:\LOGS\access.2017.07.06.log] WITH 
( 
	FIELDTERMINATOR = ',', 
	ROWTERMINATOR = '\n'
)

Open in new window

ASKER

I was hoping not to had to define the Log Name as i have over a 100 logs i need to import

ASKER

I am getting the following error in some log import

Msg 4864, Level 16, State 1, Line 4
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 17409, column 4 (Code).

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Question abandoned.Provided tested solution.