Import a textfile - SQL Server Forums

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)

Import a textfile

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-24 : 17:39:23

I am trying to import a textfile, but I can't figure out how to do it. I am using the Import/Export function. I have used it many times, but I can't figure out this one textfile. I can email it to you, so you can look at it. Let me know. Thanks!

Brenda

If it weren't for you guys, where would I be?

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-11-24 : 18:09:49

Brenda, What format is the text file in? For example is it comma seperated? You might also want to look at BCP in BOL as a way of doing this.

steve

To alcohol ! The cause of - and solution to - all of life's problems

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-29 : 15:17:54

I pulled this data out of Paradox and need to put it into SQL Server. Here is a sample line of the data:

"011-381070"|"1"|"Tracey called with questions and I answered them. She sounded like she will send in the cont. bs\n5/03/2004 old add 681 cummings ave, bessemer al 35023-1209. sending cl to new add.jh\n6/12/2004 pc.jmd\n7/22/2004 pc.jmd"

It contains a case number, part number, and then notes. The notes would be a lot longer though.

I have used BCP, but can't get it to work for some reason. Any ideas?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-29 : 15:20:15

You can use the import/export wizard, just tell it that | is your separator. You can also use bcp for this, use -t switch with | separator (-t|).

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-30 : 05:04:40

See
http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html
http://www.nigelrivett.net/ImportTextFiles.html

You will find bcp simpler and faster than dts for dealing with text files.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-30 : 15:37:47

Yes, but you'll find the quotes to be a royal pain...

How much data are we talking about?

I would load that into a single column varchar(8000) table.

I would then write sql to parse it out into it's final format...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-30 : 15:51:02

Like:


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myStage99(Col1 varchar(8000))
GO

-- mimic bcp

INSERT INTO myStage99(Col1)
SELECT    '"011-381070"|"1"|"Tracey called with questions and I answered them. She sounded like she will send in '
	+ 'the cont. bs\n5/03/2004 old add 681 cummings ave, bessemer al 35023-1209. sending cl to new add.jh\n'
	+ '6/12/2004 pc.jmd\n7/22/2004 pc.jmd"' 

SELECT * FROM myStage99

SELECT SUBSTRING(Col1,2,CHARINDEX('|',Col1)-3) --Col 1
, SUBSTRING(Col1,CHARINDEX('|',Col1)+2,CHARINDEX('|',Col1,CHARINDEX('|',Col1)+1)-CHARINDEX('|',Col1)-3) --Col 2
, REVERSE(SUBSTRING(REVERSE(Col1),1,CHARINDEX('|',REVERSE(Col1))-2)) -- Col3
FROM myStage99
GO

SET NOCOUNT OFF
DROP TABLE myStage99
GO

Brett

8-)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-30 : 17:15:17

Thanks everyone for all your help!

Now I just want to know how to take out quotation marks in a text datatype field. Like:

"brendalisalowe" --> brendalisalowe

REPLACE? TRIM? Etc...

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-30 : 17:17:24

Did you look up REPLACE in SQL Server Books Online?

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-30 : 17:26:20

Yes, I did look it up. I always try to look there first, even though it doesn't seem like it. I tried this:

select REPLACE(notes,'"','')from tblparadox

'notes' is a column, but it doesn't like it. Can you not use a column name?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-30 : 17:29:04

Yes it works for columns. Do you get an error?

It works fine for me:

declare @notes varchar(512)

set @notes = '"tara"'

select REPLACE(@notes,'"','')

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-30 : 17:31:23

It says this:

Argument data type text is invalid for argument 1 of replace function.

Brenda

If it weren't for you guys, where would I be?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-30 : 17:32:26

What about some sort of SUBSTRING thing? How would I get the length of a text field? LEN()??

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-30 : 17:33:15

What is the data type of the notes column?

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-30 : 17:35:25

text, because I need a column that can contain lots of data.

Is that what it should be?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-30 : 17:37:44

Well text is difficult to work with. Do any rows pass 8000 bytes in that column? If not, then just use varchar(8000). If they do, well you'll need to use UPDATETEXT to modify your column. I've never used it as I've never had to modify text columns, so I can't be of much help with the syntax.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-30 : 17:40:09

Is there anyway to test to see if any of the fields contain more than 8000 bytes?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-30 : 17:42:48

SELECT DATALENGTH(notes)
FROM tblParadox

SELECT MAX(DATALENGTH(notes))
FROM tblParadox

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-30 : 17:49:10

When I run this:

SELECT MAX(DATALENGTH(notes))
FROM tblParadox

I get 13299.

Should I play around with the UPDATETEXT then?

Brenda

If it weren't for you guys, where would I be?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-30 : 17:49:45

Yep.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-11-30 : 17:56:47

What do you think the 88, 1, and 'b' are in this line?

UPDATETEXT pub_info.pr_info @ptrval 88 1 'b'

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_6i2c.asp

Brenda

If it weren't for you guys, where would I be?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources