Importing CSV file via BLOB

In the past months, many of our customers have asked us how to import CSV files via PL/SQL without having access to the database server's file system. For this article we have prepared an example to explain the basic workflow, which you can easily adapt according to your needs. The sample code would read time entries of different terminals and import them into the table MDZEIMP. The function contains a BLOB as input parameter, which is provided by the client.

CREATE OR REPLACE PROCEDURE Ind_ImportCSV(theFile IN BLOB) IS
	sourceCharSet VARCHAR2(40) := 'AL32UTF8';
	dbCharSet VARCHAR2(40) := Md_Sql.GetDBCharSet;
	m_File UTL_FILE.FILE_TYPE;
	theLine VARCHAR2(200);
	isFirst BOOLEAN := true;
	rec mdzeimp%ROWTYPE;
BEGIN
	-- create an oracle directory 'IMPORT' first
	Md_Lib.WriteBlobToFile(Md_Zip.UnzipBlob(theFile), 'IMPORT', 'Imp_ZE.csv');
	m_File := UTL_FILE.FOPEN ('IMPORT', 'Imp_ZE.csv', 'r', 200);

	-- sets the default delimiter of the CSV file
	Md_ImpLieItm.SetDelimiter(';');

	loop
		begin
			theLine := NULL;
			UTL_FILE.GET_LINE (m_File, theLine);

			if (TRIM(theLine) is null) then
				exit;
			end if;

			exception
				when NO_DATA_FOUND then
					exit;
		end;

		if (isFirst) then
			-- ignore header line
			isFirst := false;
		else
			--Card_Nr;time;Come/Go;Device_ID
			--1046;04.01.2016 07:05;G;5
    
			--activate this line when to convert to a different charset
			--theLine := CONVERT(theLine, dbCharSet, sourceCharSet);

			rec.Card_Nr := Md_ImpLieItm.Extract(theLine);
			rec.StartTime := TO_DATE(Md_ImpLieItm.Extract(theLine), 'DD.MM.YYYY HH24:MI');
			rec.Zea_Code := Md_ImpLieItm.Extract(theLine);
			rec.Terminal := TO_NUMBER(Md_ImpLieItm.Extract(theLine));
			rec.Terminal_Type := 'FP';
    
			insert into mdzeimp values rec;
		end if;
	end loop;

	commit;
	UTL_FILE.FCLOSE(m_File);

	exception
		when OTHERS then
			if (UTL_FILE.IS_OPEN (m_File)) then	UTL_FILE.FCLOSE(m_File); end if;
			Md_Error.Raise;
END;
/

The BLOB file is first saved to the Oracle 'IMPORT' directory. (This directory needs to be created beforehand if it doesn't already exist.) Next, UTL_FILE reads and processes every single line. Then the package Md_ImpLieItem extracts the fields of the CSV file and assigns them to the respective fields in the table.

Finally you need to provide a command to upload the CSV file. To place the command directly in the main menu of the HRM module, go to the XML repository and modify the file RBI_PREMIUM#EXTENSION accordingly:

<?xml version="1.0"?>
<ResourceBundleInfoContainer xmlns:mdc="http://www.multidata.at/mp5/Common" xmlns="http://www.multidata.at/mp5" >
	<CommandsCatalog>
		<Command Name="Ind.ZeUpload" Title="Upload terminal data" Mode="DbCall" Action="Ind_ImportCSV">
			<Parameters>
				<mdc:Parameter Name="theFile" DataType="Blob" Title="File" Value="D:\Temp\Terminal.csv" Display="true"/>
			</Parameters>
		</Command>
	</CommandsCatalog>
	<MenusCatalog>
		<MenuBarItem Title="&amp;Time recording">
			<MenuBarItem Title="Imports">
				<MenuBarItem CommandName="Ind.ZeUpload" BeginGroup="true" />
			</MenuBarItem>
		</MenuBarItem>
	</MenusCatalog>
</ResourceBundleInfoContainer>