Formatting on imported tables

Posted on 2011-05-03
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.

Question by:General_GSpot
    LVL 42

    Expert Comment

    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.

    LVL 1

    Author Comment

    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.
    LVL 1

    Author Comment


    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?

    LVL 21

    Expert Comment

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

    Accepted Solution

    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
    DATABASE=PAth_of_folder','select * from File_name.dbf')


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    This article describes some very basic things about SQL Server filegroups.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 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

    11 Experts available now in Live!

    Get 1:1 Help Now