Notes

USE DirectCertificationSystem;


--Outer Cursor Varibales
DECLARE @iControlID INT;
DECLARE @vcFULL_FILE_NAME VARCHAR(500);
DECLARE @vcFILE_NAME VARCHAR(100);

SELECT @iControlID = [CONTROL_PK],
@vcFULL_FILE_NAME = [RENAMED_FILE_NAME],
@vcFILE_NAME = [FILE_NAME]
FROM [dbo].[STUDENT_CONTROL] WITH (NOLOCK)
WHERE CONTROL_PK = 220
AND [APPLICATION_YEAR_FK] = [dbo].[udf_GetCurrentYear]()
ORDER BY [CONTROL_PK];

--SELECT @vcFULL_FILE_NAME


DECLARE @vcMessage VARCHAR(80);


--Flat File Cursor Variables
DECLARE @FromFlatFile VARCHAR(500);

--CSV Cursor Variables
DECLARE @vcCOUNTY_NUMBER VARCHAR(3),
@vcSPONSOR_NUMBER VARCHAR(15),
@vcSITE_NUMBER VARCHAR(5),
@vcSSN VARCHAR(9),
@vcBIRTH_DATE VARCHAR(8),
@vcLAST_NAME VARCHAR(30),
@vcFIRST_NAME VARCHAR(30),
@vcMIDDLE_NAME VARCHAR(30),
@vcGENDER VARCHAR(1),
@vcRACE VARCHAR(1),
@vcSASID VARCHAR(10),
@vcFL_COMMON_ID VARCHAR(14),
@vcADDRESS_1 VARCHAR(30),
@vcADDRESS_2 VARCHAR(30),
@vcCITY VARCHAR(30),
@vcSTATE VARCHAR(2),
@vcZIP VARCHAR(5),
@vcZIP4 VARCHAR(4),
@vcLOCAL_ID VARCHAR(15);

--General Variables
DECLARE @vcFILE_TYPE VARCHAR(4);
DECLARE @vcSQL VARCHAR(MAX);
DECLARE @RecordSequence INT;
DECLARE @bFileOK INT;
DECLARE @iFileStatus INT;

--Error Handling
DECLARE @iError INT;
SELECT @iError = 0;


--CHECK IF FILE IS PRESENT IN THE LANDING AREA
EXEC xp_fileexist @vcFULL_FILE_NAME, @bFileOK OUTPUT;
IF @bFileOK = 1
BEGIN
BEGIN TRY

--DROP THE XLImport TABLE IF IT ALREADY EXISTS
IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[XLImport]')
AND OBJECTPROPERTY(id, N'IsTable') = 1
)
BEGIN
DROP TABLE [dbo].[XLImport];
END;

--DROP THE TEMP TABLE IF IT ALREADY EXISTS
IF OBJECT_ID('tempdb..#tempExcelData') IS NOT NULL
BEGIN
DROP TABLE #tempExcelData;
END;

--CREATE TEMP TABLE
CREATE TABLE #tempExcelData
(
[COUNTY_NUMBER] VARCHAR(2),
[SPONSOR_NUMBER] VARCHAR(8),
[SITE_NUMBER] VARCHAR(8),
[SSN] VARCHAR(9),
[BIRTH_DATE] VARCHAR(8),
[LAST_NAME] VARCHAR(30),
[FIRST_NAME] VARCHAR(30),
[MIDDLE_NAME] VARCHAR(30),
[GENDER] VARCHAR(1),
[RACE] VARCHAR(1),
[SASID] VARCHAR(10),
[LOCAL_ID] VARCHAR(15),
[ADDRESS_1] VARCHAR(30),
[ADDRESS_2] VARCHAR(30),
[CITY] VARCHAR(30),
[STATE] VARCHAR(2),
[ZIP] VARCHAR(5)
);

SELECT @vcFULL_FILE_NAME
BEGIN TRY
SET @vcSQL = 'SELECT * INTO XLImport FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',
''Data Source=' + @vcFULL_FILE_NAME + ';Extended Properties=Excel 8.0'')...[StudentEnrollment$]';
--print @vcSQL
EXEC (@vcSQL);
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS errorMessage
END CATCH

SELECT 2

RETURN;


