Import Export using csv file

Question

Post reply

Import Export using csv file

am-244616

SSCarpal Tunnel

Points: 4936
More actions
February 8, 2007 at 1:36 pm

#69589

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

In the title you said import/export using csv file but in the content you use a text file.

Assume you have an input text file with fname, Lname and you want to export the Dname to another text file, is this what you want?

You can put all in an DTS package.

A connection of input text file A.

A connection of output text file B.

SQL Server connection SQL.

Step 1 - input text file A to SQL table NAME using transfer data task.

Step 2 - update the table NAME to concatenate into DNAME using execute SQL task.

Step 3 - using transfer data task again from SQL Server to text file B.

DONE !!

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 2

Loner,

Thank you for your response,

I want to be able to do this in sql server 2000, and hence I posted under forum too. I tried to use BCP utility but I fail during the first step itself

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

Loner SSC-Insane Points: 21279 More actions · Answer 3

From the error message, it seemed that your data file had a problem. Did you check your data file and make sure it does not have EOF mark somewhere?

What happened when you used 'bcp', it should work too ?

DECLARE @SQL VARCHAR(8000)

SET @SQL = ''

SET @SQL = 'bcp Colonial..Collin_images IN "c:\source.txt" -c -t";" -T'

EXEC master..xp_cmdshell @SQL

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 4

when I the above bcp code I get the following error, I am not sure if my server settings are wrong

any ideas??

SQLState = 08001, NativeError = 17

Error = [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

SQLState = 01000, NativeError = 2

Warning = [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()).

NULL

Patrick Folan Mr or Mrs. 500 Points: 512 More actions · Answer 5

The above error message caused by problem with your connection string....does not recognize server name.....

Why dont you create a DTS Job in SQL Enterpise Manager to load the data and then set up job to run it daily using xp_cmdshell e.g. ......

Execute master..xp_cmdshell 'dtsrun -SYOUR_SERVER -UYOUR_LOGIN -PYOUR_PASSWORD -NYOUR_DTS_JOB '