BCP Field Terminators

[cogdej]

I have a ton of files that are space delimited and need to import them using BCP from the command line.

bcp tablename in c:\filelocation -c -t" " -r \n -Sserver -Uuser -Ppassword

I have tried escaping it and still have no answers.

Any help is appreciated.

Thanks

[rmiao]

Double chech it's space or tab.

[cogdej]

100% sure its space files. I tried a format file and that didn't work either.

[rmiao]

I'll try load a file in dts to see what kind of delimiter it picks. By the way, how are those files generated?

[cogdej]

They are iis log files. I was told there was a way to just put in the ascii value but I have not found any examples on any site.

[nosepicker]

Try just bcp'ing in a small sample (like the first 10 lines) of the file and see if that works. If not, post those 10 lines and the schema of your target table and we can work on it.

[cogdej]

bombs on the first line

error:
Bulk insert data conversion error (type mismatch) for row 1, column 1 (date).

However I know its not the table because I am able to use the query analyzer to run a standard bulk insert command and it works.

I have
Bulk Insert xxxx from location
WITH
( DATAFILETYPE = 'char',
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)

Has worked for large sample size over 10000 rows but when I used BCP command line it will not insert the first row. 4 hours later and 4 different fmt files later it still will not run.

[cogdej]

I now am sure the bcp.fmt file is 100% working as I was able to use it in the query analyzer as well.

When I use the command line it outputs this error
SQLState = 37000, NativeError = 170
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.

Does anyone know of a difference in the way SQL server interprets the information it is reading in such as the date?

[cogdej]

I discovered, through trial and error, that a BCP format file can not have a '-', '(' or ')' in the table names. This caused the format file to exit. Thank you for all the help.

[rmiao]

Try following to see if works:

bcp [tablename] in c:\filelocation -c -t" " -r \n -Sserver -Uuser -Ppassword

[cogdej]

I got it to work with the bcp format file however I ran into a well documented error where you get a date format error because of the regional settings on the server. Thank you for the help.