Thursday 18 December 2014

Import dbf database in sqlserver



Steps For Importing Dbf database into Sql Server

There is no appropriate way or direct way to do that but nothing is impossible so there is workaround that can be done to perform this task . Here is the steps :-

  • First we have to convert the .dbf format data into .csv format data
  • Then depending on table structure Create the Table in database one by one 
  • Then using import bulk options of sql server that accept the data in csv format we can easily insert data in sql server

Convert DBF to CSV

DBF Converter allows you convert a single dbf file or folder with dbf files to csv format from GUI or command line.
1. Select the dbf file or foder with dbf files for batch conversion.
2. Select the output csv file or folder for csv files for batch conversion.

Download Software 
dbf to csv conversion


3. Preview, select options for sorting, filtering data (if necessary)
You can also select/unselect columns, set order for columns.
common options for dbf to csv conversion

4. Select options for csv format:columns delimiter, rows delimiter (if necessary), and click "Finish"

CSV file format options


Import CSV File Into SQL Server Using Bulk Insert


 CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Steps For Importing CSV in Sql server 

  • Create The table whose data we are going to import into database

USE TestData
GO
CREATE TABLE
CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO


where TestData is database name and CSVTest is table name
  • I assume your .Csv file lies in c:\csvtest.txt and it contains data like this

    1,James,Smith,19750101
    2,Meggie,Smith,19790122
    3,Robert,Smith,20071101
    4,Alex,Smith,20040202




  • Then Run the following script in the query windows of sql server . Make sure your paths given are correct to .csv file

    Code


    BULK
    INSERT
    CSVTest
    FROM 'c:\csvtest.txt'
    WITH
    (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
    )
    GO
    --Check the content of the table.
    SELECT *
    FROM CSVTest
    GO
    --Drop the table to clean up database.
    DROP TABLE CSVTest
    GO

    Snapshot








Share this

0 Comment to "Import dbf database in sqlserver "

Post a Comment