1. Load a portion of file into a temp DB table to produce FMT file
-- Use this
to read the CSV file and create a table; see the schema.ini design below to
correctly load the file
SELECT * INTO MyTable
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Text;Database=c:\temp;HDR=Yes;', 'SELECT * FROM [External.csv]') temp
--If Text
driver doesn't work then load the file into an Excel then read the file from
Excel using OPENROWSET
SELECT * INTO LU_PennzoilBillingOLD
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel
12.0;Database=c:\temp\External.xlsx;HDR=Yes;', 'SELECT TOP 10 * FROM
[Sheet1$]') t
2. Produce
external FMT file for future bulkload
-- Use this
to create a format file from the DB table IN A COMMAND WINDOW
bcp DataWarehouse.dbo.TempTable format
nul -T -c -f "c:\temp\External.fmt"
3. Use the following pieces of code for data refresh
process
-- Use this
to bulkload the CSV file into DB table
TRUNCATE TABLE LU_GE_Billing
BULK INSERT TempTable FROM 'c:\temp\External.csv' WITH (FORMATFILE = 'c:\temp\External.fmt', FIRSTROW=1, MAXERRORS=10)
Schema.ini is a file used to describe the External.csv file used by MS text driver. Put the following content in a file named c:\temp\Schema.ini (same directory as your External.csv located):
[Temp.csv]
ColNameHeader=False
Format=Delimited(|)
MaxScanRows=0
Col1=Col1 Long
Col2=Col2 Long
Col3=InvoiceNum Text Width 20
Col4=Manufacturer Text Width
50
Col5=Distributor Text Width
50
