Hi Jeff,

First of all, Happy New Year! Out here it still 21:55... Hope you are having a great time with your family!

Based on the first file you created for me (treating the header), I made some changes (including the path) for using the real file (atached). When I run "EXEC dbo.GetFileType01 'C:\temp\sm\urso47Test02.csv'" (the real file now is "02"), I get the error message:

Acording with CTRL+G = 123, the error is somewhere out here:

Maybe one critical change I did should be replacing ":" by "." because I don't have the colon sign at the first header line:

I didn't make any changes in the formatfile. Here goes the statement changed:

CREATE PROCEDURE dbo.GetFileType01

/**********************************************************************************************************************

Purpose:

Give a full path/filename/extension, load the file according to the specs for "FileType01".

Usage Example:

EXEC dbo.GetFileType01 'C:\Temp\urso47Test01.txt'

Dependencies:

1. The related "BCP Format File" (RawData8000.fmt) must be located in "C:\Temp".

Obviously, you can change that.

2. The dbo.DelimitedSplitFunction must exist in the same database as this stored procedure.

It carries its own documentation

Programmers Notes:

1. Some minor error checking with the file is done. You can easily enhance the simple error checking to be full

validation, at some point.

Revision History

Rev 00 - 16 Dec 2012 - Jeff Moden - Initial creation/test

Ref - http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx

**********************************************************************************************************************/

--===== Declare the input parameters for this stored procedure

@pFullPath VARCHAR(500)

AS

--=====================================================================================================================

-- Presets

--=====================================================================================================================

--===== Setup the environment just to be sure

SET NOCOUNT ON;

SET DATEFORMAT MDY;

--===== Create the staging table for the raw data

CREATE TABLE #RawData

(

RowNum INT IDENTITY(1,1),

LineData VARCHAR(8000)

)

;

--===== Create some obviously named variables

DECLARE @ProcName VARCHAR(128),

@RowCount INT,

@SQL VARCHAR(8000)

;

--===== Preset variables with constants

SELECT @ProcName = OBJECT_NAME(@@PROCID),

