2

I have some financial data in a CSV file and I would like to put it into an SQL table.

I want to run the following script which I adapted from this source :(http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/)

CREATE TABLE Price
(ID INT IDENTITY(1,1) NOT NULL,
PriceDate datetime2,
OpenPrice decimal(10,4),
HighPrice decimal(10,4),
LowPrice decimal(10,4),
ClosePrice decimal(10,4),
Volume int,
AdjClose decimal(10,4),
AdjOpen decimal(10,4),
AdjLow decimal(10,4),
AdjHigh decimal(10,4)
)
GO


BULK
INSERT Price
FROM 'C:\Users\Public\Documents\bhp.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

I am getting the following errors:

'Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ID).'

I think, it is because my "ID" which is a primary key and an autonumber increment.

Obviously I don't want to be generating this number via the CSV (I plan to insert multiple CSV files into this table later), I want it generated by the database.

Is it possible to specify to BULK INSERT which columns I plan to insert into for each column?

Nick Forge
  • 21,344
  • 7
  • 55
  • 78
Diskdrive
  • 18,107
  • 27
  • 101
  • 167
  • Try the above http://stackoverflow.com/questions/14711814/ignore-certain-columns-when-using-bulk-insert By the way, nice question +1. – kostas ch. Jun 18 '13 at 08:27
  • If you have any control over the data source (I don't know that you do, but maybe this will help someone), then if your input file has zero for the identity column, it will work just fine and create a new number for you on insert..... – Sir Fartsalot Jun 13 '23 at 16:01

2 Answers2

1

You could use a format file to specify how BULK INSERT handles the missing column.

You could also create a staging table without the ID column and import in to that. You can then perform any data cleaning that may be required and do a normal INSERT in to your production table.

Another option available to you, would be to create a view against your production table. In your view definition, you would leave out the ID column. You can then BULK INSERT against the view.

codingbadger
  • 42,678
  • 13
  • 95
  • 110
1

I would -I always do- create a temporary (unindexed, no unique ID, no primary key, etc) table and bulk insert CSV there, then either move data to the target table or alter the table to make it look as I want.
The reason is, formatting can be complicated in some cases and it's just another source of error, easier to filter for issues like a field is NOT NULL but CSV has an error in it, etc. One also can trigger updating data if it's not an ASAP insert (e.g. indexing up the table can run overnight)
I had enough headaches with CSV import, so... in my experience the best solution is a temp table and ALTER it or move data. But it's your call of course :)

Community
  • 1
  • 1
  • 1
    ah thanks, yep feel daft for overlooking such an obvious solution as just use a temp table! – Diskdrive Jun 18 '13 at 13:44
  • @Diskdrive You're welcome. When one digs into an issue never see the easiest solution, it's human nature - that's why we have Stack Overflow ;) Glad I could help. Cheers! –  Jun 18 '13 at 13:58