asked on

import csv to sql

hi i am trying to import a csv file to sql 2005 i did that before with other files using this code

exec master..xp_cmdshell 'bcp "dbMain.dbo.tempImport " in C:\MyFiles\tt.csv -f:\MyFiles\BcpImport.fmt -SCELLSERVER\SQLEXPRESS -T'

but now i got this error

SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file

so im trying to use this code

SELECT *
FROM OPENROWSET('MSDASQL.1',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\MyFiles\;', 'SELECT * from tt.csv')

and it comes in the first row as the field name and then i get this error

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL.1" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL.1" for linked server "(null)".

and after this when i try to open the file direct it says "the file is used by another posses"

ASKER CERTIFIED SOLUTION

dwe761

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbacs

ASKER

i figured out the problem, I didn't write the path to the format file correctly i wrote -f:\MyFiles instead of -fC:\MyFile
now it works it imports the 7 rows from the file to the table but it does give me an error here is the output

NULL
Starting copy...
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file
NULL
7 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 297 Average : (23.57 rows per sec.)
NULL

since i get all 7 rows i don't really care about that error, but if some one knows how to fix that i appreciate your help thanks