Import CSV files into SQL Server 2008

1
votes

Basically, I want to import hundreds of CSV files into SQL Server 2008.

File format is as following :

<Ticker>,<DTYYYYMMDD>,<Open>,<High>,<Low>,<Close>,<Volume>
AAM,20120110,21.6,22.8,21.4,21.6,3510
AAM,20120109,22.2,22.9,22.0,22.2,1130
AAM,20120105,0.0,23.0,22.2,22.2,210

I tried : 

BULK
INSERT BBB
FROM 'D:\FIFA\excel_aam.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '/n'
)
GO

but it didn't work. So I was thinking import the CSV file as varchar format, then change each columns to proper data type later, like this :

CREATE TABLE BBB (
TICKER VARCHAR(15)NULL,
INDEXDATE VARCHAR(15) PRIMARY KEY,
OPENPRICE VARCHAR(15) NULL,
HIGHPRICE VARCHAR(15) NULL,
LOWPRICE VARCHAR(15) NULL,
CLOSEPRICE VARCHAR(15) NOT NULL,
VOLUME VARCHAR(15))
GO

but it gave me the error :

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 7 (VOLUME).

So, how could I import these files (so many files that i couldn't use import and export wizard) into SQL Server properly?

2
sql-server
not yet, thanks, I'm just Sql beginner... – Cocc Cocc
SSIS is not SQL. I suggest searching for the term and learning about this SQL Server feature. – Oded

2 Answers

0
votes

For importing so many files sounds like u'll need SSIS

0
votes

It works just fine in my case when I just change the rowterminator to \n (not /n)

--CREATE TABLE BBB (
--TICKER VARCHAR(15)NULL,
--INDEXDATE DATETIME,
--OPENPRICE DECIMAL(12,4),
--HIGHPRICE DECIMAL(12,4),
--LOWPRICE DECIMAL(12,4),
--CLOSEPRICE DECIMAL(12,4),
--VOLUME DECIMAL(20,4))
--GO

BULK INSERT BBB
FROM 'D:\FIFA\excel_aam.csv'
WITH
(
   FIRSTROW = 2,
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)
GO

(3 row(s) affected)

and I have the rows in the BBB table now....