January 1, 2013 at 9:07 am
Another try:
DROP PROCEDURE dbo.GetFileType01
GO
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 #ListaEstatUtilizador =
MAX(
CASE
WHEN RowNum = 1 AND LineData LIKE '#Lista Estat. Utilizador%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
ELSE ''
END
),
#FormatVersion =
MAX(
CASE
WHEN RowNum = 2 AND LineData LIKE 'Format Version:%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
ELSE ''
END
),
#DataObtida =
CAST(MAX(
CASE
WHEN RowNum = 3 AND LineData LIKE '#Data Obtida:%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
ELSE ''
END
) AS DATETIME),
#NomedoDisposit =
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 #ListaEstatUtilizador> '' --Is Not Blank or NULL
AND #FormatVersion> '' --Is Not Blank or NULL
AND #DataObtida> '1900' --Wasn't an "empty" date (and assumes no dates before 1900-01-01)
AND #NomedoDisposit> '' --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',16,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
SELECT #ListaEstatUtilizador= MAX(hdr.#ListaEstatUtilizador),
#FormatVersion= MAX(hdr.#FormatVersion),
#DataObtida= MAX(hdr.#DataObtida),
#NomedoDisposit= MAX(hdr.#NomedoDisposit),
#Endereço= MAX(hdr.#Endereço),
#Utilizador= MAX(CAST(CASE WHEN split.ItemNumber = 1THEN Item ELSE '' END AS VARCHAR(100))),
Nome= MAX(CAST(CASE WHEN split.ItemNumber = 2THEN Item ELSE '' END AS VARCHAR(100))),
[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 = 17THEN 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
Error stil:
Msg 50000, Level 16, State 1, Procedure GetFileType01, Line 124
ERROR:[GetFileType01]Incorrect header for file C:\Temp\sm\urso47Test02.csv
January 2, 2013 at 12:07 am
urso47 (12/31/2012)
As we don't have square brackets in the file uploaded, I tried removing them all, but it didn't work.
You certainly do. The last file you showed me had square brackets in the first two columns of the data.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2013 at 12:15 am
Alright... what's the name of the file you used that last stored procedure on?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 2, 2013 at 4:36 am
Hi Jeff,
The file I sent last time is "urso47Test02.csv" and I am running EXEC dbo.GetFileType01 'c:\temp\sm\urso47Test02.csv'.
The first 2 columns has brackets on its contents, I thougth you were talking about the collumn name. Firts 2 Collumns name = no bracktes, first 2 collumns contents = has brackets.
I thougth
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
would remove the brackets of all rows of all collumns.
Thanks!
January 2, 2013 at 6:05 pm
Any new instruction? Thanks!
January 3, 2013 at 6:18 pm
Can anyone help me on this?
January 4, 2013 at 10:19 am
SOrry... I've been isck.
Go back to your last error and science it out. The problem will be apparent. I could probably spoon feed ya on this stuff but you're the one that's going to have to support it.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2013 at 12:27 pm
It's helpless, I have been trying to find the problem several times for the last 7 days but I can't find it because I have my limitation, if I haven't, I wouldn't be here begging for help. If you know it, I thougth you could just tell me clearly then. I thought the purpose of this forum was help us solve our doubts straight away once you already well know how to do it.
January 5, 2013 at 4:50 pm
Hi Jeff,
Hope you are recovered now. I got done last lesson you gave me. This is the working code:
DROP PROCEDURE dbo.GetFileTest
GO
CREATE PROCEDURE dbo.GetFileTest
/**********************************************************************************************************************
Purpose:
Give a full path/filename/extension, load the file according to the specs for "FileType01".
Usage Example:
EXEC dbo.GetFileTest 'C:\Temp\test\urso47Test02.csv'
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\test\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
),
#FormatVersion =
MAX(
CASE
WHEN RowNum = 2 AND LineData LIKE '#Format Version:%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
ELSE ''
END
),
#DataObtida =
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 #FormatVersion > '' --Is Not Blank or NULL
AND #DataObtida > '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',16,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
SELECT [#Lista Estat]= MAX(hdr.[#Lista Estat]),
#FormatVersion= MAX(hdr.#FormatVersion),
#DataObtida= MAX(hdr.#DataObtida),
[#Nome do Disposit.]= MAX(hdr.[#Nome do Disposit.]),
#Endereço= MAX(hdr.#Endereço),
#Utilizador= MAX(CAST(CASE WHEN split.ItemNumber = 1 THEN Item ELSE '' END AS VARCHAR(10))),
Nome= MAX(CAST(CASE WHEN split.ItemNumber = 2 THEN Item ELSE '' END AS VARCHAR(10))),
[Tot. de Impressões]= MAX(CAST(CASE WHEN split.ItemNumber = 3 THEN Item ELSE '' END AS INT)),
[P & B(Tot. de Impressões)]= MAX(CAST(CASE WHEN split.ItemNumber = 4 THEN Item ELSE '' END AS INT)),
[P & B(Tot. de Impressões)]= MAX(CAST(CASE WHEN split.ItemNumber = 4 THEN Item ELSE '' END AS INT)),
[Cor(Tot. de Impressões)]= MAX(CAST(CASE WHEN split.ItemNumber = 5 THEN Item ELSE '' END AS INT)),
[P & B: Resultado(Tot. de Impressões)]= MAX(CAST(CASE WHEN split.ItemNumber = 6 THEN Item ELSE '' END AS INT)),
[Cor: Resultado(Tot. de Impressões)]= MAX(CAST(CASE WHEN split.ItemNumber = 7 THEN Item ELSE '' END AS INT)),
[Total de Preto e branco(Copidora/Servidor de Documentos)]= MAX(CAST(CASE WHEN split.ItemNumber = 8 THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato pequeno)(Copidora/Servidor de Documentos)]= MAX(CAST(CASE WHEN split.ItemNumber = 9 THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato grande)(Copidora/Servidor de Documentos)]= MAX(CAST(CASE WHEN split.ItemNumber = 10 THEN Item ELSE '' END AS INT)),
[Total de Uma cor(Copidora/Servidor de Documentos)]= MAX(CAST(CASE WHEN split.ItemNumber = 11 THEN Item ELSE '' END AS INT)),
[Cor Única(Formato pequeno)(Copidora/Servidor de Documentos)]= MAX(CAST(CASE WHEN split.ItemNumber = 12 THEN Item ELSE '' END AS INT)),
[Cor Única(Formato grande)(Copidora/Servidor de Documentos)]= MAX(CAST(CASE WHEN split.ItemNumber = 13 THEN Item ELSE '' END AS INT)),
[Total de Duas cores(Copidora/Servidor de Documentos)]= MAX(CAST(CASE WHEN split.ItemNumber = 14 THEN Item ELSE '' END AS INT)),
[Duas cores(Formato pequeno)(Copidora/Servidor de Documentos)]= MAX(CAST(CASE WHEN split.ItemNumber = 15 THEN Item ELSE '' END AS INT)),
[Duas cores(Formato grande)(Copidora/Servidor de Documentos)]= MAX(CAST(CASE WHEN split.ItemNumber = 16 THEN 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 = 18 THEN Item ELSE '' END AS INT)),
[Cor Integral(Formato grande)(Copidora/Servidor de Documentos)]= MAX(CAST(CASE WHEN split.ItemNumber = 19 THEN Item ELSE '' END AS INT)),
[Total de Preto e branco(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 20 THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato pequeno)(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 21 THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato grande)(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 22 THEN Item ELSE '' END AS INT)),
[Total de Uma cor(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 23 THEN Item ELSE '' END AS INT)),
[Cor Única(Formato pequeno)(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 24 THEN Item ELSE '' END AS INT)),
[Cor Única(Formato grande)(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 25 THEN Item ELSE '' END AS INT)),
[Total de Duas cores(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 26 THEN Item ELSE '' END AS INT)),
[Duas cores(Formato pequeno)(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 27 THEN Item ELSE '' END AS INT)),
[Duas cores(Formato grande)(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 28 THEN Item ELSE '' END AS INT)),
[Total a Cores(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 29 THEN Item ELSE '' END AS INT)),
[Cor(Formato pequeno)(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 30 THEN Item ELSE '' END AS INT)),
[Cor(Formato grande)(Impressora)]= MAX(CAST(CASE WHEN split.ItemNumber = 31 THEN Item ELSE '' END AS INT)),
[Total de Scanner(Scanner)]= MAX(CAST(CASE WHEN split.ItemNumber = 32 THEN Item ELSE '' END AS INT)),
[Total de Preto e branco(Scanner)]= MAX(CAST(CASE WHEN split.ItemNumber = 33 THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato pequeno)(Scanner)]= MAX(CAST(CASE WHEN split.ItemNumber = 34 THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato grande)(Scanner)]= MAX(CAST(CASE WHEN split.ItemNumber = 35 THEN Item ELSE '' END AS INT)),
[Total a Cores(Scanner)]= MAX(CAST(CASE WHEN split.ItemNumber = 36 THEN Item ELSE '' END AS INT)),
[Cor(Formato pequeno)(Scanner)]= MAX(CAST(CASE WHEN split.ItemNumber = 37 THEN Item ELSE '' END AS INT)),
[Cor(Formato grande)(Scanner)]= MAX(CAST(CASE WHEN split.ItemNumber = 38 THEN Item ELSE '' END AS INT)),
[Total de Preto e branco(Fax)]= MAX(CAST(CASE WHEN split.ItemNumber = 39 THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato pequeno)(Fax)]= MAX(CAST(CASE WHEN split.ItemNumber = 40 THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato grande)(Fax)]= MAX(CAST(CASE WHEN split.ItemNumber = 41 THEN Item ELSE '' END AS INT)),
[Total a Cores(Fax)]= MAX(CAST(CASE WHEN split.ItemNumber = 42 THEN Item ELSE '' END AS INT)),
[Cor(Formato pequeno)(Fax)]= MAX(CAST(CASE WHEN split.ItemNumber = 43 THEN Item ELSE '' END AS INT)),
[Cor(Formato grande)(Fax)]= MAX(CAST(CASE WHEN split.ItemNumber = 44 THEN Item ELSE '' END AS INT)),
[Transmissão(Fax)]= MAX(CAST(CASE WHEN split.ItemNumber = 45 THEN Item ELSE '' END AS INT)),
[Custos(Fax)]= MAX(CAST(CASE WHEN split.ItemNumber = 46 THEN Item ELSE '' END AS INT)),
[Volume utilizado(Limitação de volumes de utilização para impressão)]= MAX(CAST(CASE WHEN split.ItemNumber = 47 THEN Item ELSE '' END AS INT)),
[Valor limite(Limitação de volumes de utilização para impressão)]= MAX(CAST(CASE WHEN split.ItemNumber = 48 THEN 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
In my machine this syntax looks good and in a perfect format but I can't format it here in this post 🙁 ...
Back to the second last lesson again, the equit files stuff. Thanks!
January 6, 2013 at 9:17 am
I'm not sure what that means. Does that mean that you went back, solved your problems, and the code you posted is working fine?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2013 at 5:22 pm
Yes Jeff, for one of the questions (header related).
I am getting used to manipulate and adapt the code for treating the header that you provided, I am able now to run your SP and insert it into a final table, with ID field and stuff ;).
For the bulk insert multiple files, I still need your help on the next steps in order to insert them all into a final table. At this stage, I am able to capture all the file paths and run the SP you created and my last question about it was the Post #1401396 of 2/31/2012 3:00:43 PM:
Hi Jeff,
I did it all over again several times and finally it worked.
1- I ran the "CREATE PROCEDURE dbo.ImportEqFiles" > "Command(s) completed successfully.".
2- I ran the EXEC dbo.ImportEqFiles 'C:\Temp\eq_files', 'Eq_sample_%.csv' > "Command(s) completed successfully.".
3- I am able to see the LogFile table 2 rows.
How do I get output from the staging table to the final table with the cleaned raw data?
What about if I create another DB for this purpose and use it instead the TempDB used this time, would I be able to see the #eq_Staging and #Directory tables and its contents? If it is possible, what is the pros and cons for doing this way? Would it use more disk space than the TempDB??
Thanks again!
Thanks.
January 8, 2013 at 4:31 am
Any new instruction on the next steps? Thanks!
January 9, 2013 at 1:22 pm
Hi Jeff, can you guide me on the next steps? Thanks!
January 10, 2013 at 7:29 pm
Anybody able to help me on this?
January 10, 2013 at 7:39 pm
urso47 (1/6/2013)
Yes Jeff, for one of the questions (header related).I am getting used to manipulate and adapt the code for treating the header that you provided, I am able now to run your SP and insert it into a final table, with ID field and stuff ;).
For the bulk insert multiple files, I still need your help on the next steps in order to insert them all into a final table. At this stage, I am able to capture all the file paths and run the SP you created and my last question about it was the Post #1401396 of 2/31/2012 3:00:43 PM:
Hi Jeff,
I did it all over again several times and finally it worked.
1- I ran the "CREATE PROCEDURE dbo.ImportEqFiles" > "Command(s) completed successfully.".
2- I ran the EXEC dbo.ImportEqFiles 'C:\Temp\eq_files', 'Eq_sample_%.csv' > "Command(s) completed successfully.".
3- I am able to see the LogFile table 2 rows.
How do I get output from the staging table to the final table with the cleaned raw data?
What about if I create another DB for this purpose and use it instead the TempDB used this time, would I be able to see the #eq_Staging and #Directory tables and its contents? If it is possible, what is the pros and cons for doing this way? Would it use more disk space than the TempDB??
Thanks again!
Thanks.
So, next step. You know how to create the #Directory table. You know how to import each file type. Now just make a loop to read through the #Directory table and decide which stored procedure (one for each file type) to run for each file that shows up in the #Directory table.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply