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!
LVL 2
DatSureAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.