Dynamicallly Importing csv file into MS SQL

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

Dynamicallly Importing csv file into MS SQL

Author

Topic

minckle
Starting Member

2 Posts

Posted - 2007-02-14 : 09:53:55

I have an app written in delphi v5 with a MS SQL Database and I'm struggling with an import feature i need

The user needs a function to import Contact data from a txt/csv file into the Contact Table

Details of feature:
the user enters the different parameters into the Delphi app e.g.
File Location
Files Includes Column Headings or not
Whether the file Comma or Tab Seperated
Mapping the fields

Then i need to use those parameters and run some sort of import routine putting the data into the specific table.

The tables consists of 3 fields - First Name, Surname, Mob Number - but these fields can be in any order in the file. for example Col 1 (in file) needs to go into Field 3 in DB. this is sorted in the Mapping Fields above

How do I do this using MS SQL??

I've been looking at using the BULK INSERT command e.g
BULK INSERT Contact
FROM 'C:\Import_data.csv'
WITH
{
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
}
but at the minute i just get error -
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation.

Is this the correct command to use??

Do you know any websites that can point me in the right direction??

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-14 : 09:58:30

use parenthesis not { }


BULK INSERT Contact
FROM 'C:\Import_data.csv'
WITH
{ (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
} )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources