• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2803
  • Last Modified:

CSV to SQL - How can I accomplish this?

Dear Experts:

I have a CSV file here that was exported from a third party database.  I need to find a way (either in C# .Net or some SQL utility) to import that CSV file into a SQL Express 2005 database.  I can't export the data via  SQL DUMP from the third party database and MUST use CSV.

I don't want to have to create the table in the SQL database if I don't have to so some automation would be great.  So I need something that will basically do this:

1. CSV file is stored locally.
2. A table is created in SQL Server using the CSV file column headings (automatically)
3. The data is then imported into the SQL table.

It sounds fairly simple but I'm a dufus and can't figure it out.  Thanks ya'll!
0
DatSure
Asked:
DatSure
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Faiga DiegelSr Database EngineerCommented:
try this forum thread.. they gave lots of insights and tips to import data to express edition of SQL 2005
0
 
Faiga DiegelSr Database EngineerCommented:
0
 
chapmandewCommented:
Here is an article I wrote awhile back on how to do it:

http://blogs.techrepublic.com.com/datacenter/?p=205
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
DatSureAuthor Commented:
Hello everyone and thank you for your replies.  I really like Chap's article on SSIS but I wan't able to figure out how to get it installed and working.  I downloaded all the latest Express bits for both SQL Express (advanced edition), SQL Toolkit, and even C# but even with all of that installed the option to select a BI application wasn't shown in the templates!?

In any case, I did (on my own) figure out how to do what I wanted and it works great.  That is, it takes a file of CSV format (or XLS) and creates a new database in SQL Express and injects the information using a BULK COPY which is the fastest way possible.

Now, the next problem that is relevant to my original question is, how can I do the same thing I'm already doing (bulk copy) but for future CSV files INSERT the records into the database and ensure they are updated and no duplicates created?  Help here would be much appreciated.  Thanks!
namespace CSV2SQL
{
    class Program
    {
        static void Main(string[] args)
        {
 
            // This program will grab the .bin file found in the specified directory (CSV file) and import it's contents into a new database.
 
            // First we need to connect to the database.
 
            String myConnStr = (@"Data Source=STDFL00361\SQLEXPRESS;Initial Catalog=DonTest;Integrated Security=True");
            
            // create a new SqlConnection object with the appropriate connection string 
            SqlConnection sqlConn = new SqlConnection(myConnStr);
 
            //String mySql = (@"SELECT * INTO XLImport7 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\sample.xls; Extended Properties=Excel 8.0')...[sample$]");
            String mySql = (@"SELECT * INTO XLImport7 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\sample.xls; Extended Properties=Excel 8.0')...[sample$]");
 
            SqlCommand myCmd = new SqlCommand(mySql, sqlConn);
 
            // Try to open the connection.
 
            try
            {
                sqlConn.Open();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("There was a problem with connecting to the database! \n {0}", ex);
                Console.ReadLine();
            }
 
            // Ok, now lets try to exceute our command.
            try
            {
                myCmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Hmm, something went wrong with trying to execute the command! \n {0}", ex);
                Console.ReadLine();
            }
            finally
            {
                sqlConn.Close();
            }
 
        }
    }
}

Open in new window

0
 
chapmandewCommented:
is there something that uniquely identifies each row value?
0
 
DatSureAuthor Commented:
Yes, each row consists of what we call a PRO number.  These are unique and would work well as a primary key.  So how could I use my method with that unique column item per row?  Thanks!
0
 
chapmandewCommented:
I would think something like this as a command:

SELECT * FROM XLImport7 LEFT JOIN DestinationTble ON XLImport7.ProNumber = DestinationTable.ProNumber

Does that help?
0
 
DatSureAuthor Commented:
Chap:

I understand where you're going with this and I too thought of a way to try to use SQL itself resolve this problem but couldn't find any commands that could sync one table with another?

I found a Merge function in the DataTable C# functions but all that did was combine the two datatables I had.  I really just need to sync the two records.  The original table and the new table created by the CSV.  Anyone have any ideas?
0
 
rogersamCommented:
can someone tell me how to do this thing by creating a form application a form in .NET
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now