Note: Census data is used in the following example.
- Download the product delivery file.
- Extract the delivery file to find the text data file.
- Create the table structure in the database using the Create Table script. Be sure to change the table name in the template to the dataset you are loading. If, for example, you are loading the Census dataset, the table should be named census.
- Please refer to [link] if a new format (FMT) file needs to be created. https://msdn.microsoft.com/en-us/library/ms191516.aspx.
- Issue the following
command:
Syntax:bcp <database_name>.dbo.<table_name> format nul -c -f <path>\format_file_name.fmt -t "|" -S -T-T: Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. If -T is not specified, the -U and -P parameters for username and password must be passed in order to log in.
-t "|": Specifies that the format file will be generated using the pipe character as a delimiter.
Example:bcp market.dbo.index format nul -c -f C:\census\index.FMT -t "|" -S -T
Note: Once the format file has been generated, \r needs to be removed from the file in
order to load data successfully.