Large fixed width to sql help please!

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Large fixed width to sql help please!

Large fixed width to sql help please!

Rate Topic

Display Mode

Topic Options

Author

Message

jerome.morris

Posted Thursday, November 08, 2012 9:29 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 18, 2013 6:06 AM
Points: 99, Visits: 118

When everything else fails I will ask you guys. This has been annoying me for weeks now and although I can get my c# code to complete the task at hand, the other system I need to tackle are not running Win 64bit so I am always faced with the dreaded out of memory problem.

I have a large Fixed width text file (226mb min) and around 97 columns of data. I need to get all this data into my SQL DB table.

Here is my create

USE [SLADB]
GO

/****** Object: Table [dbo].[ProdDataTB] Script Date: 11/08/2012 16:25:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ProdDataTB](
[MachineName] [nvarchar](10) NULL,
[ModeName] [nvarchar](10) NULL,
[FileName] [nvarchar](10) NULL,
[JobName] [nvarchar](10) NULL,
[UserName] [nvarchar](10) NULL,
[TransactionKey] [nvarchar](10) NULL,
[StartDate_Time] [nvarchar](25) NULL,
[StartDate] [nvarchar](10) NULL,
[StartTime] [time](7) NULL,
[StartYYYY] [nvarchar](4) NULL,
[StartMM] [nvarchar](2) NULL,
[StartDD] [nvarchar](2) NULL,
[StartHH] [nvarchar](2) NULL,
[StartNN] [nvarchar](2) NULL,
[StartSS] [nvarchar](2) NULL,
[Spare1] [nvarchar](6) NULL,
[EndDate_Time] [nvarchar](30) NULL,
[EndDate] [nvarchar](10) NULL,
[EndTime] [time](7) NULL,
[EndYYYY] [nvarchar](4) NULL,
[EndMM] [nvarchar](2) NULL,
[EndDD] [nvarchar](2) NULL,
[EndHH] [nvarchar](2) NULL,
[EndNN] [nvarchar](2) NULL,
[EndSS] [nvarchar](2) NULL,
[Spare2] [nvarchar](6) NULL,
[RunTime] [float] NULL,
[DelayTime] [float] NULL,
[MachineStopTime] [float] NULL,
[OperatorStopTime] [float] NULL,
[MachineFaultTime] [float] NULL,
[OperatorFaultTime] [float] NULL,
[OldPiecesFed] [float] NULL,
[OldPiecesCompleted] [float] NULL,
[NumMachineStops] [float] NULL,
[NumOperatorStops] [float] NULL,
[NumDelays] [float] NULL,
[Feeder_00_Count] [float] NULL,
[Feeder_01_Count] [float] NULL,
[Feeder_02_Count] [float] NULL,
[Feeder_03_Count] [float] NULL,
[Feeder_04_Count] [float] NULL,
[Feeder_05_Count] [float] NULL,
[Feeder_06_Count] [float] NULL,
[Feeder_07_Count] [float] NULL,
[Feeder_08_Count] [float] NULL,
[Feeder_09_Count] [float] NULL,
[Feeder_10_Count] [float] NULL,
[Feeder_11_Count] [float] NULL,
[Feeder_12_Count] [float] NULL,
[Feeder_13_Count] [float] NULL,
[Feeder_14_Count] [float] NULL,
[Feeder_15_Count] [float] NULL,
[Feeder_16_Count] [float] NULL,
[Feeder_17_Count] [float] NULL,
[Input_Feeder_Count] [float] NULL,
[Input_SubFdr_1_Count] [float] NULL,
[Input_SubFdr_2_Count] [float] NULL,
[Input_SubFdr_3_Count] [float] NULL,
[Input_SubFdr_4_Count] [float] NULL,
[Input_SubFdr_5_Count] [float] NULL,
[Avg_Chassis_Speed] [float] NULL,
[Shift] [nvarchar](5) NULL,
[Total_Pcs_Outsorted] [float] NULL,
[Total_Pcs_Outsorted_Good] [float] NULL,
[Total_Pcs_Outsorted_Maybe] [float] NULL,
[Total_Pcs_Outsorted_Bad] [float] NULL,
[Total_Pcs_Outsorted_Unk] [float] NULL,
[Bin_01] [float] NULL,
[Bin_02] [float] NULL,
[Bin_03] [float] NULL,
[Bin_04] [float] NULL,
[Bin_05] [float] NULL,
[Bin_06] [float] NULL,
[Bin_07] [float] NULL,
[Bin_08] [float] NULL,
[IST_Bin1] [float] NULL,
[IST_Bin2] [float] NULL,
[IST_Bin3] [float] NULL,
[IST_Bin4] [float] NULL,
[IST_RunOut] [float] NULL,
[Mtr1_NoPrint] [float] NULL,
[Mtr2_NoPrint] [float] NULL,
[Mtr3_NoPrint] [float] NULL,
[Mtr4_NoPrint] [float] NULL,
[Edge_Mark1] [float] NULL,
[Edge_Mark2] [float] NULL,
[Edge_Mark3] [float] NULL,
[No_Seal] [float] NULL,
[Empty_Cycles] [float] NULL,
[Filled_Cycles] [float] NULL,
[MidRunTime] [nvarchar](8) NULL,
[SubShiftExt] [nvarchar](2) NULL,
[ShiftDateAdjust] [nvarchar](10) NULL,
[PiecesFed] [float] NOT NULL,
[PiecesCompleted] [float] NULL,
[Spare3] [nvarchar](68) NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_ProdDataTB] 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

Insert into

INSERT INTO [SLADB].[dbo].[ProdDataTB]
([MachineName]
,[ModeName]
,[FileName]
,[JobName]
,[UserName]
,[TransactionKey]
,[StartDate_Time]
,[StartDate]
,[StartTime]
,[StartYYYY]
,[StartMM]
,[StartDD]
,[StartHH]
,[StartNN]
,[StartSS]
,[Spare1]
,[EndDate_Time]
,[EndDate]
,[EndTime]
,[EndYYYY]
,[EndMM]
,[EndDD]
,[EndHH]
,[EndNN]
,[EndSS]
,[Spare2]
,[RunTime]
,[DelayTime]
,[MachineStopTime]
,[OperatorStopTime]
,[MachineFaultTime]
,[OperatorFaultTime]
,[OldPiecesFed]
,[OldPiecesCompleted]
,[NumMachineStops]
,[NumOperatorStops]
,[NumDelays]
,[Feeder_00_Count]
,[Feeder_01_Count]
,[Feeder_02_Count]
,[Feeder_03_Count]
,[Feeder_04_Count]
,[Feeder_05_Count]
,[Feeder_06_Count]
,[Feeder_07_Count]
,[Feeder_08_Count]
,[Feeder_09_Count]
,[Feeder_10_Count]
,[Feeder_11_Count]
,[Feeder_12_Count]
,[Feeder_13_Count]
,[Feeder_14_Count]
,[Feeder_15_Count]
,[Feeder_16_Count]
,[Feeder_17_Count]
,[Input_Feeder_Count]
,[Input_SubFdr_1_Count]
,[Input_SubFdr_2_Count]
,[Input_SubFdr_3_Count]
,[Input_SubFdr_4_Count]
,[Input_SubFdr_5_Count]
,[Avg_Chassis_Speed]
,[Shift]
,[Total_Pcs_Outsorted]
,[Total_Pcs_Outsorted_Good]
,[Total_Pcs_Outsorted_Maybe]
,[Total_Pcs_Outsorted_Bad]
,[Total_Pcs_Outsorted_Unk]
,[Bin_01]
,[Bin_02]
,[Bin_03]
,[Bin_04]
,[Bin_05]
,[Bin_06]
,[Bin_07]
,[Bin_08]
,[IST_Bin1]
,[IST_Bin2]
,[IST_Bin3]
,[IST_Bin4]
,[IST_RunOut]
,[Mtr1_NoPrint]
,[Mtr2_NoPrint]
,[Mtr3_NoPrint]
,[Mtr4_NoPrint]
,[Edge_Mark1]
,[Edge_Mark2]
,[Edge_Mark3]
,[No_Seal]
,[Empty_Cycles]
,[Filled_Cycles]
,[MidRunTime]
,[SubShiftExt]
,[ShiftDateAdjust]
,[PiecesFed]
,[PiecesCompleted]
,[Spare3])
VALUES
(<MachineName, nvarchar(10),>
,<ModeName, nvarchar(10),>
,<FileName, nvarchar(10),>
,<JobName, nvarchar(10),>
,<UserName, nvarchar(10),>
,<TransactionKey, nvarchar(10),>
,<StartDate_Time, nvarchar(25),>
,<StartDate, nvarchar(10),>
,<StartTime, time(7),>
,<StartYYYY, nvarchar(4),>
,<StartMM, nvarchar(2),>
,<StartDD, nvarchar(2),>
,<StartHH, nvarchar(2),>
,<StartNN, nvarchar(2),>
,<StartSS, nvarchar(2),>
,<Spare1, nvarchar(6),>
,<EndDate_Time, nvarchar(30),>
,<EndDate, nvarchar(10),>
,<EndTime, time(7),>
,<EndYYYY, nvarchar(4),>
,<EndMM, nvarchar(2),>
,<EndDD, nvarchar(2),>
,<EndHH, nvarchar(2),>
,<EndNN, nvarchar(2),>
,<EndSS, nvarchar(2),>
,<Spare2, nvarchar(6),>
,<RunTime, float,>
,<DelayTime, float,>
,<MachineStopTime, float,>
,<OperatorStopTime, float,>
,<MachineFaultTime, float,>
,<OperatorFaultTime, float,>
,<OldPiecesFed, float,>
,<OldPiecesCompleted, float,>
,<NumMachineStops, float,>
,<NumOperatorStops, float,>
,<NumDelays, float,>
,<Feeder_00_Count, float,>
,<Feeder_01_Count, float,>
,<Feeder_02_Count, float,>
,<Feeder_03_Count, float,>
,<Feeder_04_Count, float,>
,<Feeder_05_Count, float,>
,<Feeder_06_Count, float,>
,<Feeder_07_Count, float,>
,<Feeder_08_Count, float,>
,<Feeder_09_Count, float,>
,<Feeder_10_Count, float,>
,<Feeder_11_Count, float,>
,<Feeder_12_Count, float,>
,<Feeder_13_Count, float,>
,<Feeder_14_Count, float,>
,<Feeder_15_Count, float,>
,<Feeder_16_Count, float,>
,<Feeder_17_Count, float,>
,<Input_Feeder_Count, float,>
,<Input_SubFdr_1_Count, float,>
,<Input_SubFdr_2_Count, float,>
,<Input_SubFdr_3_Count, float,>
,<Input_SubFdr_4_Count, float,>
,<Input_SubFdr_5_Count, float,>
,<Avg_Chassis_Speed, float,>
,<Shift, nvarchar(5),>
,<Total_Pcs_Outsorted, float,>
,<Total_Pcs_Outsorted_Good, float,>
,<Total_Pcs_Outsorted_Maybe, float,>
,<Total_Pcs_Outsorted_Bad, float,>
,<Total_Pcs_Outsorted_Unk, float,>
,<Bin_01, float,>
,<Bin_02, float,>
,<Bin_03, float,>
,<Bin_04, float,>
,<Bin_05, float,>
,<Bin_06, float,>
,<Bin_07, float,>
,<Bin_08, float,>
,<IST_Bin1, float,>
,<IST_Bin2, float,>
,<IST_Bin3, float,>
,<IST_Bin4, float,>
,<IST_RunOut, float,>
,<Mtr1_NoPrint, float,>
,<Mtr2_NoPrint, float,>
,<Mtr3_NoPrint, float,>
,<Mtr4_NoPrint, float,>
,<Edge_Mark1, float,>
,<Edge_Mark2, float,>
,<Edge_Mark3, float,>
,<No_Seal, float,>
,<Empty_Cycles, float,>
,<Filled_Cycles, float,>
,<MidRunTime, nvarchar(8),>
,<SubShiftExt, nvarchar(2),>
,<ShiftDateAdjust, nvarchar(10),>
,<PiecesFed, float,>
,<PiecesCompleted, float,>
,<Spare3, nvarchar(68),>)
GO

Sample data

APS08 C5_LAKE_WE08075_002_0807500236ADAMSAG 26013Sun Jan 01 07:21:59 2012 01/01/201207:21:59 20120101072159 Sun Jan 01 07:30:42 2012 01/01/201207:30:42 20120101073042 196 27 88 212 88 212 498 483 3 0 800000487000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001147000011470000000000000000000000000000000013463 2 0 0 2 0 0 2 0 0 0 488 245 498 483
APS05 C5_LAKE_WE08066_005_0806600536HUGHEPE 28708Sun Jan 01 07:08:38 2012 01/01/201207:08:38 20120101070838 Sun Jan 01 07:47:25 2012 01/01/201207:47:25 20120101074725 1467 45 295 520 295 520 5476 5457 12 2 1500005467000000000000000000000000000000000000000000000109000042480000000000000000000000000000000000000000000000000000000000000000000000000000000000009894000000000000989400000000000000000000000014090 18 0 0 14 4 11 2 0 0 5 5467 275 5476 5457

Can anybody please recomend a fast and effective way of getting the above into my table ?

Jay

Post #1382577

jad0083

Posted Thursday, November 08, 2012 1:54 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 5:49 PM
Points: 5, Visits: 81

I would create a format file and use that to bulk import. This way you have somewhat of a reference to your file architecture, and if there should be any changes, you can do it via the format file.
2nd, I'd first use a staging table (usually filled with CHAR datatype columns), then from there, do your logic/clean up/MERGE with your final table.

Post #1382705

aaron.reese

Posted Thursday, November 08, 2012 2:00 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, May 13, 2013 7:33 AM
Points: 234, Visits: 441

it looks like an opportunity to dip your toe is the SSIS water BigGrin

Post #1382706

George M Parker

Posted Thursday, November 08, 2012 2:06 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:13 PM
Points: 516, Visits: 1,004

Agree, SQL Server Integration Services is the way to go, that's what it was built to do and it does it very well. w00t

Post #1382707

CapnHector

Posted Thursday, November 08, 2012 2:08 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:07 AM
Points: 935, Visits: 1,709

aaron.reese (11/8/2012)

it looks like an opportunity to dip your toe is the SSIS water BigGrin

ill second this if you have never worked with bcp. of the 2 i found SSIS easier to learn. there are tutorials on both bcp and SSIS out there. If you have worked in either *nix or powershell /dos and are good at learning new commands bcp may be quicker for you to learn but SSIS can do alot of preprocessing before you hit the database and save some tsql data manipulation.

Either way its time to pick one and roll with it. if you choose SSIS make sure the Business Inteligence Development Suite is installed on your machine as that is what you use to create the SSIS packages.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler

Post #1382708

Sean Lange

Posted Thursday, November 08, 2012 2:09 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204

My apologies for not providing you the answer you were looking for in your other thread. I thought that this was an extension of your previous issue. I didn't realize you hijacked your own thread. BigGrin

SSIS is your friend for this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1382709

Jeff Moden

Posted Sunday, November 11, 2012 9:41 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:26 PM
Points: 32,890, Visits: 26,760

Obviously, you're not guessing at what each line i the file contains. You have some sort of a record layout for the starting position and length or starting and ending postion of each "field" in each row. Hopefully, it's in spreadsheet form but just about anything will work for this. If you'd attach that record layout, this all might be simpler than you think and might also give you incentive to avoid SSIS for such trivial work. Wink

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1383451

« Prev Topic | Next Topic »

Permissions