 |
|
 |
Hello .,
I want to import excel csv file in sql server without creating any table in my db.Then how should i need to do help
|
|
|
|
 |
|
 |
Hi All, I found the solution for importing CSV file using C#, SQL Server, sqlbulkcopy, this code even overcomes limitations of Flat File Source task in SSIS System.Data.Odbc.OdbcConnection conn; DataTable dt = new DataTable(); System.Data.Odbc.OdbcDataAdapter da; string connectionString; string importFolder; string fileName; string strsqlconn = "Data Source=(local);Initial Catalog=<database_name>;Integrated Security=True;"; SqlConnection oSqlConn = new SqlConnection(strsqlconn); oSqlConn.Open(); importFolder = @"C:\Test\SQL Server Load\CSVLoadTest"; // path of the csv file fileName = "TestDataCSV.csv"; // CSV filename connectionString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + importFolder + ";"; // ODBC driver for txt, csv files conn = new System.Data.Odbc.OdbcConnection(connectionString); //conn.Open(); da = new System.Data.Odbc.OdbcDataAdapter("select * from [" + fileName + "]", conn); da.Fill(dt); // fill the data to datatable SqlBulkCopy bulkCopy = new SqlBulkCopy(strsqlconn); // establish connection to bulk copy to SQL Server bulkCopy.DestinationTableName = "TestDataTable"; // set the destination table bulkCopy.BulkCopyTimeout = 600; // default 30 seconds bulkCopy.WriteToServer(dt); // Write from the datatable to the destination. conn.Close(); oSqlConn.Close();
-- modified 18 Apr '12.
|
|
|
|
 |
|
|
 |
|
 |
Hi Mohammad Al Hoss
I am trying to use Excel 2007 VBA to import files into MS SQL Server 2008.
Could you please provide Excel 2007 VBA code?
Kind Regards
Bhavik
|
|
|
|
 |
|
 |
Hi Mohammad,
I am surprised that I couldn't able to find out the source code of the sample. Kindly let me know how to get the source code for this same.
Thanks & Regards,
Dhana. R
|
|
|
|
 |
|
 |
hi.. I used the code to import an excel woorkbook with a single sheet into a sql 2005 db but i got this exception.
{"ODBC--connection to '{SQL Server}(.\SQLEXPRESS)' failed."} System.Data.OleDb.OleDbException
Apart from the code is there anything else that i need to do for the connector like set up a datasource or anything. could someone please help me.. I am a fresher trying to develop a project that requires importing from an excel sheet.
|
|
|
|
 |
|
 |
Your code works perfectly fine ... but when i change the select statement to INSERT it gives this error ... i actually want the table to get updated everytime ... if i use select statement then every time a new table is created .... please help ... thanks
|
|
|
|
 |
|
 |
Dear,
When I execute application there was error : "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine." Could you help me why it is happened? For your information, I use Visual Studio 2008, Microsoft Office Excel 2003 and SQL Server Express. SQL Server Express that I used is in remote machine.
Many thanks for your helping...
Regards,
Bondan Wisnuwardhana
|
|
|
|
 |
|
 |
download and install this file
#Download details: 2007 Office System Driver: Data Connectivity Components
http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
 |
|
 |
Dear,
I had download and installed AccessDatabaseEngine.exe, but it is error again. The message is : "ODBC--Call Failed.". Could you give me some clues?
Many thanks,
Bondan Wisnuwardhana
|
|
|
|
 |
|
 |
Just make sure that you do not have the same table name in the Database and it should work
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
 |
|
 |
Dear,
Thank you, it works I want to know how to import data from excel to table that already exist in the database? Many thanks for your helping...
Bondan Wisnuwardhana
|
|
|
|
 |
|
 |
check this link
http://davidhayden.com/blog/dave/archive/2006/05/31/2976.aspx
or
you can load the datasheet into a datatable then loop the records and
insert them one by one
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
 |
|
 |
