Solved

Best way to get Excell Files into SQL2000 in VB

Posted on 2004-08-11
29
472 Views
Last Modified: 2013-12-25
Hi:
I have many files in on a network drive. I want to write most efficient code to import all the files into one table.
Note: I need to create the table based on what is in the files. For example if the excel file has three columns called col1, col2, col3 and col55 I need to create a table [TABLE1] in sql and put one set of files into it. Then I have some SPs I would run. After that I will move  SELECTED data into another table and delete the [TABLE1] table.

This step will repeat again and again as new batch of excell files may have col2, col5, col1, col44, col3 etc. But here is good part. the selective data I need from all the forms will be same. for example from above I will be doing
INSERT INTO [TABLE 22]  SELECT col1, col3 FROM [TABLE1]

I do not want any links as they disappear for the next user. That why I am giving 500 points for it. I want full example code. I am open to any suggestions.
I am using SQL 2000 and VB6
Thanks
dhamijap
0
Comment
Question by:dhamijap
  • 15
  • 14
29 Comments
 
LVL 29

Accepted Solution

by:
leonstryker earned 500 total points
ID: 11778103
MS Knowledge Base Article 321686:

http://support.microsoft.com/default.aspx?scid=kb;en-us;321686

SUMMARY
This step-by-step article demonstrates how to import data from Microsoft Excel worksheets into Microsoft SQL Server databases by using a variety of methods.

back to the top
Description of the Technique
The samples in this article import Excel data by using:
SQL Server Data Transformation Services (DTS)
SQL Server linked servers
SQL Server distributed queries
ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
ADO and the Microsoft OLE DB Provider for Jet 4.0
back to the top
Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
Available instance of Microsoft SQL Server 7.0 or Microsoft SQL Server 2000
Microsoft Visual Basic 6.0 for the ADO samples that use Visual Basic
Portions of this article assume that you are familiar with the following topics:
Data Transformation Services
Linked servers and distributed queries
ADO development in Visual Basic
back to the top
Samples
Import vs. Append
The sample SQL statements that are used in this article demonstrate Create Table queries that import Excel data into a new SQL Server table by using the SELECT...INTO...FROM syntax. You can convert these statements to Append queries by using the INSERT INTO...SELECT...FROM syntax while you continue to reference the source and destination objects as shown in these code samples.

back to the top
Use DTS
You can use the SQL Server Data Transformation Services (DTS) Import Wizard to import Excel data into SQL Server tables. When you are stepping through the wizard and selecting the Excel source tables, remember that Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$), and that plain object names without the dollar sign represent Excel named ranges.

back to the top
Use a Linked Server
To simplify queries, you can configure an Excel workbook as a linked server in SQL Server. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
306397 HOWTO: Use Excel with SQL Server Linked Servers and Distributed Queries

The following code imports the data from the Customers worksheet on the Excel linked server "EXCELLINK" into a new SQL Server table named XLImport1: SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
                        
You can also execute the query against the source in a passthrough manner by using OPENQUERY as follows: SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')
                        
back to the top
Use Distributed Queries
If you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables: SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
                        
back to the top
Use ADO and SQLOLEDB
When you are connected to SQL Server in an ADO application by using Microsoft OLE DB for SQL Server (SQLOLEDB), you can use the same "distributed query" syntax from the Using Distributed Queries section to import Excel data into SQL Server.

The following Visual Basic 6.0 code sample requires that you add a project reference to ActiveX Data Objects (ADO). This code sample also demonstrates how to use OPENDATASOURCE and OPENROWSET over an SQLOLEDB connection.     Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
        "Initial Catalog=<database>;User ID=<user>;Password=<password>"

    'Import by using OPENDATASOURCE.
    strSQL = "SELECT * INTO XLImport6 FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=C:\test\xltest.xls;" & _
        "Extended Properties=Excel 8.0')...[Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and object name.
    strSQL = "SELECT * INTO XLImport7 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "[Customers$])"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and SELECT query.
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "'SELECT * FROM [Customers$]')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing
                        
back to the top
Use ADO and the Jet Provider
The sample in the preceding section uses ADO with the SQLOLEDB Provider to connect to the destination of your Excel-to-SQL import. You can also use the OLE DB Provider for Jet 4.0 to connect to the Excel source.

The Jet database engine can reference external databases in SQL statements by using a special syntax that has three different formats:
[Full path to Microsoft Access database].[Table Name]
[ISAM Name;ISAM Connection String].[Table Name]
[ODBC;ODBC Connection String].[Table Name]
This section uses the third format to make an ODBC connection to the destination SQL Server database. You can use an ODBC Data Source Name (DSN) or a DSN-less connection string: DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]
                        
