August 11, 2013 at 6:50 pm
The file is 600mb + that I am trying to get to the tables, could I import all as string then copy to another table and parse on way. ? Or do you still advise using code ?
August 12, 2013 at 1:12 am
jerome.morris (8/11/2013)
The file is 600mb + that I am trying to get to the tables, could I import all as string then copy to another table and parse on way. ? Or do you still advise using code ?
That's a common technique, but parsing before loading is more efficient. Then again, it depends on what fits in your overall situation.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 21, 2013 at 9:59 am
In the end I opted for converting the flat file to csv then bulk inserting into a temp table all Varchar(100). Now this works great from my C# app but I am at a stop again and google is not really helping. How would you advise I copy this data to the correct table and convert the format on the way ?
Temp Table
USE [SLADB]
GO
/****** Object: Table [dbo].[AlarmDetTest] Script Date: 08/21/2013 16:58:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AlarmDetTest](
[Machine] [varchar](100) NULL,
[Job] [varchar](100) NULL,
[Mode] [varchar](100) NULL,
[User] [varchar](100) NULL,
[JobStart] [varchar](100) NULL,
[Object] [varchar](100) NULL,
[AlarmID] [varchar](100) NULL,
[AlarmStart] [varchar](100) NULL,
[MachineFault] [varchar](100) NULL,
[OperFault] [varchar](100) NULL,
[PiecesFed] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Master Table
USE [SLADB]
GO
/****** Object: Table [dbo].[AlarmDet] Script Date: 08/21/2013 16:59:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AlarmDet](
[Machine] [nchar](10) NULL,
[Job] [nchar](20) NULL,
[Mode] [nchar](10) NULL,
[User] [nchar](10) NULL,
[JobStart] [datetime] NULL,
[Object] [nchar](25) NULL,
[AlarmID] [int] NULL,
[AlarmStart] [nchar](31) NULL,
[MachineFault] [int] NULL,
[OperFault] [int] NULL,
[PiecesFed] [int] NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_AlarmDet] PRIMARY KEY CLUSTERED
(
[Id] 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
Thanks
August 21, 2013 at 3:09 pm
jerome.morris (8/21/2013)
In the end I opted for converting the flat file to csv then bulk inserting into a temp table all Varchar(100). Now this works great from my C# app but I am at a stop again and google is not really helping. How would you advise I copy this data to the correct table and convert the format on the way ?
Answer: hard work.
For integer columns you need something like:
CASE WHEN col NOT LIKE '%[^0-9]%' THEN convert(int, col) END
For the date columns you will have to excel in an orgy of substring and other string functions.
Although, I still don't understand you don't you write a .Net program that reads the file and pass the data in a TVP. .Net has better string-handling abilities than T-SQL. Now you are first converting the file to CSV, load it into a staging table - and you are still not at the target.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 21, 2013 at 3:15 pm
Because I don't have the skill set to do that yet 🙁
August 21, 2013 at 3:23 pm
So why don't acquire those skills by rolling up your sleeves and start working. My examples in http://www.sommarskog.se/arrays-in-sql-2008.html should take you some part of the way.
I mean, your skills does not seem to suffice for the solution you are looking at current, and which is immensly more complex to mantain.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 21, 2013 at 4:57 pm
Ok, I will look at this, but entertain me
insert [dbo].[AlarmDet]
(JobStart)
select
--convert (Jobsatrt (),101)
--SELECT convert(datetime, '23/07/2009', 103)
--convert (datetime, Jobstart,103)
--CONVERT(varchar, CONVERT(datetime, JobStart), 100)
--cast(JobStart as date)
from [SLADB].[dbo].[AlarmDetTest]
How do I get this to work ? I know the format is M/d/yyyyHH:mm:ss
Thanks
Jay
August 21, 2013 at 7:44 pm
jerome.morris (8/21/2013)
Ok, I will look at this, but entertain meinsert [dbo].[AlarmDet]
(JobStart)
select
--convert (Jobsatrt (),101)
--SELECT convert(datetime, '23/07/2009', 103)
--convert (datetime, Jobstart,103)
--CONVERT(varchar, CONVERT(datetime, JobStart), 100)
--cast(JobStart as date)
from [SLADB].[dbo].[AlarmDetTest]
How do I get this to work ? I know the format is M/d/yyyyHH:mm:ss
Thanks
Jay
Apologies for losing track of this post.
Can you post some of the rows you currently have in the AlarmDetTest table so we can work with the actual formats you currently have? If you can post 10 or so rows of the data from that staging table in a readily consumable format, I know we can help. Please see the first link in my signature line below for what I mean by "readily consumable" format and how to make it.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2013 at 12:29 am
jerome.morris (8/21/2013)
How do I get this to work ? I know the format is M/d/yyyyHH:mm:ss
You think you know. Unless the file is produced by a device, it is almost given that there are bad dates in the data.
Let's, you need to get a space in there, and then you can use convert with the correct format code (which you find in Books Online). The problem is getting the space in there. Hm, something like:
substring(col, 1, charindex('/', col) + 4) + ' ' + substring(col, charindex('/', col) + 5, len(substring))
Something like that. I did not test, I leave that to you. But those are the building blocks. Again, use Books Online to learn more about these functions.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply