0

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

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

  • Thanks for your answer! Indeed, I need to put the target table in the command since there is a corresponding column in the format file. I'll check out your answer! – Jason Nov 25 '16 at 14:16
  • Your answer works for me! Thanks for your help! When I ran the command in the cmd window, it pops up command asking me to confirm file storage type/prefix/terminator, is there a way to escape these confirmations and just use the default settings? – Jason Nov 25 '16 at 15:16
  • I got some new problem specifying field terminator. After a struggling morning, I decided to post another question for that, do your mind taking a quick look at it? Thanks in advance for your help! – Jason Nov 25 '16 at 18:51
1

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()
}
  • Thanks for your reply! I didn't expect generating txt file is so complex and thought it would only be a couple of lines! I'll check it out. – Jason Nov 25 '16 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.