December 27, 2012 at 5:02 pm
urso47 (12/25/2012)
Thanks Jeff,I will use a directory at:
C:\Temp\Eq_files\Eq_sample_1.csv and Eq_sample_2.csv
C:\Temp\sync_files\sync_sample_1.csv and sync_sample_2.csv
C:\Temp\pcut_files\pcut_sample_1.csv and pcut_sample_2.csv
C:\Temp\sm_files\V151110456_print.csv and V1511100466_print.csv
All 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
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 27, 2012 at 7:08 pm
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.
Hi Jeff,
Thanks for your response. I just uploaded the statements and the sample files again.
December 29, 2012 at 8:16 pm
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 EQUIT
USE TEMPDB
GO
CREATE TABLE Equit
(
cGroupIDINT,
cGroupIDBillableINT,
cGroupNameVARCHAR(MAX),
cGroupDescriptionVARCHAR(MAX),
cRowIDINT,
cRowNameVARCHAR(MAX),
cRowDescriptionVARCHAR(MAX),
cTransactDateSMALLDATETIME,
cTransactTypeVARCHAR(MAX),
cChargeAccountIDVARCHAR(MAX),
cChargeAccountTypeVARCHAR(MAX),
cUserWhoPrintedVARCHAR(MAX),
cDocumentNameVARCHAR(MAX),
cUnitsINT,
cDocumentIDVARCHAR(MAX),
cDeviceIPVARCHAR(MAX),
cDevModelVARCHAR(MAX),
cDevManufVARCHAR(MAX),
cDurationVARCHAR(MAX),
cDestinationVARCHAR(MAX),
cFullNameVARCHAR(MAX),
cPrimaryPINVARCHAR(MAX),
cWorkstationVARCHAR(MAX),
cAmountINT,
JobPropertiesVARCHAR(MAX),
cAltCostINT,
cDepartmentVARCHAR(MAX),
cBillableVARCHAR(MAX),
cAlternatePinVARCHAR(MAX)
)
GO
-- FORMAT FILE FORMAT.EQUIT.FMT
10.0
30
1SQLCHAR00"\""0LineSQL_Latin1_General_CP1_CI_AS
2SQLCHAR00"\","1cGroupIDSQL_Latin1_General_CP1_CI_AS
3SQLCHAR00"\","2cGroupIDBillableSQL_Latin1_General_CP1_CI_AS
4SQLCHAR20",\""3cGroupNameSQL_Latin1_General_CP1_CI_AS
5SQLCHAR20",\""4cGroupDescriptionSQL_Latin1_General_CP1_CI_AS
6SQLCHAR20",\""5cRowIDSQL_Latin1_General_CP1_CI_AS
7SQLCHAR20",\""6cRowNameSQL_Latin1_General_CP1_CI_AS
8SQLCHAR20",\""7cRowDescriptionSQL_Latin1_General_CP1_CI_AS
9SQLCHAR20",\""8cTransactDateSQL_Latin1_General_CP1_CI_AS
10SQLCHAR20",\""9cTransactTypeSQL_Latin1_General_CP1_CI_AS
11SQLCHAR20",\""10cChargeAccountIDSQL_Latin1_General_CP1_CI_AS
12SQLCHAR20",\""11cChargeAccountTypeSQL_Latin1_General_CP1_CI_AS
13SQLCHAR20",\""12cUserWhoPrintedSQL_Latin1_General_CP1_CI_AS
14SQLCHAR20",\""13cDocumentNameSQL_Latin1_General_CP1_CI_AS
15SQLCHAR20"\","14cUnitsSQL_Latin1_General_CP1_CI_AS
16SQLCHAR20",\""15cDocumentIDSQL_Latin1_General_CP1_CI_AS
17SQLCHAR20",\""16cDeviceIPSQL_Latin1_General_CP1_CI_AS
18SQLCHAR20",\""17cDevModelSQL_Latin1_General_CP1_CI_AS
19SQLCHAR20",\""18cDevManufSQL_Latin1_General_CP1_CI_AS
20SQLCHAR20",\""19cDurationSQL_Latin1_General_CP1_CI_AS
21SQLCHAR20",\""20cDestinationSQL_Latin1_General_CP1_CI_AS
22SQLCHAR20",\""21cFullNameSQL_Latin1_General_CP1_CI_AS
23SQLCHAR20",\""22cPrimaryPINSQL_Latin1_General_CP1_CI_AS
24SQLCHAR20",\""23cWorkstationSQL_Latin1_General_CP1_CI_AS
25SQLCHAR20"\","24cAmountSQL_Latin1_General_CP1_CI_AS
26SQLCHAR20",\""25JobPropertiesSQL_Latin1_General_CP1_CI_AS
27SQLCHAR20"\","26cAltCostSQL_Latin1_General_CP1_CI_AS
28SQLCHAR20",\""27cDepartmentSQL_Latin1_General_CP1_CI_AS
29SQLCHAR20",\""28cBillableSQL_Latin1_General_CP1_CI_AS
30SQLCHAR20"\"\r"29cAlternatePinSQL_Latin1_General_CP1_CI_AS
--=== BLANK LINE
MASTER..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 2
Bulk 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 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The 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 2
Cannot 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 EQUIT
FROM ''' + @CAMINHO + '''
WITH (FIELDTERMINATOR = '','', CODEPAGE =''ACP'', ROWTERMINATOR='''')'
EXEC (@SQL)
The files used are attached.
Thanks in advance!
December 29, 2012 at 8:21 pm
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.
Hi Jeff,
Thanks for your response. I just uploaded the statements and the sample files again.
December 29, 2012 at 9:36 pm
urso47 (12/29/2012)
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 EQUIT
USE TEMPDB
GO
CREATE TABLE Equit
(
cGroupIDINT,
cGroupIDBillableINT,
cGroupNameVARCHAR(MAX),
cGroupDescriptionVARCHAR(MAX),
cRowIDINT,
cRowNameVARCHAR(MAX),
cRowDescriptionVARCHAR(MAX),
cTransactDateSMALLDATETIME,
cTransactTypeVARCHAR(MAX),
cChargeAccountIDVARCHAR(MAX),
cChargeAccountTypeVARCHAR(MAX),
cUserWhoPrintedVARCHAR(MAX),
cDocumentNameVARCHAR(MAX),
cUnitsINT,
cDocumentIDVARCHAR(MAX),
cDeviceIPVARCHAR(MAX),
cDevModelVARCHAR(MAX),
cDevManufVARCHAR(MAX),
cDurationVARCHAR(MAX),
cDestinationVARCHAR(MAX),
cFullNameVARCHAR(MAX),
cPrimaryPINVARCHAR(MAX),
cWorkstationVARCHAR(MAX),
cAmountINT,
JobPropertiesVARCHAR(MAX),
cAltCostINT,
cDepartmentVARCHAR(MAX),
cBillableVARCHAR(MAX),
cAlternatePinVARCHAR(MAX)
)
GO
-- FORMAT FILE FORMAT.EQUIT.FMT
10.0
30
1SQLCHAR00"\""0LineSQL_Latin1_General_CP1_CI_AS
2SQLCHAR00"\","1cGroupIDSQL_Latin1_General_CP1_CI_AS
3SQLCHAR00"\","2cGroupIDBillableSQL_Latin1_General_CP1_CI_AS
4SQLCHAR20",\""3cGroupNameSQL_Latin1_General_CP1_CI_AS
5SQLCHAR20",\""4cGroupDescriptionSQL_Latin1_General_CP1_CI_AS
6SQLCHAR20",\""5cRowIDSQL_Latin1_General_CP1_CI_AS
7SQLCHAR20",\""6cRowNameSQL_Latin1_General_CP1_CI_AS
8SQLCHAR20",\""7cRowDescriptionSQL_Latin1_General_CP1_CI_AS
9SQLCHAR20",\""8cTransactDateSQL_Latin1_General_CP1_CI_AS
10SQLCHAR20",\""9cTransactTypeSQL_Latin1_General_CP1_CI_AS
11SQLCHAR20",\""10cChargeAccountIDSQL_Latin1_General_CP1_CI_AS
12SQLCHAR20",\""11cChargeAccountTypeSQL_Latin1_General_CP1_CI_AS
13SQLCHAR20",\""12cUserWhoPrintedSQL_Latin1_General_CP1_CI_AS
14SQLCHAR20",\""13cDocumentNameSQL_Latin1_General_CP1_CI_AS
15SQLCHAR20"\","14cUnitsSQL_Latin1_General_CP1_CI_AS
16SQLCHAR20",\""15cDocumentIDSQL_Latin1_General_CP1_CI_AS
17SQLCHAR20",\""16cDeviceIPSQL_Latin1_General_CP1_CI_AS
18SQLCHAR20",\""17cDevModelSQL_Latin1_General_CP1_CI_AS
19SQLCHAR20",\""18cDevManufSQL_Latin1_General_CP1_CI_AS
20SQLCHAR20",\""19cDurationSQL_Latin1_General_CP1_CI_AS
21SQLCHAR20",\""20cDestinationSQL_Latin1_General_CP1_CI_AS
22SQLCHAR20",\""21cFullNameSQL_Latin1_General_CP1_CI_AS
23SQLCHAR20",\""22cPrimaryPINSQL_Latin1_General_CP1_CI_AS
24SQLCHAR20",\""23cWorkstationSQL_Latin1_General_CP1_CI_AS
25SQLCHAR20"\","24cAmountSQL_Latin1_General_CP1_CI_AS
26SQLCHAR20",\""25JobPropertiesSQL_Latin1_General_CP1_CI_AS
27SQLCHAR20"\","26cAltCostSQL_Latin1_General_CP1_CI_AS
28SQLCHAR20",\""27cDepartmentSQL_Latin1_General_CP1_CI_AS
29SQLCHAR20",\""28cBillableSQL_Latin1_General_CP1_CI_AS
30SQLCHAR20"\"\r"29cAlternatePinSQL_Latin1_General_CP1_CI_AS
--=== BLANK LINE
MASTER..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 2
Bulk 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 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The 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 2
Cannot 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 EQUIT
FROM ''' + @CAMINHO + '''
WITH (FIELDTERMINATOR = '','', CODEPAGE =''ACP'', ROWTERMINATOR='''')'
EXEC (@SQL)
The files used are attached.
Thanks in advance!
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 29, 2012 at 9:56 pm
I made some changes in the formatfle but still not working 🙁 ...
10.0
29
1SQLCHAR012"\","1cGroupID""
2SQLCHAR012"\","2cGroupIDBillable""
3SQLCHAR00",\""3cGroupNameLatin1_General_CI_AS
4SQLCHAR00"\","4cGroupDescriptionLatin1_General_CI_AS
5SQLCHAR012"\","5cRowID""
6SQLCHAR00",\""6cRowNameLatin1_General_CI_AS
7SQLCHAR00",\""7cRowDescriptionLatin1_General_CI_AS
8SQLCHAR024"\","8cTransactDate""
9SQLCHAR00",\""9cTransactTypeLatin1_General_CI_AS
10SQLCHAR00",\""10cChargeAccountIDLatin1_General_CI_AS
11SQLCHAR00",\""11cChargeAccountTypeLatin1_General_CI_AS
12SQLCHAR00",\""12cUserWhoPrintedLatin1_General_CI_AS
13SQLCHAR00",\""13cDocumentNameLatin1_General_CI_AS
14SQLCHAR012"\","14cUnits""
15SQLCHAR00",\""15cDocumentIDLatin1_General_CI_AS
16SQLCHAR00",\""16cDeviceIPLatin1_General_CI_AS
17SQLCHAR00",\""17cDevModelLatin1_General_CI_AS
18SQLCHAR00",\""18cDevManufLatin1_General_CI_AS
19SQLCHAR00",\""19cDurationLatin1_General_CI_AS
20SQLCHAR00",\""20cDestinationLatin1_General_CI_AS
21SQLCHAR00",\""21cFullNameLatin1_General_CI_AS
22SQLCHAR00",\""22cPrimaryPINLatin1_General_CI_AS
23SQLCHAR00",\""23cWorkstationLatin1_General_CI_AS
24SQLCHAR012"\","24cAmount""
25SQLCHAR00",\""25JobPropertiesLatin1_General_CI_AS
26SQLCHAR012"\","26cAltCost""
27SQLCHAR00",\""27cDepartmentLatin1_General_CI_AS
28SQLCHAR00",\""28cBillableLatin1_General_CI_AS
29SQLCHAR00"\"\r"29cAlternatePinLatin1_General_CI_AS
December 29, 2012 at 9:59 pm
Thanks Jeff, I will wait for your always helpfull examples then!
December 29, 2012 at 11:51 pm
Ok... 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.
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 variables
DECLARE @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.FileLog
GO
Here's a couple of example runs...
-----------------------------------------------------------------------------------------------------------------------
--===== 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
With that and the previous example for the files that have headers, you should be able to handle the rest.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2012 at 10:25 am
Hi 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:
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 2
Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".
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:
Msg 208, Level 16, State 0, Line 1 Invalid object name '#eq_Staging'.
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
"Message: Command(s) completed successfully."
but I still can't find them in my DB Object Explorer (in my DB objects tree).
I ran also
CREATE 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!!!
December 30, 2012 at 10:08 pm
I 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.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2012 at 8:00 am
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!
December 31, 2012 at 4:55 pm
Hi Jeff,
First of all, Happy New Year! Out here it still 21:55... Hope you are having a great time with your family!
Based on the first file you created for me (treating the header), I made some changes (including the path) for using the real file (atached). When I run "EXEC dbo.GetFileType01 'C:\temp\sm\urso47Test02.csv'" (the real file now is "02"), I get the error message:
Msg 50000, Level 18, State 1, Procedure GetFileType01, Line 123
ERROR:[GetFileType01]Incorrect header for file C:\temp\sm\urso47Test02.csv
Acording with CTRL+G = 123, the error is somewhere out here:
RAISERROR('ERROR:[%s]Incorrect header for file %s',18,1,@ProcName,@pFullPath);
Maybe one critical change I did should be replacing ":" by "." because I don't have the colon sign at the first header line:
WHEN RowNum = 1 AND LineData LIKE '[#Lista Estat].%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX('.',LineData)+1,8000)))
I didn't make any changes in the formatfile. Here goes the statement changed:
CREATE PROCEDURE dbo.GetFileType01
/**********************************************************************************************************************
Purpose:
Give a full path/filename/extension, load the file according to the specs for "FileType01".
Usage Example:
EXEC dbo.GetFileType01 'C:\Temp\urso47Test01.txt'
Dependencies:
1. The related "BCP Format File" (RawData8000.fmt) must be located in "C:\Temp".
Obviously, you can change that.
2. The dbo.DelimitedSplitFunction must exist in the same database as this stored procedure.
It carries its own documentation
Programmers Notes:
1. Some minor error checking with the file is done. You can easily enhance the simple error checking to be full
validation, at some point.
Revision History
Rev 00 - 16 Dec 2012 - Jeff Moden - Initial creation/test
Ref - http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx
**********************************************************************************************************************/
--===== Declare the input parameters for this stored procedure
@pFullPath VARCHAR(500)
AS
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Setup the environment just to be sure
SET NOCOUNT ON;
SET DATEFORMAT MDY;
--===== Create the staging table for the raw data
CREATE TABLE #RawData
(
RowNum INT IDENTITY(1,1),
LineData VARCHAR(8000)
)
;
--===== Create some obviously named variables
DECLARE @ProcName VARCHAR(128),
@RowCount INT,
@sql VARCHAR(8000)
;
--===== Preset variables with constants
SELECT @ProcName = OBJECT_NAME(@@PROCID),
@sql = REPLACE('
BULK INSERT #RawData
FROM ''<<@pFullPath>>''
WITH (
BATCHSIZE = 2000000000,
CODEPAGE = ''RAW'',
DATAFILETYPE = ''char'',
--ERRORFILE = ''<<@pFullPath>>'', --Uncomment this line if you want to capture error rows
FIELDTERMINATOR = ''\t'',
FORMATFILE = ''C:\Temp\sm\RawData8000.fmt'',
MAXERRORS = 2000000000,
ROWTERMINATOR = '''',
TABLOCK
);'
,'<<@pFullPath>>',@pFullPath)
;
--===== Load the file for parsing using the format file
EXEC (@SQL)
;
--===== Parse and save the header information using a "CROSSTAB" to pivot the data
SELECT [#Lista Estat] =
MAX(
CASE
WHEN RowNum = 1 AND LineData LIKE '[#Lista Estat].%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX('.',LineData)+1,8000)))
ELSE ''
END
),
[#Format Version] =
MAX(
CASE
WHEN RowNum = 2 AND LineData LIKE '[Format Version]:%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
ELSE ''
END
),
[#Data Obtida] =
CAST(MAX(
CASE
WHEN RowNum = 3 AND LineData LIKE '[#Data Obtida]:%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
ELSE ''
END
) AS DATETIME),
[#Nome do Disposit.] =
MAX(
CASE
WHEN RowNum = 4 AND LineData LIKE '[#Nome do Disposit.]:%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
ELSE ''
END
),
[#Endereço] =
MAX(
CASE
WHEN RowNum = 5 AND LineData LIKE '[#Endereço]:%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
ELSE ''
END
)
INTO #Header
FROM #RawData
WHERE RowNum BETWEEN 1 AND 5
;
--===== Check to make sure the header was loaded correctly.
-- Raise an error and exit if it wasn't
SELECT @RowCount = COUNT(*)
FROM #Header
WHERE [#Lista Estat]> '' --Is Not Blank or NULL
AND [#Format Version]> '' --Is Not Blank or NULL
AND [#Data Obtida]> '1900' --Wasn't an "empty" date (and assumes no dates before 1900-01-01)
AND [#Nome do Disposit.]> '' --Is Not Blank or NULL
AND [#Endereço]> '' --Is Not Blank or NULL
;
IF @RowCount <> 1
BEGIN
RAISERROR('ERROR:[%s]Incorrect header for file %s',18,1,@ProcName,@pFullPath);
RETURN;
END
;
--===== Check to make sure the detail header is at row 7.
-- Raise an error and exit if it isn't.
IF NOT EXISTS (SELECT 1 FROM #RawData WHERE RowNum = 7 AND LineData = '[#Utilizador],[Nome],[Tot. de Impressões],[P & B(Tot. de Impressões)],[Cor(Tot. de Impressões)],[P & B: Resultado(Tot. de Impressões)],[Cor: Resultado(Tot. de Impressões)],[Total de Preto e branco(Copidora/Servidor de Documentos)],[Preto e branco(Formato pequeno)(Copidora/Servidor de Documentos)],[Preto e branco(Formato grande)(Copidora/Servidor de Documentos)],[Total de Uma cor(Copidora/Servidor de Documentos)],[Cor Única(Formato pequeno)(Copidora/Servidor de Documentos)],[Cor Única(Formato grande)(Copidora/Servidor de Documentos)],[Total de Duas cores(Copidora/Servidor de Documentos)],[Duas cores(Formato pequeno)(Copidora/Servidor de Documentos)],[Duas cores(Formato grande)(Copidora/Servidor de Documentos)],[Total de Cor integral(Copidora/Servidor de Documentos)],[Cor Integral(Formato pequeno)(Copidora/Servidor de Documentos)],[Cor Integral(Formato grande)(Copidora/Servidor de Documentos)],[Total de Preto e branco(Impressora)],[Preto e branco(Formato pequeno)(Impressora)],[Preto e branco(Formato grande)(Impressora)],[Total de Uma cor(Impressora)],[Cor Única(Formato pequeno)(Impressora)],[Cor Única(Formato grande)(Impressora)],[Total de Duas cores(Impressora)],[Duas cores(Formato pequeno)(Impressora)],[Duas cores(Formato grande)(Impressora)],[Total a Cores(Impressora)],[Cor(Formato pequeno)(Impressora)],[Cor(Formato grande)(Impressora)],[Total de Scanner(Scanner)],[Total de Preto e branco(Scanner)],[Preto e branco(Formato pequeno)(Scanner)],[Preto e branco(Formato grande)(Scanner)],[Total a Cores(Scanner)],[Cor(Formato pequeno)(Scanner)],[Cor(Formato grande)(Scanner)],[Total de Preto e branco(Fax)],[Preto e branco(Formato pequeno)(Fax)],[Preto e branco(Formato grande)(Fax)],[Total a Cores(Fax)],[Cor(Formato pequeno)(Fax)],[Cor(Formato grande)(Fax)],[Transmissão(Fax)],[Custos(Fax)],[Volume utilizado(Limitação de volumes de utilização para impressão)],[Valor limite(Limitação de volumes de utilização para impressão)]
')
BEGIN
RAISERROR('ERROR:[%s] Misplaced detail-header for file %s',16,1,@ProcName, @pFullPath);
RETURN;
END
;
--===== Load and return the detail rows 114
SELECT [#Lista Estat] = MAX(hdr.[#Lista Estat]),
[#Format Version] = MAX(hdr.[#Format Version]),
[#Data Obtida] = MAX(hdr.[#Data Obtida]),
[#Nome do Disposit.] = MAX(hdr.[#Nome do Disposit.]),
[#Endereço] = MAX(hdr.[#Endereço]),
[#Utilizador] = MAX(CAST(CASE WHEN split.ItemNumber = 1THEN Item ELSE '' END AS VARCHAR(10))),
[Nome] = MAX(CAST(CASE WHEN split.ItemNumber = 2THEN Item ELSE '' END AS VARCHAR(10))),
[Tot. de Impressões] = MAX(CAST(CASE WHEN split.ItemNumber = 3THEN Item ELSE '' END AS INT)),
[P & B(Tot. de Impressões)] = MAX(CAST(CASE WHEN split.ItemNumber = 4THEN Item ELSE '' END AS INT)),
[Cor(Tot. de Impressões)] = MAX(CAST(CASE WHEN split.ItemNumber = 5THEN Item ELSE '' END AS INT)),
[P & B: Resultado(Tot. de Impressões)] = MAX(CAST(CASE WHEN split.ItemNumber = 6THEN Item ELSE '' END AS INT)),
[Cor: Resultado(Tot. de Impressões)] = MAX(CAST(CASE WHEN split.ItemNumber = 7THEN Item ELSE '' END AS INT)),
[Total de Preto e branco(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 8THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato pequeno)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 9THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato grande)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 10THEN Item ELSE '' END AS INT)),
[Total de Uma cor(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 11THEN Item ELSE '' END AS INT)),
[Cor Única(Formato pequeno)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 12THEN Item ELSE '' END AS INT)),
[Cor Única(Formato grande)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 13THEN Item ELSE '' END AS INT)),
[Total de Duas cores(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 14THEN Item ELSE '' END AS INT)),
[Duas cores(Formato pequeno)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 15THEN Item ELSE '' END AS INT)),
[Duas cores(Formato grande)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 16THEN Item ELSE '' END AS INT)),
[Total de Cor integral(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 17 THEN Item ELSE '' END AS INT)),
[Cor Integral(Formato pequeno)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 18THEN Item ELSE '' END AS INT)),
[Cor Integral(Formato grande)(Copidora/Servidor de Documentos)] = MAX(CAST(CASE WHEN split.ItemNumber = 19THEN Item ELSE '' END AS INT)),
[Total de Preto e branco(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 20THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato pequeno)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 21THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato grande)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 22THEN Item ELSE '' END AS INT)),
[Total de Uma cor(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 23THEN Item ELSE '' END AS INT)),
[Cor Única(Formato pequeno)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 24THEN Item ELSE '' END AS INT)),
[Cor Única(Formato grande)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 25THEN Item ELSE '' END AS INT)),
[Total de Duas cores(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 26THEN Item ELSE '' END AS INT)),
[Duas cores(Formato pequeno)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 27THEN Item ELSE '' END AS INT)),
[Duas cores(Formato grande)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 28THEN Item ELSE '' END AS INT)),
[Total a Cores(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 29THEN Item ELSE '' END AS INT)),
[Cor(Formato pequeno)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 30THEN Item ELSE '' END AS INT)),
[Cor(Formato grande)(Impressora)] = MAX(CAST(CASE WHEN split.ItemNumber = 31THEN Item ELSE '' END AS INT)),
[Total de Scanner(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 32THEN Item ELSE '' END AS INT)),
[Total de Preto e branco(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 33THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato pequeno)(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 34THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato grande)(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 35THEN Item ELSE '' END AS INT)),
[Total a Cores(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 36THEN Item ELSE '' END AS INT)),
[Cor(Formato pequeno)(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 37THEN Item ELSE '' END AS INT)),
[Cor(Formato grande)(Scanner)] = MAX(CAST(CASE WHEN split.ItemNumber = 38THEN Item ELSE '' END AS INT)),
[Total de Preto e branco(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 39THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato pequeno)(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 40THEN Item ELSE '' END AS INT)),
[Preto e branco(Formato grande)(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 41THEN Item ELSE '' END AS INT)),
[Total a Cores(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 42THEN Item ELSE '' END AS INT)),
[Cor(Formato pequeno)(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 43THEN Item ELSE '' END AS INT)),
[Cor(Formato grande)(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 44THEN Item ELSE '' END AS INT)),
[Transmissão(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 45THEN Item ELSE '' END AS INT)),
[Custos(Fax)] = MAX(CAST(CASE WHEN split.ItemNumber = 46THEN Item ELSE '' END AS INT)),
[Volume utilizado(Limitação de volumes de utilização para impressão)] = MAX(CAST(CASE WHEN split.ItemNumber = 47THEN Item ELSE '' END AS INT)),
[Valor limite(Limitação de volumes de utilização para impressão)] = MAX(CAST(CASE WHEN split.ItemNumber = 48THEN Item ELSE '' END AS INT))
FROM #RawData rd
CROSS JOIN #Header hdr --There's only one header so this works
CROSS APPLY ( --=== This parses the CSV in each row and removes the braces (every kid's dream ;-))
SELECT ItemNumber,
Item = REPLACE(REPLACE(Item,'[','') ,']','')
FROM dbo.DelimitedSplit8K(LineData,',')
) split
WHERE rd.RowNum > 7 --Data header is at row 7
AND LEN(LineData) > 1 --Gets rid of blank rows
GROUP BY rd.RowNum --Completes the pivot
;
GO
By the way for my level, this script is hard to understand, what level is this in the SQL world?
Thanks again!
December 31, 2012 at 5:47 pm
urso47 (12/31/2012)
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:Msg 50000, Level 18, State 1, Procedure GetFileType01, Line 123
ERROR:[GetFileType01]Incorrect header for file C:\temp\sm\urso47Test02.csv
Acording with CTRL+G = 123, the error is somewhere out here:
RAISERROR('ERROR:[%s]Incorrect header for file %s',18,1,@ProcName,@pFullPath);
Maybe one critical change I did should be replacing ":" by "." because I don't have the colon sign at the first header line:
WHEN RowNum = 1 AND LineData LIKE '[#Lista Estat].%'
THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX('.',LineData)+1,8000)))
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. 😉
By the way for my level, this script is hard to understand, what level is this in the SQL world?
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. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 31, 2012 at 11:14 pm
As 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:
Msg 3701, Level 11, State 5, Line 1
Msg 102, Level 15, State 1, Procedure GetFileType01, Line 138
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure GetFileType01, Line 197
Incorrect syntax near 'split'.
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!
January 1, 2013 at 1:34 am
My last try:
DROP PROCEDURE dbo.GetFileType01
GO
CREATE PROCEDURE dbo.GetFileType01
/**********************************************************************************************************************
Purpose:
Give a full path/filename/extension, load the file according to the specs for "FileType01".
Usage Example:
EXEC dbo.GetFileType01 'C:\Temp\urso47Test01.txt'
Dependencies:
1. The related "BCP Format File" (RawData8000.fmt) must be located in "C:\Temp".
Obviously, you can change that.
2. The dbo.DelimitedSplitFunction must exist in the same database as this stored procedure.
It carries its own documentation
Programmers Notes:
1. Some minor error checking with the file is done. You can easily enhance the simple error checking to be full
validation, at some point.
Revision History
Rev 00 - 16 Dec 2012 - Jeff Moden - Initial creation/test
Ref - http://www.sqlservercentral.com/Forums/Topic1396968-1292-1.aspx
**********************************************************************************************************************/
--===== Declare the input parameters for this stored procedure
@pFullPath VARCHAR(500)
AS
--=====================================================================================================================
-- Presets
--=====================================================================================================================
--===== Setup the environment just to be sure
SET NOCOUNT ON;
SET DATEFORMAT MDY;
--===== Create the staging table for the raw data
CREATE TABLE #RawData
(
RowNum INT IDENTITY(1,1),
LineData VARCHAR(8000)
)
;
--===== Create some obviously named variables
DECLARE @ProcName VARCHAR(128),
@RowCount INT,
@sql VARCHAR(8000)
;
--===== Preset variables with constants
SELECT @ProcName = OBJECT_NAME(@@PROCID),
@sql = REPLACE('
BULK INSERT #RawData
FROM ''<<@pFullPath>>''
WITH (
BATCHSIZE = 2000000000,
CODEPAGE = ''RAW'',
DATAFILETYPE = ''char'',
--ERRORFILE = ''<<@pFullPath>>'', --Uncomment this line if you want to capture error rows
FIELDTERMINATOR = ''\t'',
FORMATFILE = ''C:\Temp\sm\RawData8000.fmt'',
MAXERRORS = 2000000000,
ROWTERMINATOR = '''',
TABLOCK
);'
,'<<@pFullPath>>',@pFullPath)
;
--===== Load the file for parsing using the format file
EXEC (@SQL)
;
--===== Parse and save the header information using a "CROSSTAB" to pivot the data
SELECT #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 = 2THEN Item ELSE '' END AS VARCHAR(100))),
Tot. de Impressões = MAX(CAST(CASE WHEN split.ItemNumber = 3THEN Item ELSE '' END AS INT)),
P & B(Tot. de Impressões)= MAX(CAST(CASE WHEN split.ItemNumber = 4THEN Item ELSE '' END AS INT)),
Cor(Tot. de Impressões)= MAX(CAST(CASE WHEN split.ItemNumber = 5THEN Item ELSE '' END AS INT)),
P & B: Resultado(Tot. de Impressões)= MAX(CAST(CASE WHEN split.ItemNumber = 6THEN Item ELSE '' END AS INT)),
[Cor: Resultado(Tot. de Impressões)] = MAX(CAST(CASE WHEN split.ItemNumber = 7THEN Item ELSE '' END AS INT)),
Total de Preto e branco(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 8THEN Item ELSE '' END AS INT)),
Preto e branco(Formato pequeno)(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 9THEN Item ELSE '' END AS INT)),
Preto e branco(Formato grande)(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 10THEN Item ELSE '' END AS INT)),
Total de Uma cor(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 11THEN Item ELSE '' END AS INT)),
Cor Única(Formato pequeno)(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 12THEN Item ELSE '' END AS INT)),
Cor Única(Formato grande)(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 13THEN Item ELSE '' END AS INT)),
Total de Duas cores(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 14THEN Item ELSE '' END AS INT)),
Duas cores(Formato pequeno)(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 15THEN Item ELSE '' END AS INT)),
Duas cores(Formato grande)(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 16THEN Item ELSE '' END AS INT)),
Total de Cor integral(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 17 THEN Item ELSE '' END AS INT)),
Cor Integral(Formato pequeno)(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 18THEN Item ELSE '' END AS INT)),
Cor Integral(Formato grande)(Copidora/Servidor de Documentos) = MAX(CAST(CASE WHEN split.ItemNumber = 19THEN Item ELSE '' END AS INT)),
Total de Preto e branco(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 20THEN Item ELSE '' END AS INT)),
Preto e branco(Formato pequeno)(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 21THEN Item ELSE '' END AS INT)),
Preto e branco(Formato grande)(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 22THEN Item ELSE '' END AS INT)),
Total de Uma cor(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 23THEN Item ELSE '' END AS INT)),
Cor Única(Formato pequeno)(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 24THEN Item ELSE '' END AS INT)),
Cor Única(Formato grande)(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 25THEN Item ELSE '' END AS INT)),
Total de Duas cores(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 26THEN Item ELSE '' END AS INT)),
Duas cores(Formato pequeno)(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 27THEN Item ELSE '' END AS INT)),
Duas cores(Formato grande)(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 28THEN Item ELSE '' END AS INT)),
Total a Cores(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 29THEN Item ELSE '' END AS INT)),
Cor(Formato pequeno)(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 30THEN Item ELSE '' END AS INT)),
Cor(Formato grande)(Impressora) = MAX(CAST(CASE WHEN split.ItemNumber = 31THEN Item ELSE '' END AS INT)),
Total de Scanner(Scanner) = MAX(CAST(CASE WHEN split.ItemNumber = 32THEN Item ELSE '' END AS INT)),
Total de Preto e branco(Scanner) = MAX(CAST(CASE WHEN split.ItemNumber = 33THEN Item ELSE '' END AS INT)),
Preto e branco(Formato pequeno)(Scanner) = MAX(CAST(CASE WHEN split.ItemNumber = 34THEN Item ELSE '' END AS INT)),
Preto e branco(Formato grande)(Scanner) = MAX(CAST(CASE WHEN split.ItemNumber = 35THEN Item ELSE '' END AS INT)),
Total a Cores(Scanner) = MAX(CAST(CASE WHEN split.ItemNumber = 36THEN Item ELSE '' END AS INT)),
Cor(Formato pequeno)(Scanner) = MAX(CAST(CASE WHEN split.ItemNumber = 37THEN Item ELSE '' END AS INT)),
Cor(Formato grande)(Scanner) = MAX(CAST(CASE WHEN split.ItemNumber = 38THEN Item ELSE '' END AS INT)),
Total de Preto e branco(Fax) = MAX(CAST(CASE WHEN split.ItemNumber = 39THEN Item ELSE '' END AS INT)),
Preto e branco(Formato pequeno)(Fax) = MAX(CAST(CASE WHEN split.ItemNumber = 40THEN Item ELSE '' END AS INT)),
Preto e branco(Formato grande)(Fax) = MAX(CAST(CASE WHEN split.ItemNumber = 41THEN Item ELSE '' END AS INT)),
Total a Cores(Fax) = MAX(CAST(CASE WHEN split.ItemNumber = 42THEN Item ELSE '' END AS INT)),
Cor(Formato pequeno)(Fax) = MAX(CAST(CASE WHEN split.ItemNumber = 43THEN Item ELSE '' END AS INT)),
Cor(Formato grande)(Fax) = MAX(CAST(CASE WHEN split.ItemNumber = 44THEN Item ELSE '' END AS INT)),
Transmissão(Fax) = MAX(CAST(CASE WHEN split.ItemNumber = 45THEN Item ELSE '' END AS INT)),
Custos(Fax) = MAX(CAST(CASE WHEN split.ItemNumber = 46THEN Item ELSE '' END AS INT)),
Volume utilizado(Limitação de volumes de utilização para impressão) = MAX(CAST(CASE WHEN split.ItemNumber = 47THEN Item ELSE '' END AS INT)),
Valor limite(Limitação de volumes de utilização para impressão) = MAX(CAST(CASE WHEN split.ItemNumber = 48THEN Item ELSE '' END AS INT))
FROM #RawData rd
CROSS JOIN #Header hdr --There's only one header so this works
CROSS APPLY ( --=== This parses the CSV in each row and removes the braces (every kid's dream ;-))
SELECT ItemNumber,
Item = REPLACE(REPLACE(Item,'[','') ,']','')
FROM dbo.DelimitedSplit8K(LineData,',')
) split
WHERE rd.RowNum > 7 --Data header is at row 7
AND LEN(LineData) > 1 --Gets rid of blank rows
GROUP BY rd.RowNum --Completes the pivot
;
GO
Error:
Msg 3701, Level 11, State 5, Line 1
Cannot 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 144
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure GetFileType01, Line 196
Incorrect syntax near 'split'.
Line 144:
#Utilizador = MAX(CAST(CASE WHEN split.ItemNumber = 1 THEN Item ELSE '' END AS VARCHAR(100))),
Even after several tries, I still not able to fix it 🙁 ...
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply