?
Solved

Insert CSV to SQL using logparser - MUCHO Points ;)

Posted on 2009-12-21
4
Medium Priority
?
1,247 Views
Last Modified: 2012-05-08
Hi!
im trying to use logparser in order to insert a csv file to sql
the csv fields are

TimeStamp      Aggregated Event Count      Name      File Name      File Path      Destination Address      Destination Asset Resource      Destination Host Name      Destination Zone Resource      Device Host Name      Device Zone Resource      Device Custom String5      Device Custom String6

the SQL Fields are

USE [Metrics]
GO
/****** Object:  Table [dbo].[Arc_AV]    Script Date: 12/21/2009 18:28:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Arc_AV](
      [EventID] [int] IDENTITY(1,1) NOT NULL,
      [TimeStamp] [varchar](50) NULL,
      [CountArc] [smallint] NULL,
      [Name] [varchar](50) NULL,
      [FileName] [varchar](300) NULL,
      [FilePath] [varchar](300) NULL,
      [DestIP] [varchar](50) NULL,
      [DestResource] [varchar](50) NULL,
      [DestHost] [varchar](50) NULL,
      [DestZone] [varchar](50) NULL,
      [DeviceHostName] [varchar](50) NULL,
      [DeviceZone] [varchar](50) NULL,
      [Action_1] [varchar](50) NULL,
      [Action_2] [varchar](50) NULL,
 CONSTRAINT [PK_Arc_AV] PRIMARY KEY CLUSTERED
(
      [EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

EventID should be an index (auto) identification

i cant parse the CSV to the db - is there a way to tell logparser which field goes into what?

this is what im getting

C:\Program Files\Log Parser 2.2>LogParser "SELECT [TimeStamp] AS TimeStamp, [Agg
regated Event Count] AS CountArc, [Name] AS Name INTO dbo.Arc_AV FROM g:\av.csv"
 -i:CSV -o:SQL -server:M0TEK-PC\METRICS -database:Metrics -driver:"SQL Server" -
username:dummy -password:********* -createTable:OFF
Task aborted.
SQL table column "EventID_DB" data type is not compatible with SELECT clause
  item "TimeStamp" (type STRING)

0
Comment
Question by:m0tek
  • 2
3 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 26097590
Use a trick - put the ID as the last physical field in the table, then try again:

CREATE TABLE [dbo].[Arc_AV](
      [TimeStamp] [varchar](50) NULL,
      [CountArc] [smallint] NULL,
      [Name] [varchar](50) NULL,
      [FileName] [varchar](300) NULL,
      [FilePath] [varchar](300) NULL,
      [DestIP] [varchar](50) NULL,
      [DestResource] [varchar](50) NULL,
      [DestHost] [varchar](50) NULL,
      [DestZone] [varchar](50) NULL,
      [DeviceHostName] [varchar](50) NULL,
      [DeviceZone] [varchar](50) NULL,
      [Action_1] [varchar](50) NULL,
      [Action_2] [varchar](50) NULL,
      [EventID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Arc_AV] PRIMARY KEY CLUSTERED
(
      [EventID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
0
 

Author Comment

by:m0tek
ID: 26103351
still when i try to insert it wont go in :(

Task aborted.
SQL table column "FileName" data type is not compatible with SELECT clause
  item "Aggregated Event Count" (type INTEGER)

Statistics:
-----------
Elements processed: 0
Elements output:    0
Execution time:     0.13 seconds
0
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 26107212
Okay - time to use a trick then.  This staging style is my preferred method anyway.

First, create a staging table with a simple INT column in place of the identity column, something like:

CREATE TABLE [dbo].[Arc_AV_Staging](
      [EventID] [int] NOT NULL,
      [TimeStamp] [varchar](50) NULL,
      [CountArc] [smallint] NULL,
      [Name] [varchar](50) NULL,
      [FileName] [varchar](300) NULL,
      [FilePath] [varchar](300) NULL,
      [DestIP] [varchar](50) NULL,
      [DestResource] [varchar](50) NULL,
      [DestHost] [varchar](50) NULL,
      [DestZone] [varchar](50) NULL,
      [DeviceHostName] [varchar](50) NULL,
      [DeviceZone] [varchar](50) NULL,
      [Action_1] [varchar](50) NULL,
      [Action_2] [varchar](50) NULL,
 CONSTRAINT [PK_Arc_AV_Staging] PRIMARY KEY CLUSTERED
(
      [EventID] ASC
)
) ON [PRIMARY]

Now, in the LogParser query, use "SEQUENCE(1)" in the first position of the Select command.  This will have the effect of adding the rows with a sequenced value in the EventID column.  Finally, INSERT into the target Arc_AV table the data from the staging table ORDER BY the EventID from the Staging table.  A bit more effort, but it will handle the situation.


0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question