Originally my intension was to use Excel as my data file. Using Excel I got everything working but had one heck of a time exporting my data to Excel. All the examples were for CSV.
So I switched to exporting my data in CSV format but now I'm having trouble with importing the data back into the database.
Here's my code which is slightly modified since I switched back to CSV but I keep getting "'C:\Users\MyFolder\MyFile.csv' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file
resides."
However, the path is absolutely correct and the file is there. What am I doing wrong here?
Here's the code:
// Connect to CSV file
string strCSVConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + "; Extended Properties=\"text;HDR=No;FMT=Delimited\"";
using (OleDbConnection connection = new OleDbConnection(strCSVConnection))
{
string strCommand = "SELECT * FROM [" + strFilePath + "]";
OleDbCommand command = new OleDbCommand(strCommand, connection);
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MyDBConnection))
{
bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
bulkCopy.ColumnMappings.Add("MerchantID", "MerchantID");
bulkCopy.ColumnMappings.Add("Price", "Price");
bulkCopy.DestinationTableName = "ProductPriceUploads";
try
{
bulkCopy.WriteToServer(dr);
}
catch
{
// Handle error
}
}
}
}
Thanks for your response. I have no problems importing Excel. But I can't create "native" Excel files. That's why I switched to CSV. But now I have trouble importing CSV!!! What a dilemma: Can't export Excel but have no trouble importing it. On the other
hand, I can't import CSV but I can export CSV without a glitch! Go figure!
With the CSV file, do I need to use a schema.ini file? Can you/anyone give me a nice example of CSV import using SqlBulkCopy?
This is such a common task but I spent hours already trying to fix this issue and not even done yet!
I tried the C:\Data folder and still got the same error. Then I tried using an Excel sheet and it worked flawlessly which means it's not a folder security issue.
It's definitely the way I'm handling the CSV file. I can't believe this is this complicated or I'm making some mistake somewhere that I'm not able to see.
Here's my code with lots of comments to make it easy to follow it. I hope this will be useful for everyone.
public void BulkImportCSVUsingSqlBulkCopy(string strFileName)
{
///<summary>
/// ObjectID:
/// Description: This method imports CSV data into SQL Server using SqlBulkCopy. If you notice, I'm passing only a filename to this method.
/// That's because I split the process into two "general purpose" methods. First method, simply upload a file to a specified folder and
/// returns the filename. So, by the time I'm calling this method, the file is already saved. It's location is no mystery because it's coming
/// from an entry I made into web.config -- DataUploadFolder.
///</summary>
// This is where the file is uploaded. I defined this in the AppSettings section in web.config.
string strFilePath = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["DataUploadFolder"]);
/* This was one of the issues I was having. I was putting the file path AND filename. Use ONLY file path.
Two other important points:
1. HDR=YES means, you have column names in the CSV file. If your CSV file does not contain column names, set it to HDR=NO
2. I also placed a text file named schema.ini in the same folder as I upload my CSV files. Down below, I'll show you what's in that file.
schema.ini file simply describes the data structure in the CSV file. It's a very simple file -- nothing scary!
*/
string strCSVConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";" + "Extended Properties='text;HDR=YES;'";
// Create an OLEDB connection to the CSV file
using (OleDbConnection connection = new OleDbConnection(strCSVConnection))
{
// Amazingly, you read data from a flat text file as if you're querying a table in SQL Server
// One important point here is that you are using ONLY the filename NOT the whole path. This was my second mistake.
// The purpose of declaring the strCommand is to keep things neat. You can just as easily type the whole thing within the OleDbCommand section.
string strCommand = "SELECT * FROM [" + strFileName + "]";
// We issue the command here
OleDbCommand command = new OleDbCommand(strCommand, connection);
// And open the connection to the text file
connection.Open();
// We're using a DbDataReader here which is fast and lightweight but you can use other methods as well i.e. data adapter
using (DbDataReader dr = command.ExecuteReader())
{
// Here we're creating the bulkCopy and opening a connection to the database
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MyDatabaseConnectionString))
{
bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
bulkCopy.ColumnMappings.Add("MerchantID", "MerchantID");
bulkCopy.ColumnMappings.Add("Price", "Price");
bulkCopy.DestinationTableName = "NameOfTheTableInSQLServer"; // This is where the data are going to be inserted
try
{
// You do ALL this to get to this one line that does all the magic!
bulkCopy.WriteToServer(dr);
}
catch
{
// Handle error
}
}
}
}
}
And this is what the schema.ini looks like:
ColNameHeader=True
MaxScanRows=0
Col1=MerchantID Integer
Col2=Category Text Width 250
Col3=ProductID Integer
Col4=ProductName Text Width 250
Col5=Price Currency
SamU
Contributor
2895 Points
1639 Posts
Need help reading a CSV file using SqlBulkCopy
Dec 01, 2009 01:01 AM|LINK
Hi,
Originally my intension was to use Excel as my data file. Using Excel I got everything working but had one heck of a time exporting my data to Excel. All the examples were for CSV.
So I switched to exporting my data in CSV format but now I'm having trouble with importing the data back into the database.
Here's my code which is slightly modified since I switched back to CSV but I keep getting "'C:\Users\MyFolder\MyFile.csv' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides."
However, the path is absolutely correct and the file is there. What am I doing wrong here?
Here's the code:
// Connect to CSV file string strCSVConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + "; Extended Properties=\"text;HDR=No;FMT=Delimited\""; using (OleDbConnection connection = new OleDbConnection(strCSVConnection)) { string strCommand = "SELECT * FROM [" + strFilePath + "]"; OleDbCommand command = new OleDbCommand(strCommand, connection); connection.Open(); using (DbDataReader dr = command.ExecuteReader()) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MyDBConnection)) { bulkCopy.ColumnMappings.Add("ProductID", "ProductID"); bulkCopy.ColumnMappings.Add("MerchantID", "MerchantID"); bulkCopy.ColumnMappings.Add("Price", "Price"); bulkCopy.DestinationTableName = "ProductPriceUploads"; try { bulkCopy.WriteToServer(dr); } catch { // Handle error } } } }Sam
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: Need help reading a CSV file using SqlBulkCopy
Dec 01, 2009 04:11 AM|LINK
Try placing the CSV in a new folder in some other like c:\Data
and then try It seems to be a permission issue
and here is an example for excel
http://www.davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx
Contact me
SamU
Contributor
2895 Points
1639 Posts
Re: Need help reading a CSV file using SqlBulkCopy
Dec 01, 2009 04:24 AM|LINK
Thanks for your response. I have no problems importing Excel. But I can't create "native" Excel files. That's why I switched to CSV. But now I have trouble importing CSV!!! What a dilemma: Can't export Excel but have no trouble importing it. On the other hand, I can't import CSV but I can export CSV without a glitch! Go figure!
With the CSV file, do I need to use a schema.ini file? Can you/anyone give me a nice example of CSV import using SqlBulkCopy?
This is such a common task but I spent hours already trying to fix this issue and not even done yet!
Sam
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: Need help reading a CSV file using SqlBulkCopy
Dec 01, 2009 04:39 AM|LINK
check these
http://weblogs.sqlteam.com/mladenp/archive/2006/08/26/11368.aspx
http://www.codeproject.com/KB/aspnet/ImportExportCSV.aspx
Contact me
SamU
Contributor
2895 Points
1639 Posts
Re: Need help reading a CSV file using SqlBulkCopy
Dec 01, 2009 04:43 AM|LINK
I tried the C:\Data folder and still got the same error. Then I tried using an Excel sheet and it worked flawlessly which means it's not a folder security issue.
It's definitely the way I'm handling the CSV file. I can't believe this is this complicated or I'm making some mistake somewhere that I'm not able to see.
Sam
SamU
Contributor
2895 Points
1639 Posts
Re: Need help reading a CSV file using SqlBulkCopy
Dec 01, 2009 01:29 PM|LINK
Solved the problem. Please use this link as this is BY FAR the BEST tutorial that shows you how to import CSV data into your database.
http://www.aspdotnetcodes.com/Importing_CSV_Database_Schema.ini.aspx
Here's my code with lots of comments to make it easy to follow it. I hope this will be useful for everyone.
public void BulkImportCSVUsingSqlBulkCopy(string strFileName) { ///<summary> /// ObjectID: /// Description: This method imports CSV data into SQL Server using SqlBulkCopy. If you notice, I'm passing only a filename to this method. /// That's because I split the process into two "general purpose" methods. First method, simply upload a file to a specified folder and /// returns the filename. So, by the time I'm calling this method, the file is already saved. It's location is no mystery because it's coming /// from an entry I made into web.config -- DataUploadFolder. ///</summary> // This is where the file is uploaded. I defined this in the AppSettings section in web.config. string strFilePath = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["DataUploadFolder"]); /* This was one of the issues I was having. I was putting the file path AND filename. Use ONLY file path. Two other important points: 1. HDR=YES means, you have column names in the CSV file. If your CSV file does not contain column names, set it to HDR=NO 2. I also placed a text file named schema.ini in the same folder as I upload my CSV files. Down below, I'll show you what's in that file. schema.ini file simply describes the data structure in the CSV file. It's a very simple file -- nothing scary! */ string strCSVConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";" + "Extended Properties='text;HDR=YES;'"; // Create an OLEDB connection to the CSV file using (OleDbConnection connection = new OleDbConnection(strCSVConnection)) { // Amazingly, you read data from a flat text file as if you're querying a table in SQL Server // One important point here is that you are using ONLY the filename NOT the whole path. This was my second mistake. // The purpose of declaring the strCommand is to keep things neat. You can just as easily type the whole thing within the OleDbCommand section. string strCommand = "SELECT * FROM [" + strFileName + "]"; // We issue the command here OleDbCommand command = new OleDbCommand(strCommand, connection); // And open the connection to the text file connection.Open(); // We're using a DbDataReader here which is fast and lightweight but you can use other methods as well i.e. data adapter using (DbDataReader dr = command.ExecuteReader()) { // Here we're creating the bulkCopy and opening a connection to the database using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MyDatabaseConnectionString)) { bulkCopy.ColumnMappings.Add("ProductID", "ProductID"); bulkCopy.ColumnMappings.Add("MerchantID", "MerchantID"); bulkCopy.ColumnMappings.Add("Price", "Price"); bulkCopy.DestinationTableName = "NameOfTheTableInSQLServer"; // This is where the data are going to be inserted try { // You do ALL this to get to this one line that does all the magic! bulkCopy.WriteToServer(dr); } catch { // Handle error } } } } }And this is what the schema.ini looks like:
ColNameHeader=True
MaxScanRows=0
Col1=MerchantID Integer
Col2=Category Text Width 250
Col3=ProductID Integer
Col4=ProductName Text Width 250
Col5=Price Currency
Sam
BarbaMarioli...
Member
208 Points
136 Posts
Re: Need help reading a CSV file using SqlBulkCopy
Dec 02, 2009 02:57 PM|LINK
Hi,
check GemBox.Spreadsheet .NET Excel component. I think it will solve all your problems, because you can import/export XLS, XLSX, ODS, CSV and HTML files.
Here is an example how to import/export DataTable to Excel with GemBox.Spreadsheet.
SamU
Contributor
2895 Points
1639 Posts
Re: Need help reading a CSV file using SqlBulkCopy
Dec 02, 2009 03:10 PM|LINK
Thanks for letting me know about this component.
Sam