Yes... most likely, you will have to use a full UNC path for the \\formats path including the machine name.

--Jeff Moden

Oh yeah... on the collation problem... if you just use "" for the collation in the format file, it will allow the default to prevail... also, read about the different parameters for BULK INSERT... lot's of good stuff there...

--Jeff Moden

Thank jeff, much appreciated!!!

But tell me, i was advised to use bcp, how would you use it in SQL? Or should I say how would i invoke an external file from SQL, since bcp isn't a built-in function of SQL Server 2000.

And how do I solve my new problem, where it tells me that the src data column type isn't the same as the destination column type therfore i get a type conversion error!

My src file is a coma delimted csv file, this error occurs only with a certain set of tables and yet I have a format file and the amount of db table columns match the amount of columns in the src file.

What can I do or should try?

Thanks again

Ciao

A man's dreams are an index to his greatness!

bcp can be executed from sql if you use the xp command shell functionality.

BCP will be a little bit slower than Bulk Insert only because BCP is a more robust program than Bulk Insert. Rob Prell is correct, though... Call BCP through the xp_CmdShell extended stored procedure (it WILL require that you have SA privs).

EXEC Master.dbo.xp_CmdShell 'BCP yada-yada...'



--Jeff Moden

Again I repeat myself, allowing the execution of xp_cmdshell on a sql server that web servers can connect to is a significant security risk.

Yep... that's why I keep telling 'em to use BULK INSERT... why did you tell them to use xp_CmdShell to begin with? :D

Better yet, that bulk stuff should be done on a server separate from the web server no matter how you're handling the bulk. If you have to use the same server for both, at least setup some good proxies to protect the system...

--Jeff Moden

I am trying to suggest not doing this through a web triggered process, either bulk insert or xp_command shell-BCP. I thought this was the origional question. I don't like Bulk Insert because I don't find it to be as reliable as BCP. I would suggest something not triggered by web based PHP code to do the process they want for security reasons. Perhaps a seperate executable or some other process. Both are risky on web apps for security reasons. Using file system access with SQL opens up loads of security issues.

Hey guys....No need to get all militant with each other :D

I have a problem now, I'm trying to import data from a CSV File which was saved from Excel, since it was originally an XLS document. But when I try to import the data into my tables, whether i use BCP or BULK INSERT or even DTS I get conversion errors, the src data isn't the same type as my destination column type>


What do I do? I'm getting irritated since I've check that there are now chars where there shouldn't be.

What am I doing wrong?

Please help.

Thanks

Ciao

A man's dreams are an index to his greatness!

One way would be to import the Excel file directly with DTS... the other way would be to import the data to a staging table where you could make the translations yourself.

I've not had to do it but rumor has it that SQL Server has some pretty handy "FOR XML" hooks... might not have to go through Excel at all... I just don't know enough about it to write any code for you... might want to dig into Books Online on the subject of "XML"...

--Jeff Moden