@SQL = REPLACE('

BULK INSERT #RawData

FROM ''<<@pFullPath>>''

WITH (

BATCHSIZE = 2000000000,

CODEPAGE = ''RAW'',

DATAFILETYPE = ''char'',

--ERRORFILE = ''<<@pFullPath>>'', --Uncomment this line if you want to capture error rows

FIELDTERMINATOR = ''\t'',

FORMATFILE = ''C:\Temp\sm\RawData8000.fmt'',

MAXERRORS = 2000000000,

ROWTERMINATOR = '''',

TABLOCK

);'

,'<<@pFullPath>>',@pFullPath)

;

--===== Load the file for parsing using the format file

EXEC (@SQL)

;

--===== Parse and save the header information using a "CROSSTAB" to pivot the data

SELECT [#Lista Estat] =

MAX(

CASE

WHEN RowNum = 1 AND LineData LIKE '[#Lista Estat].%'

THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX('.',LineData)+1,8000)))

ELSE ''

END

),

[#Format Version] =

MAX(

CASE

WHEN RowNum = 2 AND LineData LIKE '[Format Version]:%'

THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))

ELSE ''

END

),

[#Data Obtida] =

CAST(MAX(

CASE

WHEN RowNum = 3 AND LineData LIKE '[#Data Obtida]:%'

THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))

ELSE ''

END

) AS DATETIME),

[#Nome do Disposit.] =

MAX(

CASE

WHEN RowNum = 4 AND LineData LIKE '[#Nome do Disposit.]:%'

THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))

ELSE ''

END

),

[#Endereço] =

MAX(

CASE

WHEN RowNum = 5 AND LineData LIKE '[#Endereço]:%'

THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))

ELSE ''

END

)

INTO #Header

FROM #RawData

WHERE RowNum BETWEEN 1 AND 5

;

--===== Check to make sure the header was loaded correctly.

-- Raise an error and exit if it wasn't

SELECT @RowCount = COUNT(*)

FROM #Header

WHERE [#Lista Estat]> '' --Is Not Blank or NULL

AND [#Format Version]> '' --Is Not Blank or NULL

AND [#Data Obtida]> '1900' --Wasn't an "empty" date (and assumes no dates before 1900-01-01)

AND [#Nome do Disposit.]> '' --Is Not Blank or NULL

AND [#Endereço]> '' --Is Not Blank or NULL

;

IF @RowCount <> 1

BEGIN

RAISERROR('ERROR:[%s]Incorrect header for file %s',18,1,@ProcName,@pFullPath);

RETURN;

END

;

--===== Check to make sure the detail header is at row 7.

-- Raise an error and exit if it isn't.

IF NOT EXISTS (SELECT 1 FROM #RawData WHERE RowNum = 7 AND LineData = '[#Utilizador],[Nome],[Tot. de Impressões],[P & B(Tot. de Impressões)],[Cor(Tot. de Impressões)],[P & B: Resultado(Tot. de Impressões)],[Cor: Resultado(Tot. de Impressões)],[Total de Preto e branco(Copidora/Servidor de Documentos)],[Preto e branco(Formato pequeno)(Copidora/Servidor de Documentos)],[Preto e branco(Formato grande)(Copidora/Servidor de Documentos)],[Total de Uma cor(Copidora/Servidor de Documentos)],[Cor Única(Formato pequeno)(Copidora/Servidor de Documentos)],[Cor Única(Formato grande)(Copidora/Servidor de Documentos)],[Total de Duas cores(Copidora/Servidor de Documentos)],[Duas cores(Formato pequeno)(Copidora/Servidor de Documentos)],[Duas cores(Formato grande)(Copidora/Servidor de Documentos)],[Total de Cor integral(Copidora/Servidor de Documentos)],[Cor Integral(Formato pequeno)(Copidora/Servidor de Documentos)],[Cor Integral(Formato grande)(Copidora/Servidor de Documentos)],[Total de Preto e branco(Impressora)],[Preto e branco(Formato pequeno)(Impressora)],[Preto e branco(Formato grande)(Impressora)],[Total de Uma cor(Impressora)],[Cor Única(Formato pequeno)(Impressora)],[Cor Única(Formato grande)(Impressora)],[Total de Duas cores(Impressora)],[Duas cores(Formato pequeno)(Impressora)],[Duas cores(Formato grande)(Impressora)],[Total a Cores(Impressora)],[Cor(Formato pequeno)(Impressora)],[Cor(Formato grande)(Impressora)],[Total de Scanner(Scanner)],[Total de Preto e branco(Scanner)],[Preto e branco(Formato pequeno)(Scanner)],[Preto e branco(Formato grande)(Scanner)],[Total a Cores(Scanner)],[Cor(Formato pequeno)(Scanner)],[Cor(Formato grande)(Scanner)],[Total de Preto e branco(Fax)],[Preto e branco(Formato pequeno)(Fax)],[Preto e branco(Formato grande)(Fax)],[Total a Cores(Fax)],[Cor(Formato pequeno)(Fax)],[Cor(Formato grande)(Fax)],[Transmissão(Fax)],[Custos(Fax)],[Volume utilizado(Limitação de volumes de utilização para impressão)],[Valor limite(Limitação de volumes de utilização para impressão)]

')

BEGIN

RAISERROR('ERROR:[%s] Misplaced detail-header for file %s',16,1,@ProcName, @pFullPath);

RETURN;

END

;

--===== Load and return the detail rows 114

SELECT [#Lista Estat] = MAX(hdr.[#Lista Estat]),

[#Format Version] = MAX(hdr.[#Format Version]),

[#Data Obtida] = MAX(hdr.[#Data Obtida]),

[#Nome do Disposit.] = MAX(hdr.[#Nome do Disposit.]),

[#Endereço] = MAX(hdr.[#Endereço]),

[#Utilizador] = MAX(CAST(CASE WHEN split.ItemNumber = 1THEN Item ELSE '' END AS VARCHAR(10))),

[Nome] = MAX(CAST(CASE WHEN split.ItemNumber = 2THEN Item ELSE '' END AS VARCHAR(10))),

[Tot. de Impressões] = MAX(CAST(CASE WHEN split.ItemNumber = 3THEN Item ELSE '' END AS INT)),

[P & B(Tot. de Impressões)] = MAX(CAST(CASE WHEN split.ItemNumber = 4THEN Item ELSE '' END AS INT)),

[Cor(Tot. de Impressões)] = MAX(CAST(CASE WHEN split.ItemNumber = 5THEN Item ELSE '' END AS INT)),

[P & B: Resultado(Tot. de Impressões)] = MAX(CAST(CASE WHEN split.ItemNumber = 6THEN Item ELSE '' END AS INT)),

[Cor: Resultado(Tot. de Impressões)] = MAX(CAST(CASE WHEN split.ItemNumber = 7THEN Item ELSE '' END AS INT)),

[Total de Preto e branco(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 8THEN Item ELSE '' END AS INT)),

[Preto e branco(Formato pequeno)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 9THEN Item ELSE '' END AS INT)),

[Preto e branco(Formato grande)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 10THEN Item ELSE '' END AS INT)),

[Total de Uma cor(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 11THEN Item ELSE '' END AS INT)),

[Cor Única(Formato pequeno)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 12THEN Item ELSE '' END AS INT)),

[Cor Única(Formato grande)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 13THEN Item ELSE '' END AS INT)),

[Total de Duas cores(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 14THEN Item ELSE '' END AS INT)),

[Duas cores(Formato pequeno)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 15THEN Item ELSE '' END AS INT)),

[Duas cores(Formato grande)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 16THEN Item ELSE '' END AS INT)),

[Total de Cor integral(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 17 THEN Item ELSE '' END AS INT)),

[Cor Integral(Formato pequeno)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 18THEN Item ELSE '' END AS INT)),

[Cor Integral(Formato grande)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 19THEN Item ELSE '' END AS INT)),

[Total de Preto e branco(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 20THEN Item ELSE '' END AS INT)),

[Preto e branco(Formato pequeno)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 21THEN Item ELSE '' END AS INT)),

[Preto e branco(Formato grande)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 22THEN Item ELSE '' END AS INT)),

[Total de Uma cor(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 23THEN Item ELSE '' END AS INT)),

[Cor Única(Formato pequeno)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 24THEN Item ELSE '' END AS INT)),

[Cor Única(Formato grande)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 25THEN Item ELSE '' END AS INT)),

[Total de Duas cores(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 26THEN Item ELSE '' END AS INT)),

[Duas cores(Formato pequeno)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 27THEN Item ELSE '' END AS INT)),

[Duas cores(Formato grande)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 28THEN Item ELSE '' END AS INT)),

[Total a Cores(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 29THEN Item ELSE '' END AS INT)),

[Cor(Formato pequeno)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 30THEN Item ELSE '' END AS INT)),

[Cor(Formato grande)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 31THEN Item ELSE '' END AS INT)),

[Total de Scanner(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 32THEN Item ELSE '' END AS INT)),

[Total de Preto e branco(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 33THEN Item ELSE '' END AS INT)),

[Preto e branco(Formato pequeno)(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 34THEN Item ELSE '' END AS INT)),

[Preto e branco(Formato grande)(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 35THEN Item ELSE '' END AS INT)),

[Total a Cores(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 36THEN Item ELSE '' END AS INT)),

[Cor(Formato pequeno)(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 37THEN Item ELSE '' END AS INT)),

[Cor(Formato grande)(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 38THEN Item ELSE '' END AS INT)),

[Total de Preto e branco(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 39THEN Item ELSE '' END AS INT)),

[Preto e branco(Formato pequeno)(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 40THEN Item ELSE '' END AS INT)),

[Preto e branco(Formato grande)(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 41THEN Item ELSE '' END AS INT)),

[Total a Cores(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 42THEN Item ELSE '' END AS INT)),

[Cor(Formato pequeno)(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 43THEN Item ELSE '' END AS INT)),

[Cor(Formato grande)(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 44THEN Item ELSE '' END AS INT)),

[Transmissão(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 45THEN Item ELSE '' END AS INT)),

[Custos(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 46THEN Item ELSE '' END AS INT)),

[Volume utilizado(Limitação de volumes de utilização para impressão)] = MAX(CAST(CASE WHEN split.ItemNumber = 47THEN Item ELSE '' END AS INT)),

[Valor limite(Limitação de volumes de utilização para impressão)] = MAX(CAST(CASE WHEN split.ItemNumber = 48THEN Item ELSE '' END AS INT))

FROM #RawData rd

CROSS JOIN #Header hdr --There's only one header so this works

CROSS APPLY ( --=== This parses the CSV in each row and removes the braces (every kid's dream ;-))

SELECT ItemNumber,

Item = REPLACE(REPLACE(Item,'[','') ,']','')

FROM dbo.DelimitedSplit8K(LineData,',')

) split

WHERE rd.RowNum > 7 --Data header is at row 7

AND LEN(LineData) > 1 --Gets rid of blank rows

GROUP BY rd.RowNum --Completes the pivot

;

GO

By the way for my level, this script is hard to understand, what level is this in the SQL world?

Thanks again!