BULK INSERT - can you use a space as field terminator???

[pandatime]

Hi,

That's basically the question. I have a file that is delimited by spaces and tabs. The tabs is easy, char(9). But how do I specify in the format file that I want it break on a space?

I tried:
9.0
6
1 SQLCHAR 0 255 "' '" 3 SYSTEM_DATE ""
etc...

but it doesn't like:

The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

According a website I found, space isn't listed as a field terminator, so I wonder if this can be done?

Thanks

 

[pandatime]

Yet another "nevermind".

It's " "... now my question is, is there a way to specify a DOUBLE-SPACE as the delimiter?

As in:

2010-01-01 00:00:00 Joe Smith

I want to break on 2010-01-01 00:00:00, but this is on the SECOND space in the line. Any way to do this??

Thank you

 

[SQLSister]

I would kick the file back to the provider. There is no good way to handle a space as a delimiter unless you use a text qualifier (usually ") as data also has spaces. This woudl be an unacceptable file in my office and I wouldn't waste my time on it. Up to the provider to fix junk like this.

"NOTHING is more important in a database than integrity." ESquared