How to bulk load from a file that has spaces in its name ?

• SQL Guy 1

  SSCoach

  Points: 15981

  More actions

  August 3, 2010 at 2:30 pm

  #236141

  We need to bulk insert from a file that looks like this:

  BULK INSERT schema_name.table_name

  from '\\server_name\share_name$\folder with spaces\file_name.txt'

  I get an error:

  Cannot bulk load because the file "\\server_name\share_name$\folder with spaces\file_name.txt" could not be opened.

  Same thing happens if I surround all string with double-quotes "

  BULK INSERT schema_name.table_name

  from '"\\server_name\share_name$\folder with spaces\file_name.txt"'

  The message is like this:

  Cannot bulk load because the file ""\\server_name\share_name$\folder with spaces\file_name.txt"" could not be opened.

  However, if I do type:

  xp_cmdshell 'type "\\server_name\share_name$\folder with spaces\file_name.txt"'

  it lists all contents of the file.

  Is there any other way to manipulate with quotes, or maybe to use some different technique ?

  Thanks

• Michael Valentine Jones

  SSC Guru

  Points: 64818

  More actions

  August 3, 2010 at 2:37 pm

  #1201794

  You can use the 8.3 compatible name for the directory, PROGRA~1 for Program Files in the example below:

  dir \\MYSERVER\c$\pr* /x

  Volume in drive \\MYSERVER\c$ is MYVOLUME

  Volume Serial Number is 59C5-DC5B

  Directory of \\MYSERVER\c$

  07/07/2010 04:24 PM <DIR> PROGRA~1 Program Files

  0 File(s) 0 bytes

  1 Dir(s) 147,934,945,792 bytes free