October 19, 2010 at 6:54 pm
Ok I have an application that will let me define the start and end positions for each field. With 700 + Fields this can be a real headache. Now although we have gone over the file many times it seems as if we have data rows off.
Now the client has supplied the field definitions in excel.
So I have a field number, start position and length. going from row one to row 708
Example
1 1 8
2 10 1
3 12 50
etc....
Can someone recommend a way to import the data, parsing it on the fly, or import each row as a single column and then split it up after?
Anything that someone can recommend would be great; thanks in advance.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 19, 2010 at 7:27 pm
What I am THINKING of doing... just as a matter of reference here. I am thinking of importing the definition xls sheet as a lookup table
THEN Import the data to a staging table. Each record would be a single field.
THEN in a memory variable grab a row, parse each position to a field and insert into a table that has the 708 fields.
The end result is that I need to be able to reliably work with the data and then turn around and export it for further processing.
Anyway I am thinking that this is not the best route so I am looking for some advice.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 19, 2010 at 9:02 pm
First, you need to know if you want the data stored into separate columns in a permanent table, or just returned through a select statement.
This code handles both ways. See remarks in the code.
-- create temporary tables, populate with data
if object_id('tempdb..#MyTableWithColumnSizes') IS NOT NULL DROP TABLE #MyTableWithColumnSizes;
if object_id('tempdb..#test') IS NOT NULL DROP TABLE #test;
CREATE TABLE #MyTableWithColumnSizes(id int, Start smallint, Size tinyint);
insert into #MyTableWithColumnSizes
SELECT 1, 1, 8 UNION ALL
SELECT 2, 9, 1 UNION ALL
SELECT 3, 10, 50;
CREATE TABLE #test (DataLine varchar(1000));
-- load your data in
--BULK INSERT #test FROM 'c:\MyFile.txt';
-- however, we'll simulate the bulk insert here:
INSERT INTO #test
SELECT 'Jeffrey AWilliams, how are you?';
--Next: do you want the data stored in a permanent table, or just returned?
--Permanent table:
DECLARE @sql varchar(max);
-- first, build an alter table command to add all of the columns
SELECT @sql = 'ALTER TABLE #test ADD ' + STUFF((
SELECT ',Field' + convert(varchar(3), id) + ' varchar('+ + convert(varchar(3), size) + ')'
FROM #MyTableWithColumnSizes
ORDER BY id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'');
EXEC (@sql);
PRINT @sql;
-- now, update the new columns with the data originally entered in DataLine
SELECT @sql = 'UPDATE #test SET ' + STUFF((
SELECT ',Field' + convert(varchar(3), id) + '= SubString(DataLine, ' + convert(varchar(3), Start) + ',' + convert(varchar(3), Size) + ')'
FROM #MyTableWithColumnSizes
ORDER BY id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'');
EXEC (@sql);
PRINT @sql;
-- show what was put into the table:
SELECT Field1, Field2, Field3 FROM #test;
-- to just return the data:
SELECT @sql = 'SELECT ' + STUFF((
SELECT ',Field' + convert(varchar(3), id) + ' = SubString(DataLine, ' + convert(varchar(3), Start) + ',' + convert(varchar(3), Size) + ')'
FROM #MyTableWithColumnSizes
ORDER BY id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'') +
' FROM #test';
EXEC (@sql);
PRINT @sql;
if object_id('tempdb..#MyTableWithColumnSizes') IS NOT NULL DROP TABLE #MyTableWithColumnSizes;
if object_id('tempdb..#test') IS NOT NULL DROP TABLE #test;
Results:
ALTER TABLE #test ADD Field1 varchar(8),Field2 varchar(1),Field3 varchar(50)
UPDATE #test SET Field1= SubString(DataLine, 1,8),Field2= SubString(DataLine, 9,1),Field3= SubString(DataLine, 10,50)
Field1 Field2 Field3
-------- ------ --------------------------------------------------
Jeffrey A Williams, how are you?
Field1 Field2 Field3
-------- ------ --------------------------------------------------
Jeffrey A Williams, how are you?
SELECT Field1 = SubString(DataLine, 1,8),Field2 = SubString(DataLine, 9,1),Field3 = SubString(DataLine, 10,50) FROM #test
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply