Flat File Import in SQL Server 2005

Question

Flat File Import in SQL Server 2005

pt9s

SSC Veteran

Points: 296
More actions
October 12, 2010 at 8:57 am

#227827

I am trying to import the following file
My\flat\file\delimited
by\backslash
to come in as
My |flat |File |delimited
by |backslash |NULL |NULL
No joy so far. 🙁
What do I need to do???
Please help!

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

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Since every row doesn't have the name number of columns, you're going to have to import it manually instead of using a flat-file task.

-- first, create a temp table to hold the data

IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test;

CREATE TABLE #Test (RowData varchar(max));

-- bulk load the file into one column.

BULK INSERT #Test FROM 'C:\temp\DelimiterTest.txt';

-- add an identity column to this table

ALTER TABLE #Test

ADD RowID INT IDENTITY;

;WITH CTE AS

(

-- get the data from the temp table,

-- and split it out into the delimited parts

SELECT t1.RowID,

t1.RowData,

ds.Item,

ds.ItemNumber

FROM #Test t1

CROSS APPLY dbo.DelimitedSplit8K(t1.RowData, '\') ds

)

-- return the results

SELECT RowID,

RowData,

Column1 = MAX(CASE WHEN ItemNumber = 1 THEN Item ELSE NULL END),

Column2 = MAX(CASE WHEN ItemNumber = 2 THEN Item ELSE NULL END),

Column3 = MAX(CASE WHEN ItemNumber = 3 THEN Item ELSE NULL END),

Column4 = MAX(CASE WHEN ItemNumber = 4 THEN Item ELSE NULL END)

FROM CTE

GROUP BY RowID, RowData

ORDER BY RowID;

Click here for the latest Delimited Split Function.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

pt9s SSC Veteran Points: 296 More actions · Answer 2

pt9s

SSC Veteran

Points: 296

October 13, 2010 at 7:28 am

#1236182

Wayne

This is great!

Thanks so much!!!

dg227 SSCrazy Points: 2017 More actions · Answer 3

An alternative is to use PowerShell: you can read in the original flat file, count the number of old delimiters in the header/top-most record, and the replace each of the old delimiters with the required " |" delimiter.

By doing an initial delimiter count in the header/first row, additional delimiters can be added on to the end of subsequent records that are shorter than the header. This could be faster when a larger file is being consumed ... just depends on your situation and what you're trying to do.

WayneS SSC Guru Points: 95460 More actions · Answer 4

pt9s (10/13/2010)
Wayne
This is great!
Thanks so much!!!

You're welcome.

Does it solve your issue?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2