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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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 KumarDatabase 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.