The following twelve-line MVON BASIC program builds a BULK INSERT command and passes it to SQL Server.
* Build our BULK INSERT statement
CSV.FILE = '\ONgroup\MVON\local.os\CSV\20151124.csv' ;* Comma-separated file
FMT.FILE = '\ONgroup\MVON\local.os\CSV\CALL_REVIEW.fmt' ;* The BULK INSERT format file.
* Use the \ character as our quotation mark, since both ' and " are used in the BI.SNTC.
BI.SNTC = \INSERT INTO CALL_REVIEW_2 SELECT '20151124.csv' "CSV_FILE_NAME",* FROM OPENROWSET(BULK '\:CSV.FILE:\', FORMATFILE = '\:FMT.FILE:\', CODEPAGE = 'RAW',FIRSTROW =2) AS T1\
* Call -SQL.EXEC
OUT.LIST = ""
TERM.OUT = ""
CALL -SQL.EXEC(@WHO, BI.SNTC, OUT.LIST, TERM.OUT)
IF @SYSTEM.RETURN.CODE < 0 THEN CRT TERM.OUT ;* ELSE SERVER.CODE CONTAINS NUMBER OF ROWS INSERTED
END ;* OF PROGRAM
What it Means
SQL Server's BULK INSERT is a great way to get tab-delimited, comma-delimited, or system-delimited data into SQL Server. MVON BASIC can drive a call, or calls, to BULK INSERT via the SQL.EXEC subroutine.
CALL -SQL.EXEC(AccountName, SQLServerCommand, ReturnList, TerminalOutput)
AccountName - An MVON account as defined in the mvonacct.dat file.
SQLServerCommand - The command to execute in the SQL Server run-time.
ReturnList - A dynamic array of the columns in the query, where each row is separated by a field mark and each column is separated by a value mark. Not used when SQLServerCommand is not a query.
TerminalOutput - The terminal output from SQL Server.
If our BULK INSERT is successful, @SYSTEM.RETURN.CODE will hold the number of rows inserted. If there is an error, @SYSTEM.RETURN.CODE will be < 0, and TERM.OUT will contain our error message.
INSERT INTO CALL_REVIEW_2
FORMATFILE = '\ONgroup\MVON\local.os\CSV\CALL_REVIEW.fmt',
CODEPAGE = 'RAW',FIRSTROW =2)
CALL_REVIEW_2 is a table in our SQL Server database, which is defined as follows:
CALL_REVIEW_ID is an Identity column; meaning that it gets an automatically generated sequential primary key. The name of our csv file is determined in BASIC and passed to SQL Server as a recurring literal value. The rest of the data will come from the csv file itself, in accordance with the CALL_REVIEW.fmt file.
CALL_REVIEW.fmt is a parameter file for BULK INSERT, which tells how our csv file is to be parsed, and looks like this:
1 SQLCHAR 0 60 "," 2 RECORDING_FILE_NAME SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 60 "," 3 ACCOUNT_NBR SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 60 "," 4 CALL_DATE SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 60 "," 5 CALL_TIME SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 10 "," 6 PHONE_DIALED SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 60 "," 7 AGENT_NAME SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 60 "," 8 SESSION SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 60 "," 9 CALL_RESULT SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 60 "," 10 AGENT_RESULT SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 60 "," 11 COMPAING_FILE_NAME SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 60 "," 12 CLIENT_ID SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 60 "," 13 DURATION SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 60 "\r\n" 14 SKILL SQL_Latin1_General_CP1_CI_AS
SQL Server BULK INSERT format files come in both the XML and non-XML variety. This one is clearly non-XML. More info can be found here: