Formatting on imported tables

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.
LVL 1
General_GSpotAsked:
Who is Participating?
 
dqmqConnect With a Mentor Commented:
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
 
dqmqCommented:
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
 
General_GSpotAuthor Commented:
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
 
General_GSpotAuthor Commented:
@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
 
Alpesh PatelAssistant ConsultantCommented:
Directly use Import export wizard to insert data from DBF to SQl Server.
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.