Importing CSV using BCP and Data Format

 
--// Set Up the Metadata
==================================================
 
CREATE TABLE TestTable(
Column1 varchar(10) NOT NULL,
Column2 varchar(10) NOT NULL,
Column3 varchar(10) NOT NULL
)
GO
 
 
 
This is an example to set up a migration process using BCP:
 
 
 
--// Set Up the Metadata
==================================================
 
CREATE TABLE TestTable(
Column1 varchar(10) NOT NULL,
Column2 varchar(10) NOT NULL,
Column3 varchar(10) NOT NULL
)
GO

 

 

--// Prepare the Data Source - DataFile.csv

==================================================

"Data1","Data22","Data333"

"Data1","Data22","Data333"

"Data1","Data22","Data333"

 
 

 

--// Prepare the Data Format - FormatFile.fmt

==================================================

7.0

4

1       SQLCHAR       0       1       "\""                 0     Quote1

2       SQLCHAR       0       255 "\",\""             1     Column1

3       SQLCHAR       0       255      "\",\""              2     Column2

4       SQLCHAR       0       255      "\"\r\n"             3     Column3

 

 

--// Create a BAT file called Import_Test.BAT

==================================================

BCP dba..TestTable in H:\Temp\DataFile.csv /T /fH:\Temp\FormatFile.fmt

 

pause

 

 

 

--// Execute the BAT

==================================================

Import_Test.BAT

 
 
 
--// Validate the result
==================================================
SELECT * FROM TestTable
 
 

 

--// Other way using: BULK
==================================================
 

  BULK INSERT [P00000001]

    FROM 'H:\Temp\P00000001_ALL.csv'

    WITH

    (

    FIRSTROW = 2,

    FIELDTERMINATOR = ',',  --CSV field delimiter

    ROWTERMINATOR = '\n'--,   --Use to shift the control to next row

    --ERRORFILE = 'H:\Temp\P00000001_ALL.log',

    --TABLOCK

    )

  

--// Other way using: OPENROWSET
==================================================

SELECT top 10 * FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=H:\Temp;', 'SELECT * from P00000001_ALL.csv');

 
select * from openrowset('MSDASQL','Driver={Microsoft Access Text Driver (*.txt, *.csv)}','select * from H:\Temp\P00000001_ALL.CSV') 
 

Etiquetas: