Extraer datos a csv desde Sql Server

Una manera bastante efectiva y muy rápida para enviar una gran cantidad de datos entre distintos sistemas de base de datos es utilizar ficheros csv.

La definición que da la Wikipedia de los ficheros csv es: 

Los ficheros CSV (del inglés comma-separated values) son un tipo de documento en formato abierto sencillo para representar datos en forma de tabla, en las que las columnas se separan por comas (o punto y coma en donde la coma es el separador decimal: España, Francia, Italia...) y las filas por saltos de línea. Los campos que contengan una coma, un salto de línea o una comilla doble deben ser encerrados entre comillas dobles.

Es decir, es un formato de fichero de texto plano en el que cada línea del fichero representa un registro de nuestra tabla o resultado de la consulta a exportar. Dentro de esa fila, cada campo está separada por una coma o punto y coma. Siendo en España más usado el ';'.

Tanto la exportación como la importación de datos en este tipo de fichero es extraordinariamente rápida. Además en caso de tener que transferirse por red, al ser ficheros de texto, permiten una gran compresión.

La exportación a estos ficheros se puede hacer directamente desde T-Sql, por lo que es posible, por ejemplo, hacer programaciones de exportaciones con un job de Sql Server.

La sentencia necesaria para hacer esta exportación sería la siguiente:

Declare @vchSql as nvarchar(4000)Declare @bcp as nvarchar(4000)

Set @vchSql = 'Select * From TABLA' 
Set @bcp = 'bcp "'+@vchSql+'" queryout D:\Exp\exp.csv -T -t; -c -C' 

exec master..xp_cmdshell @bcp 

Donde:

• En la variable @vchSql se asigna la consulta a exportar
• D:\Exp\Exp.csv es el fichero resultante. La ruta especificada debe ser local o accesible desde el servidor de Sql Server, ya que el proceso se ejecuta en el servidor.
• En el parámetro -t se asigna el carácter de separación de campos, en este caso ;
• El parámetro -C son necesarios para que exporte el contenido respetando tildes, eñes, etc.

Esta exportación se realiza sin cabecera. Para incluir  en el fichero una línea de cabecera con el nombre de los campos habría que realizar las siguientes acciones:

1. Crear un fichero plantilla con la cabecera del fichero a generar.
   • Esta plantilla tendría que tener la línea de cabecera y una línea en blanco.
2. Exportar el fichero con la sintaxis explicada arriba
3. Copiar a la ruta final un fichero que sea la concatenación de la cabecera más el fichero exportado

Por tanto la sintaxis final sería:

Declare @vchSql as nvarchar(4000)Declare @bcp as nvarchar(4000)

Set @vchSql = 'Select * From TABLA' 
Set @bcp = 'bcp "'+@vchSql+'" queryout D:\Exp\exp.csv -T -t; -c' 

exec master..xp_cmdshell @bcp 

exec master..xp_cmdshell 'copy "D:\Exp\Template_header\template.csv" /B + "D:\Exp\exp.csv" /B \\serverDestino\carpetaDestino\export.csv'

NOTA: Es muy importante poner el parámetro /B en la copia/concatenación de los ficheros. Con este parámetro se fuerza a que la copia se haga en formato binario, de lo contrario lo copia en formato ASCII y por tanto incluye un salto de línea al final del fichero que hace que al importar el fichero generado con BULK INSERT falle.

PadLeft en Sql

Muy a menudo necesito utilizar una función similar a PadLeft en Sql, como esta función no está implementada en Sql Server, siempre tengo que buscar la "query" en internet.

Yo creo que la mejor opción es construir una función en Sql que pueda usar en cualquier momento.

Esta función Padleft lo que haría sería, rellenar con un carácter por la izquierda hasta completar el número de caracteres que le indiques por parámetro.

Aquí os dejo el código que he creado para construir esta función.
En este código, por optimización, distingo si la cadena original es numérica o alfanumérica.

ALTER FUNCTION PADLEFT
(
   @value as nvarchar(100),
   @charIzq as varchar(1),
   @intLenth as int
)
RETURNS nvarchar(100)
AS
BEGIN

   DECLARE @vchPadded as nvarchar(100)

   Set @vchPadded=@value

   If LEN(@value)< @intLenth
   Begin

      If ISNUMERIC(@value)=1
      Begin
         SELECT @vchPadded = 
           REPLACE(STR(@value, @intLenth), SPACE(1), @charIzq)
      End
      Else
      Begin
         Set @vchPadded=@value
         While (LEN(@vchPadded)<@intLenth)
         Begin
            Set @vchPadded=@charIzq + @vchPadded
         End

      End
   End

   RETURN @vchPadded

END 

Para usarla tendrías que escribir una consulta de este tipo:
  Select dbo.PADLEFT('63','0',5)
Su resultado sería: 00063