Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Create Table and Bulk Insert Expand / Collapse
Author
Message
Posted Saturday, December 29, 2012 9:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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
(
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.FMT
10.0
30
1 SQLCHAR 0 0 "\"" 0 Line SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 "\"," 1 cGroupID SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\"," 2 cGroupIDBillable SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 0 ",\"" 3 cGroupName SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 0 ",\"" 4 cGroupDescription SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 0 ",\"" 5 cRowID SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 0 ",\"" 6 cRowName SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 0 ",\"" 7 cRowDescription SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 2 0 ",\"" 8 cTransactDate SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 2 0 ",\"" 9 cTransactType SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 2 0 ",\"" 10 cChargeAccountID SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 2 0 ",\"" 11 cChargeAccountType SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 2 0 ",\"" 12 cUserWhoPrinted SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 2 0 ",\"" 13 cDocumentName SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 2 0 "\"," 14 cUnits SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 2 0 ",\"" 15 cDocumentID SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 2 0 ",\"" 16 cDeviceIP SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 2 0 ",\"" 17 cDevModel SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 2 0 ",\"" 18 cDevManuf SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 2 0 ",\"" 19 cDuration SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 2 0 ",\"" 20 cDestination SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 2 0 ",\"" 21 cFullName SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 2 0 ",\"" 22 cPrimaryPIN SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 2 0 ",\"" 23 cWorkstation SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 2 0 "\"," 24 cAmount SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 2 0 ",\"" 25 JobProperties SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 2 0 "\"," 26 cAltCost SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 2 0 ",\"" 27 cDepartment SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 2 0 ",\"" 28 cBillable SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 2 0 "\"\r" 29 cAlternatePin SQL_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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401226
Posted Saturday, December 29, 2012 9:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 2:01 PM
Points: 29, Visits: 239
I 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_AS
4 SQLCHAR 0 0 "\"," 4 cGroupDescription Latin1_General_CI_AS
5 SQLCHAR 0 12 "\"," 5 cRowID ""
6 SQLCHAR 0 0 ",\"" 6 cRowName Latin1_General_CI_AS
7 SQLCHAR 0 0 ",\"" 7 cRowDescription Latin1_General_CI_AS
8 SQLCHAR 0 24 "\"," 8 cTransactDate ""
9 SQLCHAR 0 0 ",\"" 9 cTransactType Latin1_General_CI_AS
10 SQLCHAR 0 0 ",\"" 10 cChargeAccountID Latin1_General_CI_AS
11 SQLCHAR 0 0 ",\"" 11 cChargeAccountType Latin1_General_CI_AS
12 SQLCHAR 0 0 ",\"" 12 cUserWhoPrinted Latin1_General_CI_AS
13 SQLCHAR 0 0 ",\"" 13 cDocumentName Latin1_General_CI_AS
14 SQLCHAR 0 12 "\"," 14 cUnits ""
15 SQLCHAR 0 0 ",\"" 15 cDocumentID Latin1_General_CI_AS
16 SQLCHAR 0 0 ",\"" 16 cDeviceIP Latin1_General_CI_AS
17 SQLCHAR 0 0 ",\"" 17 cDevModel Latin1_General_CI_AS
18 SQLCHAR 0 0 ",\"" 18 cDevManuf Latin1_General_CI_AS
19 SQLCHAR 0 0 ",\"" 19 cDuration Latin1_General_CI_AS
20 SQLCHAR 0 0 ",\"" 20 cDestination Latin1_General_CI_AS
21 SQLCHAR 0 0 ",\"" 21 cFullName Latin1_General_CI_AS
22 SQLCHAR 0 0 ",\"" 22 cPrimaryPIN Latin1_General_CI_AS
23 SQLCHAR 0 0 ",\"" 23 cWorkstation Latin1_General_CI_AS
24 SQLCHAR 0 12 "\"," 24 cAmount ""
25 SQLCHAR 0 0 ",\"" 25 JobProperties Latin1_General_CI_AS
26 SQLCHAR 0 12 "\"," 26 cAltCost ""
27 SQLCHAR 0 0 ",\"" 27 cDepartment Latin1_General_CI_AS
28 SQLCHAR 0 0 ",\"" 28 cBillable Latin1_General_CI_AS
29 SQLCHAR 0 0 "\"\r" 29 cAlternatePin Latin1_General_CI_AS
Post #1401227
Posted Saturday, December 29, 2012 9:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 2:01 PM
Points: 29, Visits: 239
Thanks Jeff, I will wait for your always helpfull examples then!
Post #1401228
Posted Saturday, December 29, 2012 11:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401231
Posted Sunday, December 30, 2012 10:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 2:01 PM
Points: 29, Visits: 239
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!!!
Post #1401260
Posted Sunday, December 30, 2012 10:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401286
Posted Monday, December 31, 2012 8:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 2:01 PM
Points: 29, Visits: 239
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!
Post #1401396
Posted Monday, December 31, 2012 4:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 2:01 PM
Points: 29, Visits: 239
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 = 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
;
GO


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

Thanks again!


  Post Attachments 
urso47Test02.zip (3 views, 880 bytes)
Post #1401528
Posted Monday, December 31, 2012 5:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1401532
Posted Monday, December 31, 2012 11:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 24, 2013 2:01 PM
Points: 29, Visits: 239
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!
Post #1401554
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse