Large fixed width to sql help please!

Question

Large fixed width to sql help please!

jerome.morris

SSCrazy

Points: 2829
More actions
November 8, 2012 at 9:29 am

#266833

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jad0083 Valued Member Points: 61 More actions · Answer 1

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.

aaron.reese SSChampion Points: 13498 More actions · Answer 2

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

George M Parker SSCertifiable Points: 5412 More actions · Answer 3

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:

CapnHector SSCoach Points: 16143 More actions · Answer 4

aaron.reese (11/8/2012)
it looks like an opportunity to dip your toe is the SSIS water 😀

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[/url] 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[/url]

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

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. 😀

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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 6

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. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)