Solved

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

Posted on 2007-12-03
11
1,694 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

758 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