Solved

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

Posted on 2007-12-03
11
1,695 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
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

864 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