Importing a CSV using the open file dialog

[welshboy202010]

Hi folks, this a quick question.

I need to import a CSV file into SQL Server 2005, however as the CSV file name changes on a daily basis, I want the user to be able to select the file from an open file dialogue window.

It's a windows form app incidentally.

I have a rough idea of how to get the data into the file hard-coded wise, but I'm not sure of how I do it with an open dialogue command.

Any help would be great, and there could be a beer in it too.

 

[AlexCuse]

Have a look at the OpenFileDialog box's 'FileName' property. Here's an example (you'd have to change or eliminate the filter of course).

http://en.csharp-online.net/Select_a_file_with_OpenFileDialog

Incidentally if you need more info on bringing the file to SQL server take a look at this:

         //set up ole connection for CSV file, fill data table
         OleDbConnection oConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\;Extended Properties=\"text;HDR=Yes;FMT=Delimited\";");
         OleDbDataAdapter oAr = new OleDbDataAdapter("select * from Test.csv", oConn);
        
         //create/fill data table
         DataTable mDt = new DataTable("test");
         oAr.Fill(mDt);
        
         //create SqlConnection (used to capture counts & by BulkCopy object)
         SqlConnection sConn = new SqlConnection("Data Source=myServer;Initial Catalog=TEST;User Id=myID;Password=myPass;");
         sConn.Open();
        
         //command to capture counts
         SqlCommand sCom = new SqlCommand("select count(*) from CSVTest", sConn);
        
         //capture initial rowcount
         int sRows = Convert.ToInt32(sCom.ExecuteScalar());
        
         //create bulk copy object, connection (in constructor) & destination table
         SqlBulkCopy sBC = new SqlBulkCopy(sConn);
         sBC.DestinationTableName = "CSVTest";
        
         //write Data Table to Destination
         sBC.WriteToServer(mDt);
        
         //capture final rowcount
         int eRows = Convert.ToInt32(sCom.ExecuteScalar());
        
         //display lines copied
         Console.WriteLine("Success: " + (eRows - sRows).ToString() + " rows copied");
        
         //clean up
         oConn.Dispose();
         oAr.Dispose();
         sConn.Dispose();
         sCom.Dispose();
         mDt.Dispose();
         sBC.Close();
         sBC = null;
        
         //keep console open
         Console.ReadLine();

Hope it helps,

Alex

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!