Solved

How can data from Excel be imported to a SQL Server Database?

Posted on 2008-10-30
6
204 Views
Last Modified: 2012-05-05
Hi Experts,

How can data from Excel be imported to a SQL Server Database programmatically?

This is my VBA code to do it in Access database but how can it be done in SQL server database?

Thanks

Option Compare Database
 
Public Sub Import_BW_Data()
 
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim tbl As TableDef
 
 
Dim strSQL As String
Dim strFilename As String
Dim ExecDataUpdate As Long
 
Dim PathIs As String
 
'Detect path
PathIs = Application.CurrentProject.Path
 
Set db = CurrentDb()
 
DoCmd.SetWarnings (False)
 
strSQL = "DELETE FROM [BW_FTT]"
db.Execute (strSQL)
 
strFilename = Dir$(PathIs & "\UploadedFiles\BW_FTT\*.xls")
 
While (Not (Len(Trim$(strFilename))) = 0)
 
    DoEvents
 
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel3, "BW_FTT_Import", PathIs & "\UploadedFiles\BW_FTT\" & strFilename, False
     
    'Append
    DoCmd.OpenQuery ("Append to BW_FTT")
 
    DoCmd.DeleteObject acTable, "BW_FTT_Import"   'Delete table
    
    FileCopy "" & PathIs & "\UploadedFiles\BW_FTT\" & strFilename & "", "" & PathIs & "\ImportedFiles\BW_FTT\" & strFilename & ""
 
    Kill "" & PathIs & "\UploadedFiles\BW_FTT\" & strFilename & ""
    
    strFilename = Dir$()
 
Wend
 
 
DoCmd.SetWarnings (True)
 
MsgBox ("BW FTT Data Imported")
 
End Sub

Open in new window

0
Comment
Question by:indyng
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22842960
You can do that also using SSIS (new version of DTS)
What is your doubt in you code?
Regards!
pedro
0
 
LVL 2

Expert Comment

by:BobTheViolent
ID: 22843350
To elaborate on what pedro said.  You can set up a DTS or SSIS package using the wizard or just building it yourself and you specify a connection to an xls file and tell it what sql queries or tables to pull from it etc...   Then you can call the package from a stored procedure, query or SQL server agent.  You could even call the package from Access VBA code.

"All Tasks" -> "Import Data" command if you right click while in the Enterprise Manager looking at the Tables view or with"Action" -> "All Tasks" -> "Import Data" from the top command bar.  Or just go to "Local Packages"  and create a new DTS or SSIS.
0
 
LVL 1

Author Comment

by:indyng
ID: 22844730
I don't think i can because i am running SQL Server Express 2005
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Expert Comment

by:BobTheViolent
ID: 22844987
On msdn there is a BCP utility documented that can transfer files or tables.  Maybe this would work for you.  http://msdn.microsoft.com/en-us/library/ms162802.aspx

I didn't realize "Express" did not have DTS/SSIS.  It looks like you may have to stick with pushing the data from Access to the SQL Server.

Is there a particular reason you do not want to do the transfer with VBA like you originally posted?
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 500 total points
ID: 22849098
SQL Server 2005 Express Edition does not include Integration Services (SSIS) or support for SQL Server 2000 DTS packages.

To read text file from SQL Server try this:
http://www.databasejournal.com/features/mssql/article.php/3584751
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1559168&SiteID=1
http://doc.ddart.net/mssql/sql70/sp_adda_17.htm

If you use OPENROWSET or OPENDATASOURCE you must enable that in SQL Configuration Surface Area (Features)

Helped?
Regards!
Pedro
SQL-Surface.JPG
0
 
LVL 1

Author Comment

by:indyng
ID: 22849850
BobTheViolent:

Can I do the transfer with VBA in SQL server? I thought i had to do the transfer in access and then push it to SQL server express 2005.

Thanks
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question