DECLARE @iColumnCount INT;
SET @iColumnCount =
(
SELECT COUNT(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'XLImport'
);

SELECT @iColumnCount;

RETURN;

IF @iColumnCount = 17
BEGIN
INSERT INTO #tempExcelData
SELECT LTRIM(RTRIM(REPLACE([COUNTY_NUMBER], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([SPONSOR_NUMBER], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([SITE_NUMBER], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([SSN], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([BIRTH_DATE], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([LAST_NAME], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([FIRST_NAME], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([MIDDLE_NAME], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([GENDER], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([RACE], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([SASID], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([LOCAL_ID], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([ADDRESS_1], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([ADDRESS_2], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([CITY], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([STATE], CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE([ZIP], CHAR(9), ' ')))
--,LTRIM(RTRIM(REPLACE([ZIP4],CHAR(9),' ')))
FROM XLImport;

--SELECT * FROM #tempExcelData;
END;
ELSE
BEGIN
PRINT 'INVALID FILE FORMAT';
SET @iError = 1;
END;
END TRY
BEGIN CATCH
SET @iError = 1;

--DROP THE XLImport TABLE IF IT ALREADY EXISTS
IF EXISTS
(
SELECT *
FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[XLImport]')
AND OBJECTPROPERTY(id, N'IsTable') = 1
)
BEGIN
DROP TABLE [dbo].[XLImport];
END;

--DROP THE TEMP TABLE IF IT ALREADY EXISTS
IF OBJECT_ID('tempdb..#tempExcelData') IS NOT NULL
BEGIN
DROP TABLE #tempExcelData;
END;
END CATCH;

IF @iError = 0
BEGIN

PRINT 'GOT IN';

SET @RecordSequence = 0;

DECLARE cExcelParseData CURSOR FOR
SELECT [COUNTY_NUMBER],
[SPONSOR_NUMBER],
[SITE_NUMBER],
[SSN],
[BIRTH_DATE],
[LAST_NAME],
[FIRST_NAME],
[MIDDLE_NAME],
[GENDER],
[RACE],
[SASID],
--,[FL_COMMON_ID]
[ADDRESS_1],
[ADDRESS_2],
[CITY],
[STATE],
[ZIP],
--,[ZIP4]
[LOCAL_ID]
FROM #tempExcelData;
OPEN cExcelParseData;
FETCH NEXT FROM cExcelParseData
INTO @vcCOUNTY_NUMBER,
@vcSPONSOR_NUMBER,
@vcSITE_NUMBER,
@vcSSN,
@vcBIRTH_DATE,
@vcLAST_NAME,
@vcFIRST_NAME,
@vcMIDDLE_NAME,
@vcGENDER,
@vcRACE,
@vcSASID,
@vcADDRESS_1,
@vcADDRESS_2,
@vcCITY,
@vcSTATE,
@vcZIP,
@vcLOCAL_ID;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @RecordSequence = @RecordSequence + 1;

INSERT INTO [STUDENT_STAGING]
(
[COUNTY_NUMBER],
[SPONSOR_NUMBER],
[SITE_NUMBER],
[SSN],
[BIRTH_DATE],
[LAST_NAME],
[FIRST_NAME],
[MIDDLE_INITIAL],
[GENDER],
[RACE],
[FL_ID],
--,[FL_COMMON_ID]
[APPLICATION_YEAR_FK],
[ADDRESS_1],
[ADDRESS_2],
[CITY],
[STATE],
[ZIP],
--,[ZIP4]
[STAGING_STATUS],
[CONTROL_FK],
[RECORD_SEQUENCE],
[ERROR_MESSAGE],
[CRE_USER],
[CRE_DATE],
[CRE_APP],
[LOCAL_ID]
)
VALUES
( LTRIM(RTRIM(REPLACE(@vcCOUNTY_NUMBER, CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE(@vcSPONSOR_NUMBER, CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE(@vcSITE_NUMBER, CHAR(9), ' '))), LTRIM(RTRIM(REPLACE(@vcSSN, CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE(@vcBIRTH_DATE, CHAR(9), ' '))), LTRIM(RTRIM(REPLACE(@vcLAST_NAME, CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE(@vcFIRST_NAME, CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE(@vcMIDDLE_NAME, CHAR(9), ' '))), LTRIM(RTRIM(REPLACE(@vcGENDER, CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE(@vcRACE, CHAR(9), ' '))), LTRIM(RTRIM(REPLACE(@vcSASID, CHAR(9), ' '))),
[dbo].[udf_GetCurrentYear](), LTRIM(RTRIM(REPLACE(@vcADDRESS_1, CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE(@vcADDRESS_2, CHAR(9), ' '))), LTRIM(RTRIM(REPLACE(@vcCITY, CHAR(9), ' '))),
LTRIM(RTRIM(REPLACE(@vcSTATE, CHAR(9), ' '))), LTRIM(RTRIM(REPLACE(@vcZIP, CHAR(9), ' '))),
--,LTRIM(RTRIM(REPLACE(@vcZIP4,CHAR(9),' ')))
1, -- INITIAL UPLOAD
@iControlID, @RecordSequence, '', 1, GETDATE(), 2, -- Batch Processing Application
LTRIM(RTRIM(REPLACE(@vcLOCAL_ID, CHAR(9), ' '))));

FETCH NEXT FROM cExcelParseData
INTO @vcCOUNTY_NUMBER,
@vcSPONSOR_NUMBER,
@vcSITE_NUMBER,
@vcSSN,
@vcBIRTH_DATE,
@vcLAST_NAME,
@vcFIRST_NAME,
@vcMIDDLE_NAME,
@vcGENDER,
@vcRACE,
@vcSASID,
@vcADDRESS_1,
@vcADDRESS_2,
@vcCITY,
@vcSTATE,
@vcZIP,
@vcLOCAL_ID;
END;

CLOSE cExcelParseData;
DEALLOCATE cExcelParseData;

DROP TABLE #tempExcelData;
DROP TABLE XLImport;

SET @iFileStatus = 2;
END;
END;
ELSE
BEGIN
SET @iFileStatus = 5;
END;








-- LEA_Morelength2_06252019.xlsx
-- \MDCSDB-UATMDCSFilesEnrollmentFiles65-636970996221672147-onzo4vo31ndblb00hefk3zyr.xlsx