Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  importing .csv files using bcp command.

Author  Topic  Rajiv Starting Member 1 Post Posted - 2010-06-24 : 07:15:41 I am trying to import .csv files (in excel) using the following details: 1. Format file 9.0 21 1 SQLCHAR 0 1000 "," 1 vehicleTypeID Latin1_General_BIN 2 SQLCHAR 0 1000 "," 2 wheelPlanCode Latin1_General_BIN 3 SQLCHAR 0 1000 "," 3 baseEuroClass Latin1_General_BIN 4 SQLCHAR 0 1000 "," 4 PMEuroClass Latin1_General_BIN 5 SQLCHAR 0 1000 "," 5 graduatedVEDCO2 Latin1_General_BIN 6 SQLCHAR 0 1000 "," 6 LECorRPC Latin1_General_BIN 7 SQLCHAR 0 1000 "," 7 modTypeCode Latin1_General_BIN 8 SQLCHAR 0 1000 "," 8 country Latin1_General_BIN 9 SQLCHAR 0 1000 "," 9 fuelClass Latin1_General_BIN 10 SQLCHAR 0 1000 "," 10 weight Latin1_General_BIN 11 SQLCHAR 0 1000 "," 11 busType Latin1_General_BIN 12 SQLCHAR 0 1000 "," 12 engineCapacity Latin1_General_BIN 13 SQLCHAR 0 1000 "," 13 taxClass Latin1_General_BIN 14 SQLCHAR 0 1000 "," 14 bodyClass Latin1_General_BIN 15 SQLCHAR 0 1000 "," 15 yearOfManufacture Latin1_General_BIN 16 SQLCHAR 0 1000 "," 16 regDate Latin1_General_BIN 17 SQLCHAR 0 1000 "," 17 nonCompliantFrom Latin1_General_BIN 18 SQLCHAR 0 1000 "," 18 cameraID Latin1_General_BIN 19 SQLCHAR 0 1000 "," 19 capturedDate Latin1_General_BIN 20 SQLCHAR 0 1000 "," 20 capturedTime Latin1_General_BIN 21 SQLCHAR 0 1000 "\n" 21 VRM Latin1_General_BIN 2. location of the csv files and format files as E:\temp\lez2009_AQN 3. SQl code as use lez SET NOCOUNT ON DROP TABLE #filelist CREATE TABLE #filelist (FileName varchar(100)) INSERT INTO #filelist EXEC master..xp_cmdshell 'dir e:\temp\lez2009_AQN\*.csv /b' DECLARE @file varchar(100), @command varchar(1000) DECLARE FileCursor CURSOR FOR SELECT FileName FROM #filelist where FileName is not null OPEN FileCursor FETCH NEXT FROM FileCursor INTO @file WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @command = 'bcp lez.dbo.anpr2009_AQN in e:\temp\lez2009_AQN\' + @file + ' -T -F2 -f e:\temp\lez2009_AQN\anpr.fmt' EXEC master..xp_cmdshell @command FETCH NEXT FROM FileCursor INTO @file END CLOSE FileCursor DEALLOCATE FileCursor 4. I am trying to import the data in table dbo.anpr2009_AQN while running the code, I get the foll error: SQLState = HY000, NativeError = 0 Error = [Microsoft][SQL Native Client]I/O error while reading BCP format file NULL