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

LVL 1
VHAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please try updated code -

Use SQUARE Brackets.

/*****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(5)
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] 
		   GO

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

END

Open in new window


Sample trial code -

CREATE TABLE BulkInsert ( a INT, b INT )

 BULK 
		   INSERT BulkInsert 
		   FROM [C:\Pawan\access.2017.11.16.txt] WITH 
		   ( 
		   FIELDTERMINATOR = ',', 
		   ROWTERMINATOR = '\n')

Open in new window



OUTPUT MESSAGE

/*------------------------

 BULK 
		   INSERT BulkInsert 
		   FROM [C:\Pawan\access.2017.11.16.txt] WITH 
		   ( 
		   FIELDTERMINATOR = ',', 
		   ROWTERMINATOR = '\n')
------------------------*/

(2 row(s) affected)

Open in new window


OUTPUT data from table

/*------------------------
SELECT * FROM BulkInsert
------------------------*/
a           b
----------- -----------
1           2
3           5

(2 row(s) affected)

Open in new window

0
 
VHAuthor Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
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

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
VHAuthor Commented:
I was hoping not to had to define the Log Name as i have over a 100 logs i need to import
0
 
VHAuthor Commented:
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).
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
can u attach your text file and the data types you have used in you table? Error is data type conversion ...

Meaning the data you are fetching from the CSV and putting in the datatype are not matching.

You can define a Temporary table with NVARCHAR(MAX) as datatype . Now that will consume any data in the world and from there we can figure out which of  your data is bad and then we can take the call like how to remove it or how to clean it based on our requirement.
0
 
Pawan KumarDatabase ExpertCommented:
Question abandoned.Provided tested solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.