m0tek
asked on
Insert CSV to SQL using logparser - MUCHO Points ;)
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)
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)
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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]