SQL Server: Import and Export CSV

Question

I am working on SQL Server 2000. As you know, there is an application that's shows query results (Query Analyzer) The data is shown there, on a grid, and you can save that grid in a csv file. That's great.

Now, I want to INSERT those csv values into a Table. Does SQL Server provide any method?? And I mean, automatic or semi-automatic methods (like when you read an Excel from Query Analyzer). Because, for example, with a Bulk insert I can accomplish that but, it involves a lot of work.

I am considering developing an application that does that (maybe in python) But, I don't want to if anything else already exists.

Answer 1

If you already know how to import from Excel, you can open the .CSV file(s) in Excel and save them as .xls once the conversion is done.

Answer 2

You can look at using DTS SSIS to do this.

DTS is what you will want to use for SQL Server 2000 (as the comments below suggest).

I believe you can also copy paste directly from a spreadsheet into a table if you're not trying to do anything too fancy. I haven't done this in a while so you'll have to double check.

Answer 3

Why are you creating a csv file at all, why not simply insert the results of your selct statement? Assume the select you used to build the csv was :

select a.field1, b.field2 
from anothertable a
join yetanothertable b on a.myid = b.myid

why not just do:

insert mytable (field1, field2)
select a.field1, b.field2 
from anothertable a
join yetanothertable b on a.myid = b.myid

If you really have a csv file to import, you can do so using the import wizard if it is a one-time deal or a DTS package if it is a repeatable process you want to do on a regular schedule.

Answer 4

If you just need CSV exports/imports, I'd use BCP. It's much faster, it's easier to use for those requirements (at least in my opinion).

Greets Flo