MSSQL CSV IMPORT

Below details a series of MSSQL scripts to perform a CSV Import into a MSSQL database where it can be used for other purposes. In the below example, i have used this to import a bank csv file and convert it to a double-entry trial balance that accountants can use.

-- 1) Save your Excel File as a csv file.
-- => Ensure no header
-- => Understand each column's characteristic so that MSSQL table can be defined.
-- => Identify the location of the file to be imported.
-- => Ensure that dates are formated as " =TEXT([date],"yyyymmdd")
--
-- 2) Review the MSSQL scripts BEFORE running them

CREATE TABLE dbo.#Test
(
ClientId nvarchar(50),
Staff nvarchar(30),
Project nvarchar(30),
Trandate datetime
)

BULK INSERT #Test
FROM 'C:\Users\STEPHEN\Desktop\MSSQL_Import.csv'

WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

UPDATE #Test -- UPDATE YOUR CLIENT ID DETAIL HERE
SET ClientId = '114374'

SELECT * FROM dbo.#Test

CREATE TABLE dbo.#Import -- CREATE A MAPPING TABLE FOR SUBSEQUENT IMPORT
(
TranDate datetime,
Net money,
Description nvarchar(255),
ID nvarchar(20),
ClientId int,
Debit nvarchar(100),
Credit nvarchar(100)
)

INSERT INTO dbo.#Import
( [ClientId], [Description], [TranDate] ) -- PERFORM YOUR MAPPING HERE
SELECT [ClientId], Concat(Staff, '-', Project), TranDate
FROM dbo.#Test

UPDATE dbo.#Import
SET ID = CONCAT('GJ' , LEFT(newid(),6) ,CONCAT('#',Day(Trandate)), CONVERT(nvarchar(3), TranDate), Year(Trandate)),
NET = 1000

INSERT INTO blissimport.dbo.Import1 -- CHECK YOUR IMPORT TABLE FIRST
SELECT * FROM dbo.#Import

DROP TABLE dbo.#Import

DROP TABLE dbo.#Test

SELECT * FROM Import1

INSERT INTO [dbo].[CATEGORIZE] -- CONVERSION INTO TRIAL BALANCE BEGINS HERE
SELECT [CLIENT_ID] AS 'CLIENT_ID',
'BANK' AS 'TRX_TYPE',
ID AS 'GL_ID',
DATE,
NET AS 'Debit',
0 AS 'Credit',
DESCRIPTION,
[BANK] AS 'COA',
'' AS 'TAX',
NET AS 'NET',
'' AS 'SubModule',
'' AS 'Detail'

FROM [dbo].[IMPORT1]

WHERE NET >= 0

UNION ALL

SELECT [CLIENT_ID] AS 'CLIENT_ID',
'RECEIVE MONEY' AS 'TRX_TYPE',
ID AS 'GL_ID',
DATE,
0 AS 'Debit',
NET AS 'Credit',
DESCRIPTION,
'' AS 'COA',
'' AS 'TAX',
NET*-1 AS 'NET',
'' AS 'SubModule',
'' AS 'Detail'

FROM [dbo].[IMPORT1]

WHERE NET >= 0

UNION ALL

SELECT [CLIENT_ID] AS 'CLIENT_ID',
'BANK' AS 'TRX_TYPE',
ID AS 'GL_ID',
DATE,
0 AS 'Debit',
NET*-1 AS 'Credit',
DESCRIPTION,
[BANK] AS 'COA',
'' AS 'TAX',
NET AS 'NET',
'' AS 'SubModule',
'' AS 'Detail'

FROM [dbo].[IMPORT1]

WHERE NET < 0 UNION ALL SELECT [CLIENT_ID] AS 'CLIENT_ID', 'SPENT MONEY' AS 'TRX_TYPE', ID AS 'GL_ID', DATE, NET*-1 AS 'Debit', 0 AS 'Credit', DESCRIPTION, '' AS 'COA', '' AS 'TAX', NET*-1 AS 'NET', '' AS 'SubModule', '' AS 'Detail' FROM [dbo].[IMPORT1] WHERE NET < 0 ORDER BY DATE, GL_ID UPDATE CATEGORIZE SET NET = Debit - Credit

Leave a Reply


*