[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Formatting on imported tables

Posted on 2011-05-03
5
Medium Priority
?
376 Views
Last Modified: 2012-06-21
Started working at a new company and inherited a few MSWorks 'databases'. I've tinkered some and was able to export those eventually into SQL tables in my own database using Stored Procedures, but there are quotes around all the data. Is there a way I can do this again and remove the quotes?

This company has been entering data in these works tables since around 1996, so there is a lot of data that I'd rather not do over. I'm not a big fan of Access either.

Thanks.
0
Comment
Question by:General_GSpot
  • 2
  • 2
5 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 35516231
Use Save As to convert the entire Works db a .dbf (DBASE IV) file.  That will give you a better quality import.

Too bad you aren't a fan of Access.  Because from Access you can Link to the .dbf file and have your way with the tables, including running queries to populate the SQL server tables.

 
0
 
LVL 1

Author Comment

by:General_GSpot
ID: 35516269
Well, I can become a fan if it saves me the trouble. And I didn't mean to disparage Access, I just meant I'd rather have SQL as my database for the long term.

So use the same method you first mentioned to get it into access, meaning save as dbf, then open that with access?

I will try both now and let you know, thanks.
0
 
LVL 1

Author Comment

by:General_GSpot
ID: 35516498
@dqmq,

You are correct, the data is a lot neater in the dbf file, with a bit of missing data, but that's fine since it's the earlier stuff.

Do I run a stored proc like the one I used for the original csv file but using the dbf? Can you give me some pointers as to how I should code the stored proc to get the dbf to SQL?

Thanks.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35689039
Directly use Import export wizard to insert data from DBF to SQl Server.
0
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 35690773
To find the import wizard, richt-click the database in SSMS Object Explorer, then select Tools, import.  

You can also use SSIS or if you want to write your own import statement, it goes like this:

insert into table_name
  select * from OPENROWSET
('MICROSOFT.JET.OLEDB.4.0','dBase IV;HDR=NO;IMEX=2;
DATABASE=PAth_of_folder','select * from File_name.dbf')

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

825 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