The following Visual Basic 6.0 code sample requires that you add a project reference to ADO. This code sample demonstrates how to import Excel data to SQL Server over an ADO connection by using the Jet 4.0 Provider.     Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"
   
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
       
    cn.Close
    Set cn = Nothing
                        
You can also use this syntax, which the Jet Provider supports, to import Excel data into other Microsoft Access databases, indexed sequential access method (ISAM) ("desktop") databases, or ODBC databases.

back to the top
Troubleshooting
Remember that Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$) and that plain object names represent Excel named ranges.
In some circumstances, especially when you designate the Excel source data by using the table name instead of a SELECT query, the columns in the destination SQL Server table are rearranged in alphabetical order.For additional information about this problem with the Jet Provider, click the article number below to view the article in the Microsoft Knowledge Base:
299484 PRB: Columns Are Sorted Alphabetically When You Use ADOX to Retrieve Columns of Access Table

When the Jet Provider determines that an Excel column contains mixed text and numeric data, the Jet Provider selects the "majority" data type and returns non-matching values as NULLs.For additional information about how to work around this problem, click the article number below to view the article in the Microsoft Knowledge Base:
194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset

back to the top
REFERENCES
For additional information about how to use Excel as a data source, click the article number below to view the article in the Microsoft Knowledge Base:
257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA

For additional information about how to transfer data into Excel, click the article numbers below to view the articles in the Microsoft Knowledge Base:
295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO

247412 INFO: Methods for Transferring Data to Excel from Visual Basic

246335 HOWTO: Transfer Data from an ADO Recordset to Excel with Automation

319951 HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services

306125 HOW TO: Import Data from SQL Server into Microsoft Excel

Leon
0
 

Author Comment

by:dhamijap
ID: 11786232
leonstryker:
I have decided to use this method from your comments.
Use Distributed Queries
If you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables: SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
                   
Now I have created a connection and when execute it gives me error "The connection can be used to perform this operation..."
Can you please give me the example code for this part?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11786506
This method uses T-SQL to import Excel data into SQL Server.  Here is more information on this method:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306397

The most important section is the one on configuring Excel as a SQL Server linked server.

Leon


0
 

Author Comment

by:dhamijap
ID: 11786610
leonstryker:
I need to do it in VB?
Here is my code:
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim sSQL As String
Dim sNextExcelFile As String
Dim I As Integer

    Set oConn = New ADODB.Connection
    oConn.ConnectionTimeout = 120
    oConn.Open gCS

    Set oCmd = New ADODB.Command
    oCmd.CommandType = adCmdText
    oCmd.ActiveConnection = oConn
   
    For I = 1 To UBound(msSelectedFiles)
           
        sNextExcelFile = msSelectedFiles(I)
        sSQL = "SELECT * INTO CVDP_DataPort FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database= " &   NextExcelFile & "', [Sheet1$])"
        oCmd.CommandText = sSQL
        oCmd.Execute
        'Move data to right table
        'drop table
    Next
   
    Set oConn = Nothing
    Set oCmd = Nothing
===========================================
now i am getting
OLEDB provider "Microsoft.jet.oledb.4.0 reported an error. Ther provider did not give information about the error!
error -2147217900
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11786709
Take another look at the sample I think you got it reversed and loosse the Command object:

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"                    '<---------  this is the connection to Excel
   
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"                                   '<--------- this connects to the SQL Server
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
       
    cn.Close
    Set cn = Nothing

I would set up a separate function to do the actual import and pass the Excel file paths and names to this function in a loop.

Leon
0
 

Author Comment

by:dhamijap
ID: 11786842
leonstryker:
I thought of using this method. It uses two connections. the OPENROWSET as from table was very interesting in the method I wanted to use. So, you do not want me to go ahead with that method. I thought it was very simple and straight forward.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11787107
>>the OPENROWSET as from table was very interesting in the method I wanted to use.

If you are refereing to the sample provided under: Use Distributed Queries, then you must understand that it is used from SQL Server itself (from the query analyzer) as T-SQL and not from VB.

Leon
0
 

Author Comment

by:dhamijap
ID: 11787129
I did not know that. So, I have to use what you wrote earlier. I will start with that and let you know.
0
 

Author Comment

by:dhamijap
ID: 11792885
leonstryker:
I am very close to it. I am facing the problem of selecting certain columns form Sheet1$
Here is debug.print of my sql statement
INSERT INTO [odbc;Driver={SQL Server};Server=MKDCOVSQL07;Database=s341_001;Integrated Security=SSPI].CVDP_DataPort (Store, Brand, [Merch Net Sale Units], [Merch Net sale Dollars]) SELECT F,L,M,N FROM [Sheet1$]

FLMN are the column names I want to import.

Can you please tell me what is wrong here?
dhamijap
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11793173
Are "FLMN" the actual column names or are they columns F, L, M, & N?
0
 

Author Comment

