help on Bulk insert and xp_cmdshell rename

240 pts.
Tags:
Bulk Inserts
CSV
SQL INSERT
T-SQL
Hi, I have a csv with only one field with multiple rows, when i am using bulk insert to insert into sql server table, all the rows are inserted in to single column instead of multiple rows.I dont have a delimiter. csv file: (First row is blank) apple bat cat doll when i used the below command BULK INSERT tablename FROM 'c:table.csv' WITH (firstrow = 1 ,ROWTERMINATOR = '/n' ) GO when i select the table, the o/p is not correct applebatcatdoll I need to insert rows in to individual rows like apple bat cat.. Please let me know how to do this. Once, i copy the data in to the table, i need to rename the file adding the time to the csv. Please let me know how to write the Xp_cmdshell rename. Thanks for any help
ASKED: March 5, 2009  6:49 PM
UPDATED: March 5, 2009  8:05 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

As for the insert , the problem seems to be that you have defined an incorrect row terminator. You should be using ‘\n’ instead of ‘/n’.

To rename the file, try something like this:

<pre>declare @command varchar(1000)
set @command = ‘rename c:\table.csv table_old.csv’
exec master.dbo.xp_cmdshell @command, NO_OUTPUT
go
</pre>

With SQL Server 2005 and 2008 the use of xp_cmdshell is turned off by default.

<a href=”http://msdn.microsoft.com/en-us/library/ms190693.aspx”>Enabling xp_cmdshell Option</a>

——————————

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following