InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 00:00:15 GMT20http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxI guess I do't understand why you're adding and IDENTITY column to a permanent table on the fly after the table had been populated. Can't help you with a thing there because I wouldn't do such a thing.As to the error, that's likely coming from the bulk insert. You need to find out if that's actually true because I can't tell from here. For example, when you run the proc, does #eq_staging actually exist?I'm not sure what else you changed but you said you had this working. I can't troubleshoot this from afar. Peel one potato at a time and go through it one step at a time until each step works consistently and as expected.Sun, 13 Jan 2013 14:01:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxI made the following changes in order to get the information into tables:[sup]CREATE PROCEDURE dbo.ImportEqFiles /********************************************************************************** Purpose: Given a valid file path and the "LIKE" filter for file names, import the files from the given file path and log the processing of each file in the FileLog table (which is created by this proc if it doesn't exist). Files that have already been imported will not be imported again. Usage Examples: EXEC dbo.ImportEqFiles @pFilePath, @pFilter EXEC dbo.ImportEqFiles 'C:\Temp\eq_files', 'Eq_sample_%.csv'**********************************************************************************/--===== Define the parameters for this proc @pFilePath VARCHAR(450), @pFileFilter VARCHAR(450) AS--===================================================================================-- Presets--===================================================================================--===== Setup the environment SET NOCOUNT ON; --Prevents false error returns to the calling procedure or GUI SET XACT_ABORT ON; --Short circuits any explicit transactions (if they exist) when an error occurs.--===== Conditionally drop the all Temp Tables to make reruns in SSMS easier IF OBJECT_ID('tempdb..#eq_Staging','U') IS NOT NULL DROP TABLE #eq_Staging; IF OBJECT_ID('tempdb..#Directory' ,'U') IS NOT NULL DROP TABLE #Directory;--===== Create the staging table using all VARCHAR datatypes because -- of all the double quotes in the file(s) we're loading. CREATE TABLE #eq_Staging( cGroupID VARCHAR(200), cGroupIDBillable VARCHAR(200), cGroupName VARCHAR(200), cGroupDescription VARCHAR(200), cRowID VARCHAR(200), cRowName VARCHAR(200), cRowDescription VARCHAR(200), cTransactDate VARCHAR(200), cTransactType VARCHAR(200), cChargeAccountID VARCHAR(200), cChargeAccountType VARCHAR(200), cUserWhoPrinted VARCHAR(200), cDocumentName NVARCHAR(200), cUnits VARCHAR(200), cDocumentID VARCHAR(200), cDeviceIP VARCHAR(200), cDevModel VARCHAR(200), cDevManuf VARCHAR(200), cDuration VARCHAR(200), cDestination VARCHAR(200), cFullName VARCHAR(200), cPrimaryPIN VARCHAR(200), cWorkstation VARCHAR(200), cAmount VARCHAR(200), JobProperties VARCHAR(200), cAltCost VARCHAR(200), cDepartment VARCHAR(200), cBillable VARCHAR(200), cAlternatePin VARCHAR(200));--===== Create the table to hold the directory information we'll need. CREATE TABLE #Directory( ObjectName VARCHAR(450), Depth TINYINT, IsFile TINYINT);--===== If the file processing log table doesn't already exist, create it now. IF OBJECT_ID('dbo.FileLog','U') IS NULL CREATE TABLE dbo.FileLog( FileLogID INT IDENTITY(1,1), FilePath VARCHAR(450) NOT NULL, FileName VARCHAR(450) NOT NULL, StatusDate DATETIME NOT NULL DEFAULT GETDATE(), FileStatus VARCHAR(20) NOT NULL, ProcessedBy VARCHAR(128) NOT NULL DEFAULT ORIGINAL_LOGIN(), CONSTRAINT PK_FileLog PRIMARY KEY CLUSTERED (FileLogID), CONSTRAINT AK_FileLog UNIQUE (FilePath,FileName));--===== Declare local variablesDECLARE @FullFileName VARCHAR(500), @SQL VARCHAR(MAX);--===== Ensure the file path ends with a backslash and build the quoted file name SELECT @pFilePath = CASE WHEN RIGHT(@pFilePath,1) = '\' THEN @pFilePath ELSE @pFilePath + '\' END;--===================================================================================-- Load all of the filenames from the given path into the log--===================================================================================--===== Load all the file names using xp_dirtree which does NOT require xp_CmdShell to be enabled. INSERT INTO #Directory (ObjectName, Depth, IsFile) EXEC xp_dirtree @pFilePath,1,1;--===== Add the files that aren't already in the log as "Import In Process" files that need to be processed. INSERT INTO dbo.FileLog (FilePath, FileName, StatusDate, FileStatus) SELECT FilePath = @pFilePath, ObjectName, StatusDate = GETDATE(), FileStatus = 'Import inprocess' FROM #Directory WHERE IsFile = 1 AND ObjectName LIKE @pFileFilter EXCEPT SELECT FilePath, FileName, StatusDate = GETDATE(), FileStatus = 'Import inprocess' FROM dbo.FileLog;--===================================================================================-- Import the "Import inprocess" files to the staging table--=================================================================================== SELECT @SQL = ISNULL(@SQL,'') + ' BULK INSERT #eq_Staging FROM ' + QUOTENAME(@pFilePath+FileName,'''') + ' WITH ( FIELDTERMINATOR = '','', FIRSTROW = 2, ROWTERMINATOR = '''', CODEPAGE = ''ACP'', DATAFILETYPE = ''widechar'' ); UPDATE dbo.FileLog SET StatusDate = GETDATE(), FileStatus = ''Staged'' WHERE FilePath = ' + QUOTENAME(@pFilePath,'''') + ' AND FileName = ' + QUOTENAME(FileName ,'''') + '' FROM dbo.FileLog WHERE FilePath = @pFilePath AND FileName LIKE @pFileFilter AND FileStatus = 'Import inprocess' ; [quote]--== create and load information from #eq_staging tableSELECT * INTO EQUIT FROM #eq_Staging ;--== add ID field on the equit table ALTER TABLE EQUIT ADD equit_id int IDENTITY(1,1) NOT NULL ;[/quote] --===== Display the commands that we're goig to run. -- (Note that will be truncated for display at 8K but will be the full command in reality) PRINT @SQL--===== Import and log all the files identified by this run EXEC (@SQL)[/sup]Result:[quote]Msg 7301, Level 16, State 2, Line 2Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".[/quote]I don't see anything wrong in this statement, does anyone?Thanks.Sat, 12 Jan 2013 10:02:09 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx[quote][b]urso47 (1/6/2013)[/b][hr]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:[quote]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![/quote]Thanks.[/quote]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.Thu, 10 Jan 2013 19:39:24 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxAnybody able to help me on this?Thu, 10 Jan 2013 19:29:12 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxHi Jeff, can you guide me on the next steps? Thanks!Wed, 09 Jan 2013 13:22:18 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxAny new instruction on the next steps? Thanks!Tue, 08 Jan 2013 04:31:00 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxYes 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:[quote]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![/quote]Thanks.Sun, 06 Jan 2013 17:22:37 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxI'm not sure what that means. Does that mean that you went back, solved your problems, and the code you posted is working fine?Sun, 06 Jan 2013 09:17:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxHi Jeff,Hope you are recovered now. I got done last lesson you gave me. This is the working code:[quote] 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 variablesDECLARE @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[/quote]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!Sat, 05 Jan 2013 16:50:04 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxIt'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.Fri, 04 Jan 2013 12:27:33 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxSOrry... 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.Fri, 04 Jan 2013 10:19:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxCan anyone help me on this?Thu, 03 Jan 2013 18:18:44 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxAny new instruction? Thanks!Wed, 02 Jan 2013 18:05:41 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxHi 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 [quote] 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[/quote]would remove the brackets of all rows of all collumns.Thanks!Wed, 02 Jan 2013 04:36:08 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxAlright... what's the name of the file you used that last stored procedure on?Wed, 02 Jan 2013 00:15:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx[quote][b]urso47 (12/31/2012)[/b][hr]As we don't have square brackets in the file uploaded, I tried removing them all, but it didn't work.[/quote]You certainly do. The last file you showed me had square brackets in the first two columns of the data.Wed, 02 Jan 2013 00:07:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxAnother 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 variablesDECLARE @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 = 1 THEN Item ELSE '' END AS VARCHAR(100))), Nome = MAX(CAST(CASE WHEN split.ItemNumber = 2 THEN Item ELSE '' END AS VARCHAR(100))), [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)), [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;GOError stil:[quote]Msg 50000, Level 16, State 1, Procedure GetFileType01, Line 124ERROR:[GetFileType01]Incorrect header for file C:\Temp\sm\urso47Test02.csv[/quote]Tue, 01 Jan 2013 09:07:30 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxMy last try:DROP PROCEDURE dbo.GetFileType01GO 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 variablesDECLARE @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 #ListaEstat = 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), [#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 #ListaEstat > '' --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 #ListaEstat = MAX(hdr.#ListaEstat), #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 = 1 THEN Item ELSE '' END AS VARCHAR(100))), Nome = MAX(CAST(CASE WHEN split.ItemNumber = 2 THEN Item ELSE '' END AS VARCHAR(100))), 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)), 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[quote]Error:Msg 3701, Level 11, State 5, Line 1Cannot drop the procedure 'dbo.GetFileType01', because it does not exist or you do not have permission.Msg 102, Level 15, State 1, Procedure GetFileType01, Line 144Incorrect syntax near '='.Msg 102, Level 15, State 1, Procedure GetFileType01, Line 196Incorrect syntax near 'split'.[/quote][quote]Line 144:#Utilizador = MAX(CAST(CASE WHEN split.ItemNumber = 1 THEN Item ELSE '' END AS VARCHAR(100))),[/quote]Even after several tries, I still not able to fix it :( ...Tue, 01 Jan 2013 01:34:08 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxAs we don't have square brackets in the file uploaded, I tried removing them all, but it didn't work.Making some changes, I realized that dots get gray, separated words get blue, some words get black from pink and blue...I tried replacing brackets by double quotes...Used square brackets only in parts with spaces, & symbol and dots, tried also double quotes instead square brackets...Also tried removing spaces like #Format Version > #Format Version... All tries brougth me errors like:[quote]Msg 3701, Level 11, State 5, Line 1Msg 102, Level 15, State 1, Procedure GetFileType01, Line 138Incorrect syntax near '='.Msg 102, Level 15, State 1, Procedure GetFileType01, Line 197Incorrect syntax near 'split'.[/quote]In P & B(Tot. de Impressões) ("&" got grey color) must be between square brackets or double quotes? Must I clear spaces?In #Format Version ("Version" got blue color) must be separated or together? Must it be between square brackets or double quotes?What is the meaning of the blue, grey, red and pink color within the statement?What else could I try to solve this matter?About my level, I am about 2 years experience, with F1 University and Google Search post graduation, so it has been 25 out of 10 for me but I agree with you, it's fine to learn by fire! Thanks!Mon, 31 Dec 2012 23:14:27 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx[quote][b]urso47 (12/31/2012)[/b][hr]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:[quote]Msg 50000, Level 18, State 1, Procedure GetFileType01, Line 123ERROR:[GetFileType01]Incorrect header for file C:\temp\sm\urso47Test02.csv[/quote]Acording with CTRL+G = 123, the error is somewhere out here:[quote]RAISERROR('ERROR:[%s]Incorrect header for file %s',18,1,@ProcName,@pFullPath);[/quote]Maybe one critical change I did should be replacing ":" by "." because I don't have the colon sign at the first header line:[quote]WHEN RowNum = 1 AND LineData LIKE '[#Lista Estat].%' THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX('.',LineData)+1,8000)))[/quote][/quote]You're just missing a little attention to detail. Look at the first line in the file you attached and tell me how many square brackets there are on that line. ;-) Once you fix that and before you run into the next problem with a similar error, look at line 7 in the file for the same problem and fix the related code.As a side bar, it IS nice to see that the error checking I wrote does work correctly. ;-)[quote]By the way for my level, this script is hard to understand, what level is this in the SQL world?[/quote]I'm probably the wrong person to be answering that question. If I had to judge on a linear scale of 1 to 10 of what I think an SQL Developer should know and not including the code for the DelimitedSplit8K function, I'd have to give it somewhere between a 5 and a 7 depending on how much they've studied on their own. In other terms, this should actually be fairly simple for someone with 2-4 years experience. For someone new at it, though, the code will seem like a 15 out of 10 at first. With that thought in mind, I think you're doing just fine. Keep chipping away at the problem and with all that you're learning in this trial by fire (the same way I learned it, BTW), people will be fighting over who gets you if you ever need another job. :-)Mon, 31 Dec 2012 17:47:30 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxHi 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:[quote]Msg 50000, Level 18, State 1, Procedure GetFileType01, Line 123ERROR:[GetFileType01]Incorrect header for file C:\temp\sm\urso47Test02.csv[/quote]Acording with CTRL+G = 123, the error is somewhere out here:[quote]RAISERROR('ERROR:[%s]Incorrect header for file %s',18,1,@ProcName,@pFullPath);[/quote]Maybe one critical change I did should be replacing ":" by "." because I don't have the colon sign at the first header line:[quote]WHEN RowNum = 1 AND LineData LIKE '[#Lista Estat].%' THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX('.',LineData)+1,8000)))[/quote]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 variablesDECLARE @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 = 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)), [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;GOBy the way for my level, this script is hard to understand, what level is this in the SQL world?Thanks again!Mon, 31 Dec 2012 16:55:18 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxHi 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!Mon, 31 Dec 2012 08:00:43 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxI guess I can't help on the error because the stored proc works fine for me. Your going to have to breakdown the proc by creating some variables and running the code in the proc instead of the CREATE PROCEDURE itself.The reason why you can't see the #Directory or #eq_Staging tables from the stored procedure is because they're Temp Tables. You can't see them outside of the scope of the stored procedure.Running the code to create the #Directory table will create the table in TempDB and you can actually see it but you might not be able to see its content in the object explorer because that constitutes a change in scope that hides the contents. Only the original session can see the contents.Sun, 30 Dec 2012 22:08:35 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxHi Jeff,You know, it's a whole new SQL world for me when I see you statements, and I take couple of hours to understand it even when you kindly explain every single programming line and I'm really glad for that.I did create the SP ImportEqFiles running the statment you created and it was successfully created (Message: Command(s) completed successfully.), then I executed the EXEC dbo.ImportEqFiles and I got the message:[quote] BULK INSERT #eq_Staging FROM 'C:\Temp\eq_files\Eq_sample_1.csv' WITH ( FIELDTERMINATOR = ',', FIRSTROW = 2, ROWTERMINATOR = '', CODEPAGE = 'ACP', DATAFILETYPE = 'widechar' ); UPDATE dbo.FileLog SET StatusDate = GETDATE(), FileStatus = 'Staged' WHERE FilePath = 'C:\Temp\eq_files\' AND FileName = 'Eq_sample_1.csv' BULK INSERT #eq_Staging FROM 'C:\Temp\eq_files\Eq_sample_2.csv' WITH ( FIELDTERMINATOR = ',', FIRSTROW = 2, ROWTERMINATOR = '', CODEPAGE = 'ACP', DATAFILETYPE = 'widechar' ); UPDATE dbo.FileLog SET StatusDate = GETDATE(), FileStatus = 'Staged' WHERE FilePath = 'C:\Temp\eq_files\' AND FileName = 'Eq_sample_2.csv'Msg 7301, Level 16, State 2, Line 2Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".[/quote]I can see 2 rows applying a SELECT * FROM dbo.FileLog but when I apply a SELECT * FROM #eq_Staging and SELECT * FROM #Directory I got the same error message:[quote]Msg 208, Level 16, State 0, Line 1 Invalid object name '#eq_Staging'.[/quote]Seems that they are not within this DB, so, I might be doing something wrong.The weird thing is that even when I run both of this CREATE TABLE statements for the #eq_Staging table separatelly in other window I get the [quote]"Message: Command(s) completed successfully." [/quote]but I still can't find them in my DB Object Explorer (in my DB objects tree).I ran alsoCREATE TABLE #Directory( ObjectName VARCHAR(450), Depth TINYINT, IsFile TINYINT)but I still can't find those tables in anywhere...I am looking at this for the last few hours but I can't find the solution on how parse the cleaned information to final table because I could't find the #eq_Staging and the #Documentation table so, I will wait for the next hints to proceed with the "ToDo" section.Thanks a lot!!!Sun, 30 Dec 2012 10:25:37 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxOk... I decided to do the EQ file imports as a stored procedure for you. The hard stuff (the imports) have been done. As usual, the details are in the comments in the code. Make sure you read the "Todo" section near the end of the proc.[code="sql"] CREATE PROCEDURE dbo.ImportEqFiles /********************************************************************************************************************** Purpose: Given a valid file path and the "LIKE" filter for file names, import the files from the given file path and log the processing of each file in the FileLog table (which is created by this proc if it doesn't exist). Files that have already been imported will not be imported again. Usage Examples: EXEC dbo.ImportEqFiles @pFilePath, @pFilter EXEC dbo.ImportEqFiles 'C:\Temp\eq_files', 'Eq_sample_%.csv'**********************************************************************************************************************/--===== Define the parameters for this proc @pFilePath VARCHAR(450), @pFileFilter VARCHAR(450) AS--=====================================================================================================================-- Presets--=====================================================================================================================--===== Setup the environment SET NOCOUNT ON; --Prevents false error returns to the calling procedure or GUI SET XACT_ABORT ON; --Short circuits any explicit transactions (if they exist) when an error occurs.--===== Conditionally drop the all Temp Tables to make reruns in SSMS easier IF OBJECT_ID('tempdb..#eq_Staging','U') IS NOT NULL DROP TABLE #eq_Staging; IF OBJECT_ID('tempdb..#Directory' ,'U') IS NOT NULL DROP TABLE #Directory;--===== Create the staging table using all VARCHAR datatypes because -- of all the double quotes in the file(s) we're loading. CREATE TABLE #eq_Staging( cGroupID VARCHAR(200), cGroupIDBillable VARCHAR(200), cGroupName VARCHAR(200), cGroupDescription VARCHAR(200), cRowID VARCHAR(200), cRowName VARCHAR(200), cRowDescription VARCHAR(200), cTransactDate VARCHAR(200), cTransactType VARCHAR(200), cChargeAccountID VARCHAR(200), cChargeAccountType VARCHAR(200), cUserWhoPrinted VARCHAR(200), cDocumentName NVARCHAR(200), cUnits VARCHAR(200), cDocumentID VARCHAR(200), cDeviceIP VARCHAR(200), cDevModel VARCHAR(200), cDevManuf VARCHAR(200), cDuration VARCHAR(200), cDestination VARCHAR(200), cFullName VARCHAR(200), cPrimaryPIN VARCHAR(200), cWorkstation VARCHAR(200), cAmount VARCHAR(200), JobProperties VARCHAR(200), cAltCost VARCHAR(200), cDepartment VARCHAR(200), cBillable VARCHAR(200), cAlternatePin VARCHAR(200));--===== Create the table to hold the directory information we'll need. CREATE TABLE #Directory( ObjectName VARCHAR(450), Depth TINYINT, IsFile TINYINT);--===== If the file processing log table doesn't already exist, create it now. IF OBJECT_ID('dbo.FileLog','U') IS NULL CREATE TABLE dbo.FileLog( FileLogID INT IDENTITY(1,1), FilePath VARCHAR(450) NOT NULL, FileName VARCHAR(450) NOT NULL, StatusDate DATETIME NOT NULL DEFAULT GETDATE(), FileStatus VARCHAR(20) NOT NULL, ProcessedBy VARCHAR(128) NOT NULL DEFAULT ORIGINAL_LOGIN(), CONSTRAINT PK_FileLog PRIMARY KEY CLUSTERED (FileLogID), CONSTRAINT AK_FileLog UNIQUE (FilePath,FileName));--===== Declare local variablesDECLARE @FullFileName VARCHAR(500), @SQL VARCHAR(MAX);--===== Ensure the file path ends with a backslash and build the quoted file name SELECT @pFilePath = CASE WHEN RIGHT(@pFilePath,1) = '\' THEN @pFilePath ELSE @pFilePath + '\' END;--===================================================================================================================== -- Load all of the filenames from the given path into the log--===================================================================================================================== --===== Load all the file names using xp_dirtree which does NOT require xp_CmdShell to be enabled. INSERT INTO #Directory (ObjectName, Depth, IsFile) EXEC xp_dirtree @pFilePath,1,1;--===== Add the files that aren't already in the log as "Import In Process" files that need to be processed. INSERT INTO dbo.FileLog (FilePath, FileName, StatusDate, FileStatus) SELECT FilePath = @pFilePath, ObjectName, StatusDate = GETDATE(), FileStatus = 'Import inprocess' FROM #Directory WHERE IsFile = 1 AND ObjectName LIKE @pFileFilter EXCEPT SELECT FilePath, FileName, StatusDate = GETDATE(), FileStatus = 'Import inprocess' FROM dbo.FileLog;--===================================================================================================================== -- Import the "Import inprocess" files to the staging table--===================================================================================================================== SELECT @SQL = ISNULL(@SQL,'') + ' BULK INSERT #eq_Staging FROM ' + QUOTENAME(@pFilePath+FileName,'''') + ' WITH ( FIELDTERMINATOR = '','', FIRSTROW = 2, ROWTERMINATOR = '''', CODEPAGE = ''ACP'', DATAFILETYPE = ''widechar'' ); UPDATE dbo.FileLog SET StatusDate = GETDATE(), FileStatus = ''Staged'' WHERE FilePath = ' + QUOTENAME(@pFilePath,'''') + ' AND FileName = ' + QUOTENAME(FileName ,'''') + '' FROM dbo.FileLog WHERE FilePath = @pFilePath AND FileName LIKE @pFileFilter AND FileStatus = 'Import inprocess';--===== Display the commands that we're goig to run. -- (Note that will be truncated for display at 8K but will be the full command in reality) PRINT @SQL--===== Import and log all the files identified by this run EXEC (@SQL)--===== Todo... this is where you would delete the quotes, validate the data in the staging table, do any error -- reporting, and send the data from the staging table to the final table. Just for demonstration purposes, -- we'll simply display the contents of the #eq_Staging table and the dbo.FileLog table. Keep in mind that a -- second run won't import files that have already been logged in the dbo.FileLog table. SELECT * FROM #eq_Staging SELECT * FROM dbo.FileLogGO[/code]Here's a couple of example runs...[code="sql"]-------------------------------------------------------------------------------------------------------------------------===== Demonstrate the proc. Since this is the first run, you'll have output from the staging table for the files -- that were imported. EXEC dbo.ImportEqFiles 'C:\Temp\eq_files', 'Eq_sample_%.csv';--===== Let's run the proc again with the same settings. Since we've already logged that these files have been -- imported to the staging table and, theoretically processed already, they won't be loaded/displayed again -- for this run. Only the contents of the FileLog table will be displayed. Of course, if you look at the -- "Todo" section in the proc, you have a bit more work to do that I'm going to let you do. You may also -- have to play with the correct code page to get the document names to come out correctly. EXEC dbo.ImportEqFiles 'C:\Temp\eq_files', 'Eq_sample_%.csv'--===== If you want to see it all work as described above again, run the following commented out code. -- DROP TABLE dbo.FileLog[/code]With that and the previous example for the files that have headers, you should be able to handle the rest.Sat, 29 Dec 2012 23:51:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxThanks Jeff, I will wait for your always helpfull examples then!Sat, 29 Dec 2012 21:59:55 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxI made some changes in the formatfle but still not working :( ...10.0 29 1 SQLCHAR 0 12 "\"," 1 cGroupID ""2 SQLCHAR 0 12 "\"," 2 cGroupIDBillable ""3 SQLCHAR 0 0 ",\"" 3 cGroupName Latin1_General_CI_AS4 SQLCHAR 0 0 "\"," 4 cGroupDescription Latin1_General_CI_AS5 SQLCHAR 0 12 "\"," 5 cRowID ""6 SQLCHAR 0 0 ",\"" 6 cRowName Latin1_General_CI_AS7 SQLCHAR 0 0 ",\"" 7 cRowDescription Latin1_General_CI_AS8 SQLCHAR 0 24 "\"," 8 cTransactDate ""9 SQLCHAR 0 0 ",\"" 9 cTransactType Latin1_General_CI_AS10 SQLCHAR 0 0 ",\"" 10 cChargeAccountID Latin1_General_CI_AS11 SQLCHAR 0 0 ",\"" 11 cChargeAccountType Latin1_General_CI_AS12 SQLCHAR 0 0 ",\"" 12 cUserWhoPrinted Latin1_General_CI_AS13 SQLCHAR 0 0 ",\"" 13 cDocumentName Latin1_General_CI_AS14 SQLCHAR 0 12 "\"," 14 cUnits ""15 SQLCHAR 0 0 ",\"" 15 cDocumentID Latin1_General_CI_AS16 SQLCHAR 0 0 ",\"" 16 cDeviceIP Latin1_General_CI_AS17 SQLCHAR 0 0 ",\"" 17 cDevModel Latin1_General_CI_AS18 SQLCHAR 0 0 ",\"" 18 cDevManuf Latin1_General_CI_AS19 SQLCHAR 0 0 ",\"" 19 cDuration Latin1_General_CI_AS20 SQLCHAR 0 0 ",\"" 20 cDestination Latin1_General_CI_AS21 SQLCHAR 0 0 ",\"" 21 cFullName Latin1_General_CI_AS22 SQLCHAR 0 0 ",\"" 22 cPrimaryPIN Latin1_General_CI_AS23 SQLCHAR 0 0 ",\"" 23 cWorkstation Latin1_General_CI_AS24 SQLCHAR 0 12 "\"," 24 cAmount ""25 SQLCHAR 0 0 ",\"" 25 JobProperties Latin1_General_CI_AS26 SQLCHAR 0 12 "\"," 26 cAltCost ""27 SQLCHAR 0 0 ",\"" 27 cDepartment Latin1_General_CI_AS28 SQLCHAR 0 0 ",\"" 28 cBillable Latin1_General_CI_AS29 SQLCHAR 0 0 "\"\r" 29 cAlternatePin Latin1_General_CI_ASSat, 29 Dec 2012 21:56:12 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx[quote][b]urso47 (12/29/2012)[/b][hr]Hi guys,I have been struggling myself for the last two days but I couldn't make it work. Could anyone help me on this statement?-- TABLE EQUITUSE TEMPDBGOCREATE TABLE Equit(cGroupID INT,cGroupIDBillable INT,cGroupName VARCHAR(MAX),cGroupDescription VARCHAR(MAX),cRowID INT,cRowName VARCHAR(MAX),cRowDescription VARCHAR(MAX),cTransactDate SMALLDATETIME,cTransactType VARCHAR(MAX),cChargeAccountID VARCHAR(MAX),cChargeAccountType VARCHAR(MAX),cUserWhoPrinted VARCHAR(MAX),cDocumentName VARCHAR(MAX),cUnits INT,cDocumentID VARCHAR(MAX),cDeviceIP VARCHAR(MAX),cDevModel VARCHAR(MAX),cDevManuf VARCHAR(MAX),cDuration VARCHAR(MAX),cDestination VARCHAR(MAX),cFullName VARCHAR(MAX),cPrimaryPIN VARCHAR(MAX),cWorkstation VARCHAR(MAX),cAmount INT,JobProperties VARCHAR(MAX),cAltCost INT,cDepartment VARCHAR(MAX),cBillable VARCHAR(MAX),cAlternatePin VARCHAR(MAX))GO-- FORMAT FILE FORMAT.EQUIT.FMT10.0 30 1 SQLCHAR 0 0 "\"" 0 Line SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 0 "\"," 1 cGroupID SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 0 "\"," 2 cGroupIDBillable SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 2 0 ",\"" 3 cGroupName SQL_Latin1_General_CP1_CI_AS5 SQLCHAR 2 0 ",\"" 4 cGroupDescription SQL_Latin1_General_CP1_CI_AS6 SQLCHAR 2 0 ",\"" 5 cRowID SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 2 0 ",\"" 6 cRowName SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 2 0 ",\"" 7 cRowDescription SQL_Latin1_General_CP1_CI_AS9 SQLCHAR 2 0 ",\"" 8 cTransactDate SQL_Latin1_General_CP1_CI_AS10 SQLCHAR 2 0 ",\"" 9 cTransactType SQL_Latin1_General_CP1_CI_AS11 SQLCHAR 2 0 ",\"" 10 cChargeAccountID SQL_Latin1_General_CP1_CI_AS12 SQLCHAR 2 0 ",\"" 11 cChargeAccountType SQL_Latin1_General_CP1_CI_AS13 SQLCHAR 2 0 ",\"" 12 cUserWhoPrinted SQL_Latin1_General_CP1_CI_AS14 SQLCHAR 2 0 ",\"" 13 cDocumentName SQL_Latin1_General_CP1_CI_AS15 SQLCHAR 2 0 "\"," 14 cUnits SQL_Latin1_General_CP1_CI_AS16 SQLCHAR 2 0 ",\"" 15 cDocumentID SQL_Latin1_General_CP1_CI_AS17 SQLCHAR 2 0 ",\"" 16 cDeviceIP SQL_Latin1_General_CP1_CI_AS18 SQLCHAR 2 0 ",\"" 17 cDevModel SQL_Latin1_General_CP1_CI_AS19 SQLCHAR 2 0 ",\"" 18 cDevManuf SQL_Latin1_General_CP1_CI_AS20 SQLCHAR 2 0 ",\"" 19 cDuration SQL_Latin1_General_CP1_CI_AS21 SQLCHAR 2 0 ",\"" 20 cDestination SQL_Latin1_General_CP1_CI_AS22 SQLCHAR 2 0 ",\"" 21 cFullName SQL_Latin1_General_CP1_CI_AS23 SQLCHAR 2 0 ",\"" 22 cPrimaryPIN SQL_Latin1_General_CP1_CI_AS24 SQLCHAR 2 0 ",\"" 23 cWorkstation SQL_Latin1_General_CP1_CI_AS25 SQLCHAR 2 0 "\"," 24 cAmount SQL_Latin1_General_CP1_CI_AS26 SQLCHAR 2 0 ",\"" 25 JobProperties SQL_Latin1_General_CP1_CI_AS27 SQLCHAR 2 0 "\"," 26 cAltCost SQL_Latin1_General_CP1_CI_AS28 SQLCHAR 2 0 ",\"" 27 cDepartment SQL_Latin1_General_CP1_CI_AS29 SQLCHAR 2 0 ",\"" 28 cBillable SQL_Latin1_General_CP1_CI_AS30 SQLCHAR 2 0 "\"\r" 29 cAlternatePin SQL_Latin1_General_CP1_CI_AS--=== BLANK LINEMASTER..XP_CMDSHELL 'bcp EQUIT in c:\temp\equit.csv -f c:\temp\equitfmt.fmt'BULK INSERT EQUIT FROM 'c:\temp\equit.csv' WITH (FORMATFILE = 'c:\temp\equitfmt.fmt')Error:(13 row(s) affected)Msg 4864, Level 16, State 1, Line 2Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (cGroupID).Msg 4832, Level 16, State 1, Line 2Bulk load: An unexpected end of file was encountered in the data file.Msg 7399, Level 16, State 1, Line 2The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 2Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".I also tried this one but I got the same error.DECLARE @CAMINHO VARCHAR(256), @SQL VARCHAR (1000)SET @CAMINHO = 'C:\TEMP\Equit.csv'SET @SQL = 'BULK INSERT EQUITFROM ''' + @CAMINHO + '''WITH (FIELDTERMINATOR = '','', CODEPAGE =''ACP'', ROWTERMINATOR='''')'EXEC (@SQL)The files used are attached.Thanks in advance![/quote]You have the right idea with the format file and the \" delimiter thing. Unfortunately, the data dosn't have a consistant format. There are places where a single double-quote is used, places where a double double-quote is used, and empty fields in each row that have no quotes. This is normally what happens when someone exports from a spreadsheet.That also part of the reason for you import failure messages.This, however, is what staging tables are all about. The only consistant delimiter is the comma so use that to do the initial import to a staging table and then cleanup/validate the data. And, if you can avoid it, stop using VARCHAR(MAX) for everything. It'll just slow your code down. You'll also need to import everything as a simple VARCHAR instead of SMALLDATETIME and NUMERIC(18,2) as you have in many places because the double quotes won't let them convert.I'll be back shortly with an example.Sat, 29 Dec 2012 21:36:06 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx[quote]I need the CREATE TABLE statements for each of these file types. JPG's won't do me any good. I need the code itself. You can save it TXT or SQL files. Either is fine with me.[/quote]Hi Jeff,Thanks for your response. I just uploaded the statements and the sample files again.Sat, 29 Dec 2012 20:21:22 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxHi guys,I have been struggling myself for the last two days but I couldn't make it work. Could anyone help me on this statement?-- TABLE EQUITUSE TEMPDBGOCREATE TABLE Equit(cGroupID INT,cGroupIDBillable INT,cGroupName VARCHAR(MAX),cGroupDescription VARCHAR(MAX),cRowID INT,cRowName VARCHAR(MAX),cRowDescription VARCHAR(MAX),cTransactDate SMALLDATETIME,cTransactType VARCHAR(MAX),cChargeAccountID VARCHAR(MAX),cChargeAccountType VARCHAR(MAX),cUserWhoPrinted VARCHAR(MAX),cDocumentName VARCHAR(MAX),cUnits INT,cDocumentID VARCHAR(MAX),cDeviceIP VARCHAR(MAX),cDevModel VARCHAR(MAX),cDevManuf VARCHAR(MAX),cDuration VARCHAR(MAX),cDestination VARCHAR(MAX),cFullName VARCHAR(MAX),cPrimaryPIN VARCHAR(MAX),cWorkstation VARCHAR(MAX),cAmount INT,JobProperties VARCHAR(MAX),cAltCost INT,cDepartment VARCHAR(MAX),cBillable VARCHAR(MAX),cAlternatePin VARCHAR(MAX))GO-- FORMAT FILE FORMAT.EQUIT.FMT10.0 30 1 SQLCHAR 0 0 "\"" 0 Line SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 0 0 "\"," 1 cGroupID SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 0 0 "\"," 2 cGroupIDBillable SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 2 0 ",\"" 3 cGroupName SQL_Latin1_General_CP1_CI_AS5 SQLCHAR 2 0 ",\"" 4 cGroupDescription SQL_Latin1_General_CP1_CI_AS6 SQLCHAR 2 0 ",\"" 5 cRowID SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 2 0 ",\"" 6 cRowName SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 2 0 ",\"" 7 cRowDescription SQL_Latin1_General_CP1_CI_AS9 SQLCHAR 2 0 ",\"" 8 cTransactDate SQL_Latin1_General_CP1_CI_AS10 SQLCHAR 2 0 ",\"" 9 cTransactType SQL_Latin1_General_CP1_CI_AS11 SQLCHAR 2 0 ",\"" 10 cChargeAccountID SQL_Latin1_General_CP1_CI_AS12 SQLCHAR 2 0 ",\"" 11 cChargeAccountType SQL_Latin1_General_CP1_CI_AS13 SQLCHAR 2 0 ",\"" 12 cUserWhoPrinted SQL_Latin1_General_CP1_CI_AS14 SQLCHAR 2 0 ",\"" 13 cDocumentName SQL_Latin1_General_CP1_CI_AS15 SQLCHAR 2 0 "\"," 14 cUnits SQL_Latin1_General_CP1_CI_AS16 SQLCHAR 2 0 ",\"" 15 cDocumentID SQL_Latin1_General_CP1_CI_AS17 SQLCHAR 2 0 ",\"" 16 cDeviceIP SQL_Latin1_General_CP1_CI_AS18 SQLCHAR 2 0 ",\"" 17 cDevModel SQL_Latin1_General_CP1_CI_AS19 SQLCHAR 2 0 ",\"" 18 cDevManuf SQL_Latin1_General_CP1_CI_AS20 SQLCHAR 2 0 ",\"" 19 cDuration SQL_Latin1_General_CP1_CI_AS21 SQLCHAR 2 0 ",\"" 20 cDestination SQL_Latin1_General_CP1_CI_AS22 SQLCHAR 2 0 ",\"" 21 cFullName SQL_Latin1_General_CP1_CI_AS23 SQLCHAR 2 0 ",\"" 22 cPrimaryPIN SQL_Latin1_General_CP1_CI_AS24 SQLCHAR 2 0 ",\"" 23 cWorkstation SQL_Latin1_General_CP1_CI_AS25 SQLCHAR 2 0 "\"," 24 cAmount SQL_Latin1_General_CP1_CI_AS26 SQLCHAR 2 0 ",\"" 25 JobProperties SQL_Latin1_General_CP1_CI_AS27 SQLCHAR 2 0 "\"," 26 cAltCost SQL_Latin1_General_CP1_CI_AS28 SQLCHAR 2 0 ",\"" 27 cDepartment SQL_Latin1_General_CP1_CI_AS29 SQLCHAR 2 0 ",\"" 28 cBillable SQL_Latin1_General_CP1_CI_AS30 SQLCHAR 2 0 "\"\r" 29 cAlternatePin SQL_Latin1_General_CP1_CI_AS--=== BLANK LINEMASTER..XP_CMDSHELL 'bcp EQUIT in c:\temp\equit.csv -f c:\temp\equitfmt.fmt'BULK INSERT EQUIT FROM 'c:\temp\equit.csv' WITH (FORMATFILE = 'c:\temp\equitfmt.fmt')Error:(13 row(s) affected)Msg 4864, Level 16, State 1, Line 2Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (cGroupID).Msg 4832, Level 16, State 1, Line 2Bulk load: An unexpected end of file was encountered in the data file.Msg 7399, Level 16, State 1, Line 2The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 2Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".I also tried this one but I got the same error.DECLARE @CAMINHO VARCHAR(256), @SQL VARCHAR (1000)SET @CAMINHO = 'C:\TEMP\Equit.csv'SET @SQL = 'BULK INSERT EQUITFROM ''' + @CAMINHO + '''WITH (FIELDTERMINATOR = '','', CODEPAGE =''ACP'', ROWTERMINATOR='''')'EXEC (@SQL)The files used are attached.Thanks in advance!Sat, 29 Dec 2012 20:16:56 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx[quote]I need the CREATE TABLE statements for each of these file types. JPG's won't do me any good. I need the code itself. You can save it TXT or SQL files. Either is fine with me.[/quote]Hi Jeff,Thanks for your response. I just uploaded the statements and the sample files again.Thu, 27 Dec 2012 19:08:12 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx[quote][b]urso47 (12/25/2012)[/b][hr]Thanks Jeff,I will use a directory at: C:\Temp\Eq_files\Eq_sample_1.csv and Eq_sample_2.csvC:\Temp\sync_files\sync_sample_1.csv and sync_sample_2.csvC:\Temp\pcut_files\pcut_sample_1.csv and pcut_sample_2.csvC:\Temp\sm_files\V151110456_print.csv and V1511100466_print.csvAll the files are zipped in a folder (sample files, table structure image and t-code). Pcut_files and sm_files have headers. Eq_files have a big length problematic field.For the pcut_files, the first header row is unnecessary. If possible, I would like to have an Identity field with an Identity Increment for all those tables.I tried CREATE TABLE t_eq_temp then BULK INSERT on it in order to SELECT INTO t_equitrac table but it didn't work, I can't even remove the double quotes, and the cDocumentName field gets truncated... For the eq_sample_1.csv a BULK INSERT works with all fields as VARCHAR (MAX) but, I still have the unnecessary double quotes and the sDocumentName gets splited and part of it goes to the next fields. The real files have more than 100k rows, I can send you by e-mail if you prefer. Is there a way to avoid duplicated rows in case I try to insert the same file over again?All files are in original format now, and to be honest, I didn't even imagine that we would go this further with this help and I am very glad for that.Many Thanks again!Andre[/quote]I need the CREATE TABLE statements for each of these file types. JPG's won't do me any good. I need the code itself. You can save it TXT or SQL files. Either is fine with me.Thu, 27 Dec 2012 17:02:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx[quote][b]CELKO (12/27/2012)[/b][hr]I sued Monarch years ago,... [/quote]Why did you need to sue them?Thu, 27 Dec 2012 16:55:31 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxHi Celko,Thank you for your message. I just submitted a download form and a message said that an Astera Software representative will be in contact shortly.Thu, 27 Dec 2012 15:44:22 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxThere are tools for Report mining tool. I sued Monarch years ago, and you can see an old video at:http://www.youtube.com/watch?v=MxTGHlsyJsoAnother product is:http://www.astera.com/solutions/technology-solutions/report-mining?gclid=CO_klOmZu7QCFeiPPAodMxoAxQWhcih has a free download.Basically you pull up the text of the report on a screen, pick the fields you to pull off and load them into a file that can be loaded into a database. Nice simple GUI interfaces. They correct the date formats, ignore report headers, make simple decisions, etc. Astera has a free version.Thu, 27 Dec 2012 11:29:15 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxUp!!!:-DThu, 27 Dec 2012 02:05:55 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxThanks Jeff,I will use a directory at: C:\Temp\Eq_files\Eq_sample_1.csv and Eq_sample_2.csvC:\Temp\sync_files\sync_sample_1.csv and sync_sample_2.csvC:\Temp\pcut_files\pcut_sample_1.csv and pcut_sample_2.csvC:\Temp\sm_files\V151110456_print.csv and V1511100466_print.csvAll the files are zipped in a folder (sample files, table structure image and t-code). Pcut_files and sm_files have headers. Eq_files have a big length problematic field.For the pcut_files, the first header row is unnecessary. If possible, I would like to have an Identity field with an Identity Increment for all those tables.I tried CREATE TABLE t_eq_temp then BULK INSERT on it in order to SELECT INTO t_equitrac table but it didn't work, I can't even remove the double quotes, and the cDocumentName field gets truncated... For the eq_sample_1.csv a BULK INSERT works with all fields as VARCHAR (MAX) but, I still have the unnecessary double quotes and the sDocumentName gets splited and part of it goes to the next fields. The real files have more than 100k rows, I can send you by e-mail if you prefer. Is there a way to avoid duplicated rows in case I try to insert the same file over again?All files are in original format now, and to be honest, I didn't even imagine that we would go this further with this help and I am very glad for that.Many Thanks again!AndreTue, 25 Dec 2012 20:49:44 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx[quote][b]urso47 (12/25/2012)[/b][hr]Hope you had a great X'mas Eve![/quote]Thank you and Merry Christmas![quote]It worked perfectly, even when I don't get whole understanding right now, but I will learn when practicing. I didn't even know about this "CROSS APPLY " and " Item = REPLACE" function. Thank you for teach me step by step like a kid, because for me most part of the code still very hard to understand.[/quote]Press the {f1} key to bring up "Books Online". That and this website should become your best friends.[quote]Now, I would like to ask you how could I execute this SP in dozens of files within the same directory (c:temp in this case). Is there a way to call all the files within this directory regardless the file name?[/quote]Yes... that's the next "lesson". What is the name of "that" directory?[quote]I found the SP under Programmability, but not the table for the "GetFileType01", so I think we have to do an INSERT INTO SomeTable, am I right?[/quote]Correct. We first needed to show you how to parse the data. Next, we'll show you how to insert it into a table and how to do it for many files.[quote]And finally, for some other files some fields will be bigger than 8K, specially one field that brings http adresses longer than 500 or 600 characters and I am having troubles even using the T-SQL or a SQL Server Import Export Tools, it comes with the error message:[/quote]I need the complete file layout in order to be able to help there. A sample file would also be very helpful.[quote]When I use a T-SQL transaction to do it, I found out the this field gets splited and part of it goes to the next fields, even using VARCHAR(MAX) or TEXT configuration in this field. I don't have the "header problem" with this files, but now I faced this problem with a very big/long information to insert in this other table field.[/quote]Again, I'd need to know the layout for such a file including what the delimiters are (comma, tab, or something else). I'd also need to know if the large column has any column delimiters embedded in the data itself and if the column is encapsulated in any type of "text qualifier".[quote]Always thank you, Jeff!Andre[/quote]My pleasure. If you want to save some time, always include things like a record layout, the CREATE TABLE statement for the target table, and, if you can and without violating any private information or "company proprietary informatio", at least the first 10 lines from the files you'll be working with. Obviously, a file like those containing headers will need to have more lines included to cover the header and about 10 lines of data.Tue, 25 Dec 2012 15:30:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspxHi Jeff,Hope you had a great X'mas Eve!I have to agree with you, this whole code is like a every kid's dream ;-)!It worked perfectly, even when I don't get whole understanding right now, but I will learn when practicing. I didn't even know about this "CROSS APPLY " and " Item = REPLACE" function. Thank you for teach me step by step like a kid, because for me most part of the code still very hard to understand.Now, I would like to ask you how could I execute this SP in dozens of files within the same directory (c:temp in this case). Is there a way to call all the files within this directory regardless the file name?I found the SP under Programmability, but not the table for the "GetFileType01", so I think we have to do an INSERT INTO SomeTable, am I right?And finally, for some other files some fields will be bigger than 8K, specially one field that brings http adresses longer than 500 or 600 characters and I am having troubles even using the T-SQL or a SQL Server Import Export Tools, it comes with the error message:Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "cDocumentName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard) Error 0xc020902a: Data Flow Task 1: The "output column "cDocumentName" (58)" failed because truncation occurred, and the truncation row disposition on "output column "cDocumentName" (58)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\MyDocs\SimpressDB\Equitrac\Equitrac.csv" on data row 2. (SQL Server Import and Export Wizard) Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Equitrac_csv" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard) When I use a T-SQL transaction to do it, I found out the this field gets splited and part of it goes to the next fields, even using VARCHAR(MAX) or TEXT configuration in this field. I don't have the "header problem" with this files, but now I faced this problem with a very big/long information to insert in this other table field.Always thank you, Jeff!AndreTue, 25 Dec 2012 10:55:15 GMTurso47http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx[quote][b]urso47 (12/23/2012)[/b][hr]Hi Jeff,First of all, I am impressed how helpfull you were creating this code, it is totally amazing! I am so happy to find people like you helping people like me that knows just a little bit.I created the directory C:Temp/, copied the files into it, executed the 2 files GetFileType01 and The New DelimitedSplit8K Function but I can't even find the table to apply a select or find this SP :(Maybe it is too much to ask but, could you tell me how to use the files? I read the instructions but I still not able to find out how to use it...Thanks a lot again!PS.:1- I have only the weekend to study and practice but I will do my best to do it on weekdays either.2- Once the table was created, I think would be easier just add the new files into it (use "select into" instead "create table", what do you think?).[/quote]The C:\Temp directory was just an example of a "data directory". My bad for not being a bit more clear. 1. You've already done this... From SSMS, execute the "The New DelimitedSplit8K Function.sql" file in the same database as where you intend all of this work to be done.2. I think you've already done this, as well... From SSMS, execute the "GetFileType01.sql" file in the same database as where you intend all of this work to be done.3. Copy the "RawData8000.fmt" file to C:\Temp on the server. We'll get back to this in a minute but let's get the test file running first. This is the file necessary to be able to do the imports and allow the rows to be numbered.4. Copy the "urso47Test01.txt" file to C:\Temp on the server. This is just a test file with the data from your original post in it. It's just for test purposes.5. We're all set now. Run the following command in SSMS.[code="sql"] EXEC dbo.GetFileType01 'C:\Temp\urso47Test01.txt';[/code]You should see the following display which just shows that the original data was loaded and parsed correctly.[code="plain"]ListName FormatVersion Date Equipment IP UserNumber UserName Total B&WUser 1.2.5.0 2012-12-11 12:00:34.000 L6867000214 172.21.12.62 1600 technical 76 76User 1.2.5.0 2012-12-11 12:00:34.000 L6867000214 172.21.12.62 6611 Daise 559 559User 1.2.5.0 2012-12-11 12:00:34.000 L6867000214 172.21.12.62 900301 Richard 0 0 [/code]6. C:\Temp isn't a good place to keep anything permanently. What I recommend is that you copy the "RawData8000.fmt" to the same directory as where your real files are.7. Now, find the following line in the dbo.GetFileType01 stored procedure...[code="plain"] [color="Red"] FORMATFILE = ''C:\Temp\RawData8000.fmt'',[/color][/code]... and change the C:\Temp\ part of that to wherever you just copied the RawData8000.fmt format file to.8. Pick one of your data files from that same directory and substitute either the full file path or full UNC between the quotes to load the file, parse it, and display it. [code="sql"] EXEC dbo.GetFileType01 'Put full file path or UNC here'[/code]Once you have that working, we'll move onto the next steps.Sun, 23 Dec 2012 22:18:50 GMTJeff Moden