FTP csv file from UNIX server - problem

[MrPeds]

Hi,

I am FTP'ing a CSV file from a UNIX server as I want to use this as the source data for a SQL Server table.

I can successfully FTP the file to my machine, but when I go to insert the data into the table I can never do so as the delimiters are not recognised. The column delimiter is a comma "," and the row delimiter is {CR}{LF}.

When i look at the csv file in Notepad the file contains what appear to be Unix end if line characters and I think this is what is stopping the file going into the table.

When I FTP the file from the UNIX server using a windows batch file I dont get any problems, I only get the problem using the SQL Server DTS FTP step. Is there any way of enabling the DTS FTP Task to do the same as the batch file to get it to work without me having to use a BAtch file executed via a Execute Process Task?

Thanks,

MrPeds

 

[nigelrivett]

I do ftp's from t-sql using the os command
see

http://www.nigelrivett.net/s_ftp_GetDir.html

http://www.nigelrivett.net/s_ftp_PutFile.html

That's for a put but get is similar.

As for the delimitters - you can specify any character for delimitters by using char().
Could be that you need to change they way it is ftp'd (binary/text) as it maybe converting.
You can import the whole file into a single column by specifying something like /0 as delimitters on bulk insert then look at the data if you don't have a hex editor to get the values.


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