Solved

How to Create SQL Tables using Excel Sheets as the basis for data.....?

Posted on 2007-12-03
11
1,696 Views
Last Modified: 2012-06-27
Experts:

I use the FREE version of SQL Server 2005. It's call MS SQL Server Management Studio EXPRESS.

Does this version allow me to use Excel Sheets to IMPORT data into data TABLES in SQL Server...?

Currently,...I use Crystal Reports to "extract" the data from the Excel Sheets then I use Crystal's EXPORTING program to export the data into a SQL TABLE. Can the SQL Server EXPRESS edition do this instead of my having to use Crystal Reports?

And if so,.....HOW ....do I do it....?

Thanks
MIkeV
0
Comment
Question by:MIKE
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20395707
Better try using SQL Management Studio.

Save the Excel file as dbf.
The open SQL Management Studio.
Expand the Database tab.Right click on the database, choose import. In the database choose the excel file and proceed further based on your need.

0
 
LVL 17

Author Comment

by:MIKE
ID: 20395777
I do NOT have an IMPORT option under that menu....here is the menu I have when I rightclick on Database:

NEW DATABASE
ATTACH
RESTOR DATABASE
RESTORE FILES AND FILEGROUPS
REPORTS
REFRESH

When I do NEW DATABASE, I get what appears to be a new database setupwindow...but I don't see any options for IMPORTING....

AGAIN...I'm using the FREE.......EXPRESS management studio....


MIkeV
0
 
LVL 11

Expert Comment

by:deroby
ID: 20395792
@shasunder : Not sure how you save an excel workbook or sheet as a .dbf, don't see it in my "Save As ..." list !? (Excel 2007)

There's a couple of ways to do this :
* Save the excel worksheet(s) as .csv file(s) and use bcp (I assume bcp.exe is present in SQL Express, correct me if I'm wrong) to load the data in the relevant mssql tables
=> problem here might be that you will need to find 'a good' .csv format that you can read again with bcp.exe using the correct field-terminator.

* Write some VBA code that loops over the rows, creates an INSERT() script for each row and save the entire script into a file, then open the file with SQL Mgmt Studio and run it
=> Doing many inserts one at a time will prove to be rather slow for large volumes, but it is the most flexible way to do things. Remember to put "SET NOCOUNT ON" on top of the file

* Write some VBA code that loops over the rows, creates an INSERT() script for each row and have each row executed on the server using ADO.
=> bit more complex than above, but would make things a 'single button operation'

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 17

Expert Comment

by:Shanmuga Sundaram
ID: 20395805
Try below the child levels of "Databases". you have right clicked on "Databases".
0
 
LVL 17

Author Comment

by:MIKE
ID: 20395927
Well when I righclick in the "child" area, I get this menu:

NEW DATABASE
NEW QUERY
SCRIPT DATABASE AS
TASKS
REPORT
RENAME
DELETE
REFRESH
PROPERTIES

Am I missing something......?
0
 
LVL 11

Assisted Solution

by:deroby
deroby earned 200 total points
ID: 20395956
Tasks should expand further down to the "Import..." menu that shasunder is talking about
0
 
LVL 17

Author Comment

by:MIKE
ID: 20395975
Tasks menu takes me here...:

DETACH
SHRINK
BACKUP
RESTORE
GENERATE SCRIPTS

Please remember this is the EXRESS edision...are YOU currently using the Express edition to IMPORT Excel tables......in to SQL tables...????
0
 
LVL 11

Assisted Solution

by:deroby
deroby earned 200 total points
ID: 20396271
Nope, just trying to help, ...  I don't have Express here... hence my suggestions above that should work on, well, every version of MSSQL ...
0
 
LVL 3

Accepted Solution

by:
randomjames earned 300 total points
ID: 20400122
You aren't likely going to do this through a GUI if you are using Express Edition. Most likely BCP is your best bet as deroby suggested.

If you are decent with Access then you could rig something using Access as a front-end for your SQL Server database and bring it in through Access.

0
 
LVL 17

Author Comment

by:MIKE
ID: 20403524
Ok...so basically the answer is SQL SERVER EXPRESS cannot do what I need it to do.

I will continue to use a Crystal Report to extract the data from my Excel Sheet and then just have Crystal Export the data into a SQL TABLE...this seems to work fine....I was just hoping to consolidate this process into SQL Server EXPRESS.......

Thanks again....

MikeV
0
 
LVL 3

Expert Comment

by:randomjames
ID: 20403635
Well, BCP is a command-line utility that comes with SQL Server (including Express Edition).

Basically you would need to write a little batch file that would run BCP with the command-line parameters that you need, or you could just call BCP within a stored procedure using the system extended procedure MASTER.DBO.xp_cmdshell.

There's other ways to do it but that would probably be the easiest though maybe not the most graceful...
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 32
Simple SQL query from two tables 13 54
What is the proper way to use for criteria in left join? 7 28
Run SQL Server Proc from Access 11 31
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

803 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