ETL时遇到的坑之一

今天客户给的数据压缩包,先是在mac OSX上解压碰到乱码问题(已解决)(家中预览),然后发现一系列问题,

  • 所有的数据开始都有等号(=)
  • 部分数据结尾存在多余的逗号(,)
  • 订单表字段不统一(部分文件缺少末尾字段,这也许是和上个问题有关系)
  • 部分文件名命名错误,把订单明细命名成了订单

解决办法,使用sed批量去除头尾多余的字符(写了个简单的Windows批处理文件),并把订单和订单明细分到不同目录(原来有超过10个目录)

@echo off
@rem replace first 
md detail
md order
for /r %%i in (*宝贝*.csv) do @sed "s/^=//g" %%i |sed  "s/,$//g">detail\%%~ni.txt
for /r %%i in (*订单报表*.csv) do @sed "s/^=//g" %%i  >order\%%~ni.txt

@echo on

因为是csv,导入postgresql数据库,最早考虑的是copy命令,但尝试失败后(存在字段长度不统一的问题),决定使用R循环遍历文件来解决,先根据csv创建表(经过数据类型修改,否则存在类型不一致的报错)

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user='postgres', password='', dbname='ml', host='127.0.0.1')
postgresqlpqExec(con, "SET client_encoding = 'GBK'")

path<-"E:\\job\\abc\\raw\\order"
files <- list.files(path=path, pattern="*.txt", full.names=T, recursive=FALSE)

for(f in files) {
  df<-read.csv(f,encoding="GBK")
  #删除多余字段,因为有的csv存在这个字段,有的不存在,也可以根据需要,只保留想要的字段,让导入的数据更简洁。
  #不过一般我习惯先导入,分析后再过滤我需要的字段
  df<-df[ , !(names(df) %in% c('是否上传身份证'))] 
  #保存到abc schema,raw_order表中,追加模式
  dbWriteTable(con, c("abc","raw_order"), df, row.names = FALSE, append=T) 
}

数据导入完毕(忽略Order Detail的导入,这个比较顺利),接下来就是分析和建模了。