Cyclistic共享单车案例研究：合并CSV导入SQL Server失败求助

正在开展Cyclistic共享单车案例研究，需通过R批量加载12个CSV文件并合并为单个CSV，再导入SQL Server做数据清洗分析。已用R成功生成合并后的CSV，但使用SSMS导入导出工具时失败，出现代码页冲突错误。

RStudio操作步骤 #

### Setting up environment
install.packages("tidyverse")
install.packages("data.table")
library(tidyverse)
library(data.table)

### Data source - CSV files 2022.06-2023-05 
### Loading and consolidating into a df
raw <- list.files(path = 'C:/edu/Google Data Analytics/Case_study1/s2023l12', full.names = TRUE)
cyclistic23 <- rbindlist(lapply(raw,fread), fill = TRUE)

### Exporting to a CSV file in order to import to MS SQL Server
write.csv(cyclistic23, 'C:/edu/Google Data Analytics/Case_study1/s2023l12/s2023l12.csv', row.names = FALSE, quote = FALSE)

导入错误信息 #

Validating (Error) - Messages

Error 0xc02020f4: Data Flow Task 1: The column "ride_id" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "rideable_type" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "started_at" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "ended_at" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "start_station_name" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "start_station_id" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "end_station_name" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "end_station_id" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "start_lat" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "start_lng" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "end_lat" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "end_lng" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc02020f4: Data Flow Task 1: The column "member_casual" cannot be processed because more than one code page (1252 and 1251) are specified for it.

Error 0xc004706b: Data Flow Task 1: "Destination - s2023l12" failed validation and returned validation status "VS_ISBROKEN".

Error 0xc004700c: Data Flow Task 1: One or more component failed validation.

Error 0xc0024107: Data Flow Task 1: There were errors during task validation.

原因分析 #

1. 原始CSV文件编码不一致：部分文件使用Windows-1252（英文默认编码），部分使用Windows-1251（西里尔字母编码），合并后导出的CSV未统一编码，导致SSMS导入时识别到多种代码页冲突。
2. write.csv默认使用系统本地编码，未指定统一的UTF-8编码，进一步加剧了编码混乱。

解决办法 #

方法1：在R中统一编码后导出CSV #

修改导出代码，指定带BOM的UTF-8编码（方便SSMS自动识别）：

# 替换原write.csv语句
write.csv(cyclistic23, 
          'C:/edu/Google Data Analytics/Case_study1/s2023l12/s2023l12.csv', 
          row.names = FALSE, 
          quote = FALSE,
          fileEncoding = "UTF-8-BOM")

方法2：导入时强制指定统一代码页 #

若不想重新生成CSV，可在SSMS导入向导中调整配置：

• 在选择数据源步骤，将「编码」统一设为1252 (Windows-1252)或65001 (UTF-8)。
• 进入高级选项，为所有冲突列手动指定相同的代码页（如全部设为1252）。

方法3：直接从R连接SQL Server写入数据（跳过CSV环节） #

彻底规避中间文件的编码问题，用odbc包直接将数据写入SQL Server：

install.packages("odbc")
library(odbc)

# 建立数据库连接
conn <- dbConnect(odbc(),
                  Driver = "SQL Server",
                  Server = "你的服务器名",
                  Database = "你的数据库名",
                  UID = "用户名",
                  PWD = "密码")

# 将数据写入SQL Server表
dbWriteTable(conn, name = "cyclistic23", value = cyclistic23, overwrite = TRUE)

# 关闭连接
dbDisconnect(conn)

内容的提问来源于stack exchange，提问作者Denis Mezenko