Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1703
  • Last Modified:

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
0
MIKE
Asked:
MIKE
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
derobyCommented:
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
 
derobyCommented:
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
 
randomjamesCommented:
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
 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now