Thank you for your helping. I want to know again, how to select only particular rows of excel to import. Example I have 100 rows in excel file, and I need to copy row number 20 until row number 30 only into database.
Many thanks...
|
|
|
|
 |
|
 |
try this code
private void button2_Click(object sender, EventArgs e)
{
System.Data.OleDb.OleDbDataAdapter DA = new System.Data.OleDb.OleDbDataAdapter("aaa", "aaa");
int StartRecode = 1;
int MaxRecord = 10;
System.Data.DataTable[] DT = new DataTable[1];
DA.Fill(StartRecode, MaxRecord, DT);
}
Method DA.Fill() has many parameters that can help you, and the code above is one of them.
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
 |
|
 |
I'm using the code to convert excel to sql and i'm getting the error "ODBC -- call failed"
what are the causes of this error and how do i correct it. i'm using vb.net 2008 and 0ffice 2007(.xlsx).It's very urgent.
How do i also import an excel file in an existing table?
Below is my code for the datamanage
'
'
Public Sub importToServer(ByVal ExcelPath As String, ByVal ServerName As String, ByVal DBName As String, ByVal UserName As String, ByVal Password As String, ByVal InsertedTableName As String)
Try
Dim ExceCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelPath & ";Extended Properties=Excel 8.0"
Dim excelConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ExceCon)
excelConnection.Open()
Dim OleStr As String = "SELECT * INTO [ODBC; Driver={SQL Server};" & "Server=" & ServerName & ";Database=" & DBName & ";Uid=" & UserName & ";Pwd=" & Password & "].[" & InsertedTableName & "] FROM [Sheet1$];"
Dim excelCommand As New System.Data.OleDb.OleDbCommand(OleStr, excelConnection)
excelCommand.ExecuteNonQuery()
excelConnection.Close()
Catch ex As Exception
Throw New Exception("Error: " & ex.Message & "source " & ex.Source)
End Try
End Sub
'
'
'
'
|
|
|
|
 |
|
 |
check if the inserted table name exist in the targeted DataBase if it does exist change the new or the old table name
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
 |
|
 |
error "ODBC -- call failed"
exactly the same error here!
And my table exists and i need to use that name for later reporting...
Public Class ImportarFile
If File.Exists(ExcelPath) AndAlso (Ext.Equals("xls") Or Ext.Equals("xlsx")) Then
dm.importToServer(OpenFileDialog1.FileName, _
"server", _
"TELEMARKETING", _
"user", _
"pass", _
"LEADS")
...
end class
Public Class Datamanage
Dim ExceCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ExcelPath & "; Extended Properties=Excel 8.0"
Dim excelConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ExceCon)
excelConnection.Open()
Dim OleStr As String = "SELECT * INTO [ODBC; Driver={SQL Server};Server=" & ServerName _
& ";Database=" & DBName _
& ";Uid=" & UserName _
& ";Pwd=" & Password _
& "; ].[" & InsertedTableName _
& "] FROM [Folha1$];"
...
end class
I put Folha1 instead of sheet becuase it wasnt working because of office language for amazing at it seems...
then later testing happen "ODBC -- call failed"
Can someone help please?
|
|
|
|
 |
|
|
 |
|
 |
Salam
This code creates the table then inserts the Excel data, so try to delete the table then execute the code and if it is not solved email me again.
Thanks.
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
 |
|
 |
I need to export SQL Server database to Oracle, Please let me know if it is possible to do with any open source technique/tools
Thanks,
dnpro
"Very bad programmer"
|
|
|
|
 |
|
 |
This kind of import into DB has troubles which I had faced earlier.
When you have numbers and strings in the same column. Strings are not saved into SQL Server, only numbers are saved.
I could resolve the problem using import using FileHelpers.
|
|
|
|
 |
|
 |
Thanks for your note but it is working fine with me, all values are imported Successfully. if there is a special scenario just post it to me to test it.
Thanks
Mohammad Al Hoss
Development To Me Is A Pleasure more than a Job
|
|
|
|
 |
|
|
 |