chop top 2 rows of text file before SQL import? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

chop top 2 rows of text file before SQL import?

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-18 : 16:27:21

Anyone know of a t-sql/dts procedure/script that will chop of the top whatever number of rows in a bunch of text files I want to import?

The first couple of rows in these text files are narrative garbage. If I chop them off them I arrive at row #3, which contains valid fieldnames.

thx!

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-18 : 16:30:05

Check out BULK INSERT in BOL...there's an option for FIRSTROW=3

Brett

8-)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-02-19 : 07:55:20

You can do this in DTS in the properties of the text file connection, "Skip rows" option.

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-23 : 10:10:50

I don't think I stated this properly. I need to chop of the first few rows of data from the text file, then the next available row that I keep will contain the field names.

Unless I'm mistaken, DTS automatically enters "Col1", "Col2" default names for fields, thus relegating my true fieldnames to first row status.

Will Bulk Insert allow me to chop off the first few rows and then use the fourth row for fieldnames?

thx!

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-23 : 10:13:49

If Bulk Insert FIRSTROW=3 works the way I think it does, then would this mean that FIRSTROW = fieldnames?

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-23 : 10:51:08

You want to take the "header" row of the data and create the table with those names?

Interesting...doesn't work that way...you need more code....

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-23 : 10:55:28

What's the format of the data btw?

Tab delimited, fixed width, what?

Brett

8-)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-23 : 11:29:31

It's tab delimited. The file is spit out by a machine, first couple of rows are narrative gibberish, third row is field names, fourth row is first real record.

thx

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-23 : 11:54:08

How do you know what the datatypes are going to be?

Sounds like you really need to predefine the data...

here's my shot:



USE Northwind
GO

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 Varchar(8000))
GO

-- SIMULATE bcp in

INSERT INTO myTable99(Col2)
SELECT 'sdfgsdglkjeroijeortnewortnoweitrjwoeirjweor' UNION ALL
SELECT 'HEADER INFORMATION' UNION ALL
SELECT 'LastName'+CHAR(9)+'FirstName' UNION ALL
SELECT 'Steele'+CHAR(9)+'Kit' UNION ALL
SELECT 'Rob'+CHAR(9)+'Volk' UNION ALL
SELECT 'Nigel'+CHAR(9)+'Rivett'
GO

SELECT * FROM myTable99
GO

DECLARE @Cols varchar(500), @SQL varchar(2000)

SELECT @Cols = REPLACE(Col2,CHAR(9),' varhcar(50),') + ' varchar(50)' FROM myTable99 WHERE Col1 = 3

SELECT @sql = 'CREATE TABLE myTable00('+ @Cols + ')'

EXEC(@sql)

-- Then BULK INSERT with FIELDTERMINATOR = '\t', FIRSTROW = 4
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

Brett

8-)

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-23 : 12:02:10

there are only two fields in the table that I actually care about. I won't use the rest of the fields. The first, which will be the PK, is an integer, and the second is varchar.

thx

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2004-02-23 : 13:07:40

reading up on bcp and Bulk Insert I realize I misunderstood how they work. Will think on this and post a more sensible question later.

thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources