Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

Process vertical file in SQL 2005

Experts,

I have a file with "vertical" records that I need to insert into a SQL Server table.  Here's the destination table design:

AutoID int pk
field1 varchar(50)
field2 varchar(50)
field3 varchar(50)
field4 varchar(50)
field5 varchar(50)
field6 varchar(50)
field7 varchar(50)

..and here's the data in the file

RecordNum           Value
64                            field1:            value1
65                            field2:            value2
66                            field3:            value3
67                            field4:            value4
68                            field5:            value5
69                            field6:            value6
70                            field7:            value7

The semicolon delimits the field name and the value.

I need this to be entered into a single record in the destination table:
field1, field2, field3, field4, field5, field6, field7

Any suggestions?
0
hennessym
Asked:
hennessym
  • 4
  • 4
2 Solutions
 
reb73Commented:
Bulk Import the data from the file into a table (TableFromFile in the code below) and run the attached code snippet against it..

(Not 100% sure if this will work, but give it a try running only the select statement and initially)
INSERT yourtable
	(field1, field2, field3, field4, field5, field6, field7)
SELECT
	f1.field1,f2.field2,f3.field3,f4.field4,f5.field5,f6.field7,f7.field7
FROM
	(SELECT field1 = LTRIM(RTRIM(REPLACE(value,'field1:',''))), rownum = row_number() over (order by recordnum)
	 FROM TableFromFile
         WHERE value like 'field1%'
	) f1
FULL OUTER JOIN
	(SELECT field2 = LTRIM(RTRIM(REPLACE(value,'field2:',''))), rownum = row_number() over (order by recordnum)
	 FROM TableFromFile
         WHERE value like 'field2%'
	) f2 ON f2.rownum = f1.rownum
FULL OUTER JOIN
	(SELECT field3 = LTRIM(RTRIM(REPLACE(value,'field3:',''))), rownum = row_number() over (order by recordnum)
	 FROM TableFromFile
         WHERE value like 'field3%'
	) f3 ON f3.rownum = f2.rownum AND f3.rownum = f1.rownum
FULL OUTER JOIN
	(SELECT field4 = LTRIM(RTRIM(REPLACE(value,'field4:',''))), rownum = row_number() over (order by recordnum)
	 FROM TableFromFile
         WHERE value like 'field4%'
	) f4 ON f4.rownum = f3.rownum AND f4.rownum = f2.rownum AND f4.rownum = f1.rownum
FULL OUTER JOIN
	(SELECT field5 = LTRIM(RTRIM(REPLACE(value,'field5:',''))), rownum = row_number() over (order by recordnum)
	 FROM TableFromFile
         WHERE value like 'field5%'
	) f5 ON f5.rownum = f4.rownum AND f5.rownum = f3.rownum AND f5.rownum = f2.rownum AND f5.rownum = f1.rownum
FULL OUTER JOIN
	(SELECT field6 = LTRIM(RTRIM(REPLACE(value,'field6:',''))), rownum = row_number() over (order by recordnum)
	 FROM TableFromFile
         WHERE value like 'field6%'
	) f6 ON f6.rownum = f5.rownum AND f6.rownum = f4.rownum AND f6.rownum = f3.rownum AND f6.rownum = f2.rownum
	    AND f6.rownum = f1.rownum
FULL OUTER JOIN
	(SELECT field7 = LTRIM(RTRIM(REPLACE(value,'field7:',''))), rownum = row_number() over (order by recordnum)
	 FROM TableFromFile
         WHERE value like 'field7%'
	) f7 ON f7.rownum = f6.rownum AND f7.rownum = f5.rownum AND f7.rownum = f4.rownum AND f7.rownum = f3.rownum
	    AND f7.rownum = f2.rownum AND f7.rownum = f1.rownum

Open in new window

0
 
ralmadaCommented:
why don't you import the data as is ("vertical" order) to table and then do a PIVOT on that table to your final table?
Here's an example:
This is the text file I have
RecordNum;fieldname;value
64;field1;value1
65;field2;value2
66;field3;value3
67;field4;value4
68;field5;value5
69;field6;value6
70;field7;value7
and this is the code I use in SQL

create table vertical (
recordnum int,
fieldname varchar(50),
value varchar(50)
)
go;
 
create table finaltable (
field1 varchar(50),
field2 varchar(50),
field3 varchar(50),
field4 varchar(50),
field5 varchar(50),
field6 varchar(50),
field7 varchar(50)
)
go;
 
 
BULK
 
INSERT vertical
FROM 'C:\Users\rodolfo\Documents\SQL Server Management Studio\Projects\verticalfile.txt'
WITH
(
firstrow = 2,
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n'
 
)
go;
 