by:dhamijap
ID: 11793883
They are columns F, L, M, & N which excel sheet has when you open new blank sheet.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11793981
From: http://support.microsoft.com/default.aspx?scid=kb;en-us;278973

With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).

So try this:
SELECT F6, F12, F13, F14 FROM [Sheet1$]

Leon
0
 

Author Comment

by:dhamijap
ID: 11794293
leonstryker
When I added HDR=NO:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\ColeVisonDataPort\ExcelFiles\file 2 - BJ's.xls;Extended Properties=Excel 8.0; HDR=NO;
This gives me:
"Could not find installable ISAM"
Error
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11794801
Please try this as the string.  Note the extra quotes around the Extended Properties part:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\Data\ColeVisonDataPort\ExcelFiles\file 2 - BJ's.xls;" & _
           "Extended Properties=""Excel 8.0;HDR=No"""

Leon
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:dhamijap
ID: 11795899
leonstryker:
I am getting error "No value given for one or more required parameters"
I check the excel sheet row 2 does not have any data in one of the fields. I can I tell that the data starts at row 3?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11796102
>>I am getting error "No value given for one or more required parameters"
I check the excel sheet row 2 does not have any data in one of the fields. I can I tell that the data starts at row 3?

No, I do not think that it is possible.  You would be able to do something like skiping a row, if you were doing an Insert row by row, but not this way.

You should check your Excel data before import to avoid such errors.

Leon
0
 

Author Comment

by:dhamijap
ID: 11796150
ok, users donot open the file and work with it. but is there any way that in VB I can create a range in the sheet1? If so then I can use that range in select stmt. how about that?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11796296
>>but is there any way that in VB I can create a range in the sheet1?

Sure, but you will need to establish a reference to Excel, then programmatically open the workbook, define the range, add it to the Applicaiton.Names collection and save the workbook again.  Depending on the size of your files this may take a while.

Leon
0
 

Author Comment

by:dhamijap
ID: 11809926
Leon:
I am very close to get it done. I continued without having to "to establish a reference to Excel" solution. Here is my SQL that inserts the data into the SQL server. Here is the problem.

In my INSERT INTO statement I have spaces in the excel sheet column headers. for example:
Insert into ... ([First Name]...) SELECT [First. Name]... FROM [Sheet1$]
Notice there is a DOT after First in Select part of the statement.
It gives me INVALID BRACKETING OF NAME '[First. Name]' error.

I hope it is very simple for you. I think it is punctuation problem.
dhamijap
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11811011
Get the value of the SQL string in the debug window and lets take a look at it.  The fields must match exactly.

Leon
0
 

Author Comment

by:dhamijap
ID: 11811087
Leon:
Here it is:
INSERT INTO [odbc;Driver={SQL Server};Server=MKDCOVSQL07;Database=s341_001;Integrated Security=SSPI].CVDP_dhamijapr01 (Store, Brand, [Merch Net Sale Units], [Merch Net Sale Dollars]) SELECT Store,Brand,[Merch. Net Sales Units],[Merch. Net Sales $] FROM [Sheet1$]

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11811283
Is it possible for to take out the spaces and the dots from the Excel header.  This is just a test.  I want to see if everything else works.  If it does then you should add a space to one, then all the spaces, then the dot.  Lets see what it does not like.

Leon
0
 

Author Comment

by:dhamijap
ID: 11812622
Leon:

I made it simple and the last two columns are "UnitSales and SalesDollars" then it worked. But I need to keep the spaces. For example [Unit Sales]

I am going to try again with [] and again with [] and space. In the mean time please let me know what you think? Thanks
dhamijap
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11812881
Are you saying that it does not work with [Unit Sales]? Would it be possible to use underscore character like: Unit_Sales?

Leon
0
 

Author Comment

by:dhamijap
ID: 11823749
Leon:
Finaly, I got it working. It takes [Column Name] with spaces from excel sheet. you dis very well and I thank you a lot. you have helped me before.

Now I need to put back the errors in excell sheet. so I need to open a sheet and insert the data from sql. In other words reverse of it. I may be post this if I could not get it going.
dhamijap
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11823956
Sure, thanks for the grade.

BTW, which version of Excel are you using?  Puting data into Excel is very easy.  Let me know if you need help.

Leon
0
 

Author Comment

by:dhamijap
ID: 11824106
Ver 2002 SP3
If it is one line fine otherwise I will post a question and send you link here. THanks
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11824451
Once you get the recordset, create an instance of Excel, add a workbook and then use the CopyFromRecodset method of the Excel range object.

Good Luck,
Leon
0
 

Author Comment

by:dhamijap
ID: 11829845
Leon:
here is link to my next question so I need more help on that please. If you think 50 points is not enough i can increase it. Please let me know
dhamijap
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_21098004.html
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now