sqlBulk.WriteToServer(excelReader); 

                                        string removeCharacter = "Select Replace('Price', '$ ', '') FROM #temp";

                                        sqlCommand.CommandText = removeCharacter;

I'm importing an excel file into sqlserver, I'm trying to remove the $ sign before I import the field into the database. The above code just does nothing $ sign still imports, also no error.

Recommended Answers

All 3 Replies

It's because you perform the sqlBulk.WriteToServer(excelReader) before you set the CommandText in the SqlCommand object.

Use the SqlCommand and construct your query and execute the reader first, and use WriteToServer last.

Examine this example: http://msdn.microsoft.com/en-us/library/434atets

Oxiegen, thanks for your reply I did change the code but the $ still imports into the database

sqlBulk.DestinationTableName = tempTableName;
                                {
                                    try
                                    {
                                        string removeCharacter = "Select Replace('Price', '$ ', '') FROM #temp";
                                        sqlCommand.CommandText = removeCharacter;

                                        sqlBulk.WriteToServer(excelReader); 
                                        }

Ok. Getting closer.
Now, like in the example you need to insert the line where you execute the reader just before WriteToServer.

sqlCommand.CommandText = removeCharacter;

//Execute reader here
excelReader = sqlCommand.ExecuteReader();

//Write to server here
sqlBulk.WriteToServer(excelReader);

excelReader.Close();
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.