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

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
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?
 
randomjamesConnect With a Mentor Commented:
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
 
Shanmuga SundaramDirector of Software EngineeringCommented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
derobyCommented:
@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
 
Shanmuga SundaramDirector of Software EngineeringCommented:
Try below the child levels of "Databases". you have right clicked on "Databases".
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
derobyConnect With a Mentor Commented:
Tasks should expand further down to the "Import..." menu that shasunder is talking about
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
derobyConnect With a Mentor Commented:
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
 
MIKESoftware Solutions ConsultantAuthor Commented:
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
 
randomjamesCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.