Import Export Job using CSV 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)

Import Export Job using CSV file

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-02-08 : 14:59:36

Guys,

I have txt file with following values

fname lname
______________
Tom Gray
Tim Junk

I want to be able set a job to import this into NAME table 10pm every night

NAME table has the following columns

FNAME VARCHAR(20)
LNAME VARCHAR(20)
DNAME VARCHAR(50)

after I load the data I concatenate FNAME and LNAME into DNAME. I want to setup a job then to export out DNAME column

is there any way this can be acconplished using dts job

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-08 : 15:05:19

Yes - what problem are you finding doing it.
Would probably be simpler using bcp - and do you need to hold DNAME?

==========================================
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.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2007-02-08 : 15:12:01

I am trying to cross the first hurdle which is trying to load the data as part of daily job

when I try to execute this I get the below message even though the txt file seem to be okay.

BULK INSERT Colonial..Collin_images FROM 'c:\source.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

any suggestions?

Thanks

ekb18c
Starting Member

18 Posts

Posted - 2007-02-14 : 23:44:24

What does the txt file look like?

It is:

fname,lname
tom,gray
jim,junk

also when you export you can concatenate fname and lname rather than create a derived column (dname).

Not sure if it makes a difference but.. DATAFILETYPE = 'char', when your table columns is varchar?

Also how many rows of data are you importing? Rather than using bcp, why not just a simple tranformation.

www.sqlnerd.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources