troubleshooting Question

Importing a large fixed length .txt file into SQL Server

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft SQL ServerCOBOLSQL
37 Comments1 Solution771 ViewsLast Modified:
I have another question   here     open explaining the situation.

That question is asking how to transfer this table directly to SQL server using ms Access 2013.  At this point there has been no resolution so I am going to asking the question specific to SQL Server.

How can I import this very large, fixed length field .txt file directly into a SQL Server table.

This is the layout of the table:

CREATE TABLE [dbo].[SQCS_CostFile_Import](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CostName] [nvarchar](30) NULL,
	[MuniCode] [nvarchar](4) NULL,
	[LotBlock] [nvarchar](17) NULL,
	[TaxType] [nvarchar](1) NULL,
	[MuniCode02] [nvarchar](4) NULL,
	[ControlNumber] [nvarchar](7) NULL,
	[TaxType02] [nvarchar](1) NULL,
	[CostDetail01_100] [ntext] NULL,
	[GRBNumnber] [nvarchar](30) NULL,
	[GDNumber] [nvarchar](30) NULL,
	[Remarks] [nvarchar](76) NULL,
	[upsize_ts] [timestamp] NULL,
 CONSTRAINT [aaaaaSQCS_CostFile_Import_PK] PRIMARY KEY NONCLUSTERED 
(
	[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] TEXTIMAGE_ON [PRIMARY]

And this is the layout of each record in the .txt file
                                 Col  Lgth
Cost Name               1   30      ShortText
MuniCode               31  4       ShortText
LotBlock                  35  17     ShortText
TaxType                  52  1       ShortText
MuniCode02          54   4      ShortText
ControlNumber    57    7     ShortText
TaxType02             64   1      ShortText
CostDetail              65   14000          Long Text
GRBNumber    14065  30     ShortText
GDNumber      14095  30      SHortText
Remarks          14125   75      ShortText

Can someone explain how to  import the file 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\CostSheet\TQCSFILE.txt' into the SQL table?

This is someting that will have to be done more than once so it would be best if everything needed was contained in a Stored Procedure.
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 37 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 37 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros