document SqlBulk - CLR_Bulk_Exp

• CLR_Bulk_Exp_1

This sp allow to manage with flexibility the import of a text file.
Main feauters are:

• manage the date format
• decimal separator
• manage boolean values
• manage null values
• manage encoding types
• cast values as column type
• use the windows impersonification to manage domain grant
• insert free text that will try to executed as query and if ok, insert the result before or after header

Example:

declare @r varchar(max),
        @i int

exec @i=dbo.CLR_Bulk_Exp 
                  'test',--database                    
                  'path',--path
                  'test.txt',
                  0,-- 1->UTF8, 2-> UTF8 BOM, 3-> UTF7, 4-> UTF32, altro default 1
                  'query',--the query i want to export
                  '|',--export delimitator
                  0,--if i want to add at the end of any row the delimitator
                  '.',--decimal char
                  'c1,c2',--the column i need to manage with a particular conversion(only date available)
                  'yyyyMMdd,ddMMyy',--the format of the column before specified
                  1,--export the header
                  'select getdate()',--free text that could be possible add (could be possible a query!!)
                  0,--where i want to add the text, after or before header?
                  @r output

print @i
print @r

Parameters:

Sp returns an int value that indicate the number of exported rows or if less 0 means there was an error.

 db – String

It s the db where there is the table where the file will be imported.
Sp could be in a different db.

 path – String

The path of the file.

 file – String

File to import.

 encoding – int

• 1->UTF8
• 2-> UTF8 BOM
• 3-> UTF7
• 4-> UTF32
• Other, default 1

 query – String

it s the query to execute and its result will be exported on file

 delimitator – char

It s the delimitator of text columns.

 Delimitato_alstr – bool

• True:
  insert after last column the delimitator;
• False:
  do nothing.

 decimale – char

It s the decimal separator.
If not defined (‘’) will use the default of system.

 trace – String – (comma list ‘c1,c2,c3,...’)

are the columns that need some conversation, like date format.
It’s not necessary the trace order is the same of query columns.
If not defined (‘’) will as query definition.

 options – String – (comma list ‘yyyyMMdd,ddMMyy,...’)

It s the date format, one for each column defined in the trace.
If the column it s “date/datetime/smalldatetime” sp try to cast it with the defined format.
If not defined (‘’) will use the default system settings.

 Header – bool

• True:
  insert columns name as header;
• False:
  do nothing.

 text – String

Sp try to execute this free text as query.
If returns a scalar value this will be inserted after or before the header (next parameter @testo_after_int) else will insert the text as is.

 Text_After_insert – bool

• True:
  text after header;
• False:
  text before header.

 error – String (ouput)

Returns the error.