Commands creating format files to import txt file into sql server

Question

I'm trying to create a format file using bcp utility following the instructions on this page:

Using a format file to bulk import data

https://technet.microsoft.com/en-us/library/ms178129(v=sql.105).aspx

However, this page only discusses how to create format files for tables in the databases. My target is creating a format file for a txt file on my disk and then using the format file to import the raw data in the text file to the database. I tried the following command:

bcp C:\data.txt format nul -c -f txt1.fmt -T

and get the following error:

A valid table name is required for in, out, or format options.

I'm using sql server 2016 and my system is windows 10 64 bit.

Can anyone help me with this, thanks in advance for your help!

Jason

Answer 1

You need to put in the full path for your format file. Use the table of the database where you will be importing the data to create your format file:

bcp Database.dbo.table format nul -f c:\txt1.fmt -c -T

Once the format file is created, then you can import the data from the txt file into your database using the format file:

bcp Database.dbo.table in c:\data.txt -f c:\txt1.fmt -T

https://msdn.microsoft.com/en-us/library/ms162802.aspx

Answer 2

I also write a blog post before on how to generate a text-only format file from PowerShell for a simple delimited file.

function Create-SqlFormatFile {
    param (
        [Parameter(Mandatory = $true)]
        $SourceFilename,
        $Delimiter = "\t"
    )

    $headerList = New-Object System.Collections.ArrayList

    foreach ($base in ((Get-Content $SourceFilename | Select -First 1) -split $Delimiter)) {
        $field = $base
        $number = 0
        while ($headerList -contains $field) {
            $field = $base + ($number++)
        }

        [void] $headerList.Add($field)
    }

    $format = New-Object System.Text.StringBuilder
    [void] $format.AppendLine(@"
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
"@)

    $headerList | %{
        [void] $format.AppendLine(@" 
  <FIELD ID="$_" xsi:type="CharTerm" TERMINATOR="$(if ($_ -eq $headerList[-1]) { "\r\n" } else { $Delimiter })" />
"@)
    }

    [void] $format.AppendLine(@"
</RECORD>
<ROW>
"@)

    $headerList | %{
        [void] $format.AppendLine(@"
  <COLUMN SOURCE="$_" NAME="$_" xsi:type="SQLNVARCHAR" NULLABLE="YES" />
"@)
    }

    [void] $format.AppendLine(@"
</ROW>
</BCPFORMAT>
"@)

    Set-Content -Path "$($SourceFilename).fmt" -Value $format.ToString()
}