stored procedure import text file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

stored procedure import text file

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-15 : 08:16:37

Karen writes "I have a stored procedure that imports a text file using a BULK INSERT and then manipulates the data. The initial text file data comes from a bar code reader. The output from the barcode reader can be either five or eight fields per row. Where there are five columns in a row there are not commas for the three 'empty' rows. The column terminator is a comma and the row terminator is a new row. So, the format of the file is as follows:

99,9,1,XXXXXXXXXX,XXXXXXXXXXXXXXXXXXXXX
99,9,2,,,XXXXXXXXXXXXXXXXXXXXX,XXXXXXXXXX,XXXXXXXXXXXXXXXXXXXXXX

Where the number of fields per row is 5 there is always a 1 in column 3. Where the number of fields is 8 column 3 is always 2.

Is there anyway of importing text files with varying numbers of fields per row as modifying the file with excel or a text editor is proving problematic for the users.

Many thanks

Karen"

Kristen
Test

22859 Posts

Posted - 2005-09-15 : 08:56:02

I would get all the barcode data into a temporary table, and then SPLIT the rows into columns - at that stage not worrying whether there were 5 or 8 columns, and then validate and process the resultant data:


DECLARE	@ImportData	 TABLE
(
	T_ID		int identity(1,1) NOT NULL,
	T_Import	varchar(1000) NULL,
	T_Col1		varchar(1000) NULL,
	T_Col2		varchar(1000) NULL,
	T_Col3		varchar(1000) NULL,
	T_Col4		varchar(1000) NULL,
	T_Col5		varchar(1000) NULL,
	T_Col6		varchar(1000) NULL,
	T_Col7		varchar(1000) NULL,
	T_Col8		varchar(1000) NULL,
	PRIMARY KEY
	(
		T_ID
	)
)

INSERT INTO @ImportData ( T_Import )
SELECT	'99,9,1,XXXXXXXXXX,XXXXXXXXXXXXXXXXXXXXX' UNION ALL
SELECT '99,9,2,,,XXXXXXXXXXXXXXXXXXXXX,XXXXXXXXXX,XXXXXXXXXXXXXXXXXXXXXX'

DECLARE	@I1	int,
	@I2	int,
	@I3	int,
	@I4	int,
	@I5	int,
	@I6	int,
	@I7	int,
	@I8	int

UPDATE U
SET
	  @I1 = CHARINDEX(',', T_Import + ',')
	, [T_Col1] = SUBSTRING(T_Import + ',', 1, @I1-1)
	, @I2 = CASE WHEN COALESCE(@I1, 0) = 0 THEN 0 ELSE 
		COALESCE(CHARINDEX(',', T_Import + ',', @I1+1), DATALENGTH(T_Import + ',')) END
	, [T_Col2] = CASE WHEN @I2 = 0 THEN NULL ELSE SUBSTRING(T_Import + ',', @I1+1, @I2-@I1-1) END
	, @I3 = CASE WHEN COALESCE(@I2, 0) = 0 THEN 0 ELSE 
		COALESCE(CHARINDEX(',', T_Import + ',', @I2+1), DATALENGTH(T_Import + ',')) END
	, [T_Col3] = CASE WHEN @I3 = 0 THEN NULL ELSE SUBSTRING(T_Import + ',', @I2+1, @I3-@I2-1) END
	, @I4 = CASE WHEN COALESCE(@I3, 0) = 0 THEN 0 ELSE 
		COALESCE(CHARINDEX(',', T_Import + ',', @I3+1), DATALENGTH(T_Import + ',')) END
	, [T_Col4] = CASE WHEN @I4 = 0 THEN NULL ELSE SUBSTRING(T_Import + ',', @I3+1, @I4-@I3-1) END
	, @I5 = CASE WHEN COALESCE(@I4, 0) = 0 THEN 0 ELSE 
		COALESCE(CHARINDEX(',', T_Import + ',', @I4+1), DATALENGTH(T_Import + ',')) END
	, [T_Col5] = CASE WHEN @I5 = 0 THEN NULL ELSE SUBSTRING(T_Import + ',', @I4+1, @I5-@I4-1) END
	, @I6 = CASE WHEN COALESCE(@I5, 0) = 0 THEN 0 ELSE 
		COALESCE(CHARINDEX(',', T_Import + ',', @I5+1), DATALENGTH(T_Import + ',')) END
	, [T_Col6] = CASE WHEN @I6 = 0 THEN NULL ELSE SUBSTRING(T_Import + ',', @I5+1, @I6-@I5-1) END
	, @I7 = CASE WHEN COALESCE(@I6, 0) = 0 THEN 0 ELSE 
		COALESCE(CHARINDEX(',', T_Import + ',', @I6+1), DATALENGTH(T_Import + ',')) END
	, [T_Col7] = CASE WHEN @I7 = 0 THEN NULL ELSE SUBSTRING(T_Import + ',', @I6+1, @I7-@I6-1) END
	, @I8 = CASE WHEN COALESCE(@I7, 0) = 0 THEN 0 ELSE
		COALESCE(CHARINDEX(',', T_Import + ',', @I7+1), DATALENGTH(T_Import + ',')) END
	, [T_Col8] = CASE WHEN @I8 = 0 THEN NULL ELSE SUBSTRING(T_Import + ',', @I7+1, @I8-@I7-1) END
FROM	@ImportData U

SELECT	*
FROM	@ImportData

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources