Excel to help DBAs

Have you ever been overloaded with identical endless entry of similar SQL commands? Have you ever wanted to ease your everyday work as a DBA? A possible solution is presented below:

A common task of a DBA is to execute a single batch of similar SQL commands instead of end users, as their application interface allows input of data only line by line. Usually the data is stored in *.csv file format and in this case Excel is a great tool for the data transformation from one format to another.

SQL-92 standard enables the use of case sensitive letters as well as the inclusion of special characters in SQL identifiers. In order to import data from Excel sheet into the DB table shown below we should write an Excel formula.

csv data opened in Excel file
ABCDE
1CustomerIDNameFamilyAgeCompany
229080AdrienBrody39Predators
312342TopherGrace34Predators
4234523AliceBraga29Predators
5742212BrianSteele39Predators
6234WaltonGoggins40Predators
CREATE TABLE "Persons" (
"id"INTEGER,
"CustomerID"INTEGER,
"Name"VARCHAR,
"Family"VARCHAR,
"Age"VARCHAR,
"Company"VARCHAR);

Typing an Excel formula is a tedious work, but the form below makes it easy. Curly brackets { } are used for reference to any cell or insert of an expression.

SQL request:

Result in Excel:
fx
="INSERT INTO "&CHAR(34)&"Persons"&CHAR(34)&"("&CHAR(34)&"id"&CHAR(34)&", "&CHAR(34)&"CustomerID"&CHAR(34)&", "&CHAR(34)&"Name"&CHAR(34)&", "&CHAR(34)&"Family"&CHAR(34)&", "&CHAR(34)&"Age"&CHAR(34)&", "&CHAR(34)&"Company"&CHAR(34)&") VALUES("&CHAR(34)&"Generator"&CHAR(34)&".NEXTVAL, "&A3&", '"&B3&"', '"&C3&"', "&D3+1&", '"&E3&"');"
ABCDEF
1CustomerIDNameFamilyAgeCompany
229080AdrienBrody39PredatorsINSERT INTO "Persons"("id", "CustomerID", "Name", "Family", "Age", "Company") VALUES("Generator".NEXTVAL, 29080, 'Adrien', 'Brody', 40, 'Predators');
312342TopherGrace34PredatorsINSERT INTO "Persons"("id", "CustomerID", "Name", "Family", "Age", "Company") VALUES("Generator".NEXTVAL, 12342, 'Topher', 'Grace', 35, 'Predators');
4234523AliceBraga29PredatorsINSERT INTO "Persons"("id", "CustomerID", "Name", "Family", "Age", "Company") VALUES("Generator".NEXTVAL, 234523, 'Alice', 'Braga', 30, 'Predators');
5742212BrianSteele39PredatorsINSERT INTO "Persons"("id", "CustomerID", "Name", "Family", "Age", "Company") VALUES("Generator".NEXTVAL, 742212, 'Brian', 'Steele', 40, 'Predators');
6234WaltonGoggins40PredatorsINSERT INTO "Persons"("id", "CustomerID", "Name", "Family", "Age", "Company") VALUES("Generator".NEXTVAL, 234, 'Walton', 'Goggins', 41, 'Predators');

Caution! To avoid any errors paste the content in formula bar, not directly in the cell.