insert finaltable
select [field1],
	[field2],
	[field3],
	[field4],	
	[field5],
	[field6],
	[field7]
from (select fieldname, value from vertical) o
pivot (max(value) for fieldname in ([field1],[field2],[field3],[field4],[field5],[field6],[field7])) p
 
select * from finaltable

Open in new window

0
 
hennessymAuthor Commented:
Thanks for the responses, guys!  

Ralmada, your approach works great except that it only enters a single record into finaltable.  I should have mentioned this in my question, but I'm dealing with a 1.3 GB file and records are divided by a blank row.  So my data file looks like this:

RecordNum           Value
64                            field1:            value1
65                            field2:            value2
66                            field3:            value3
67                            field4:            value4
68                            field5:            value5
69                            field6:            value6
70                            field7:            value7
71
72                            field1:            value1a
73                            field2:            value2a
74                            field3:            value3a
75                            field4:            value4a
76                            field5:            value5a
77                            field6:            value6a
78                            field7:            value7a

Also, the record numbers don't actually exist in the file.  I mentioned them because my initial thought was to bulk import into a table and then write a query to determine the record numbers of all blank lines which would tell me the beginning and ending numbers for each record.

Reb73, I'm going to try your approach momentarily.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ralmadaCommented:
Ok, if recordnum does not exists, what you can do is use this insert statement:
insert finaltable
select [field1],
	[field2],
	[field3],
	[field4],	
	[field5],
	[field6],
	[field7]
from (select select row_number() over (partition by fieldname order by fieldname) as rowno, fieldname, value from vertical) o
pivot (max(value) for fieldname in ([field1],[field2],[field3],[field4],[field5],[field6],[field7])) p

Open in new window

0
 
hennessymAuthor Commented:
Ralmada,

Thanks again for the quick response!

That works except that the records are inconsistent with my data file.  Here's a sample of what I see in my file (with actual data):

ASHandle:            AS0
OrgID:               IANA
ASName:              IANA-RSVD-0
RegDate:             2002-09-13
Comment:             Reserved - May be used to identify non-routed networks
Updated:             2002-09-13
Source:              ARIN

ASHandle:            AS1
OrgID:               LVLT
ASName:              LVLT-1
ASNumber:            1
RegDate:             2001-09-20
Updated:             2004-06-04
Source:              ARIN

ASHandle:            AS2
OrgID:               UNIVER-19
ASName:              DCN-AS
ASNumber:            2
RegDate:             1991-01-10
Updated:             2007-06-11
Source:              ARIN

ASHandle:            AS3
OrgID:               MIT-2
ASName:              MIT-GATEWAYS
ASNumber:            3
RegDate:            
Updated:             1991-06-27
TechHandle:          RH164-ARIN
Source:              ARIN

I'm attaching a copy of what I see when I run your script.  You'll notice that the ASN Handle field is always NULL, and several values are added to the incorrect record.  Any suggestions?






pivot.txt
0
 
hennessymAuthor Commented:
One additional complicating factor is that some records have multiple entries for the Comments field, e.g.

ASHandle:            AS7
OrgID:               RIPE
ASName:              RIPE-ASNBLOCK-7
ASNumber:            7
RegDate:             2002-10-15
Comment:             These addresses have been further assigned to users in
Comment:             the RIPE NCC region. Contact information can be found in
Comment:             the RIPE database at http://www.ripe.net/whois
Updated:             2003-04-25
Source:              ARIN

I'll need to concatenate those values into a single field.
0
 
ralmadaCommented:
First of all in your bulk insert, change
firstrow = 2 to firstrow = 1
Regarding the comments field. Is it possible that you number each one of them? i.e: Comment1, Comment2, Comment3, etc.
And regarding the null values please try this:

 

insert finaltable
select [field1],
	[field2],
	[field3],
	[field4],	
	[field5],
	[field6],
	[field7]
from (select select row_number() over (partition by fieldname order by fieldname) as rowno, right(fieldname, len(fieldname) -2), value from vertical) o
pivot (max(value) for fieldname in ([field1],[field2],[field3],[field4],[field5],[field6],[field7])) p

Open in new window

0
 
ralmadaCommented:
sorry typo in line 9 above:
from (select select row_number() over (partition by fieldname order by fieldname) as rowno, right(fieldname, len(fieldname) -2) as fieldname, value from vertical) o
0
 
hennessymAuthor Commented:
Thanks, guys.  It appears that both of your solutions work provided my data file is properly formatted (all possible fields exist and no duplicate field names within a vertical record).

I'll have to write a script to reformat the data file, but then I'll be able to use your solutions to add the data to the database.

Thanks again for your help!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now