Import the file into Excel by first opening excel, then going to DATA, import from TXT File, choose the csv extension which will preserve 0 prefixed values, and save that column as TEXT because excel will drop the leading 0 otherwise (DO NOT double click to open with Excel if you have numeric data in a field starting with a 0 [zero]). Then just save out as a Tab Delimited Text file. When you are importing into excel you get an option to save as GENERAL, TEXT, etc.. choose TEXT so that quotes in the middle of a string in a field like YourCompany,LLC are preserved also...

BULK INSERT dbo.YourTableName
FROM 'C:\Users\Steve\Downloads\yourfiletoIMPORT.txt'
WITH (
FirstRow = 2, (if skipping a header row)
FIELDTERMINATOR = '\t',
ROWTERMINATOR   = '\n'
)

我希望我可以使用FORMAT和Fieldquote功能，但在我的SSMS版本中似乎不支持

如果有人想使用powershell导入csv

## Install module if not installed, this is a one time install.
Install-Module SqlServer

## Input SQL Server Variables and CSV path
$csvPath = "D:\Orders.csv"
$csvDelimiter = ","
$serverName = "DESKTOP-DOG5T0Q\SQLEXPRESS"
$databaseName = "OrderDetails"
$tableSchema = "dbo"
$tableName = "Orders"

## Truncate Table
Invoke-Sqlcmd -ServerInstance $serverName -Database $databaseName -Query "TRUNCATE TABLE $tableSchema.$tableName"

## Import CSV into SQL
Import-Csv -Path $csvPath -header "Id","Country","Price","OrderQuantity" -Delimiter $csvDelimiter | Write-SqlTableData -ServerInstance $serverName -DatabaseName $databaseName -SchemaName $tableSchema -TableName $tableName -Force

导入csv到SQL server(使用SSMS查询或不查询)

也许不是你想要的，但另一种选择是为notepad++使用CSV Lint插件

该插件可以事先验证csv数据，这意味着检查坏数据，如缺少引号、不正确的十进制分隔符、日期时间格式错误等。而不是BULK INSERT，它可以将csv文件转换为SQL插入脚本。

SQL脚本将为每1000条记录的csv行包含INSERT语句，并调整任何datetime和十进制值。该插件自动检测csv中的数据类型，它将包括一个CREATE TABLE部分，其中包含每个列的正确数据类型。

Import the file into Excel by first opening excel, then going to DATA, import from TXT File, choose the csv extension which will preserve 0 prefixed values, and save that column as TEXT because excel will drop the leading 0 otherwise (DO NOT double click to open with Excel if you have numeric data in a field starting with a 0 [zero]). Then just save out as a Tab Delimited Text file. When you are importing into excel you get an option to save as GENERAL, TEXT, etc.. choose TEXT so that quotes in the middle of a string in a field like YourCompany,LLC are preserved also...

BULK INSERT dbo.YourTableName
FROM 'C:\Users\Steve\Downloads\yourfiletoIMPORT.txt'
WITH (
FirstRow = 2, (if skipping a header row)
FIELDTERMINATOR = '\t',
ROWTERMINATOR   = '\n'
)

我希望我可以使用FORMAT和Fieldquote功能，但在我的SSMS版本中似乎不支持

2)如果客户端从excel中创建了csv，那么有数据 逗号被括在"…(双引号)[如下所示 那么导入如何处理这个问题呢?

你应该使用FORMAT = 'CSV'， FIELDQUOTE = '"'选项:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '"',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

如上所述，您需要添加FORMAT和FIELDQUOTE选项来批量将. csv数据插入SQL Server。对于你的case, SQL语句是这样的:

BULK INSERT SchoolsTemp
FROM 'C:\CSVData\Schools.csv'
WITH
(
    FORMAT = 'CSV', 
    FIELDQUOTE = '""',
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    TABLOCK
)

虽然SSMS中的BULK INSERT非常适合一次性导入作业，但根据您的用例，您可能需要SSMS内部或使用第三方的其他选项。下面是一个详细的指南，描述了将CSV文件导入SQL Server的各种选项，包括自动化(我的意思是计划)过程和为CSV位置指定FTP或文件存储的方法。