利用JAVA程序批量导入csv数据到MySQL数据库

最新推荐文章于 2024-05-11 21:30:00 发布
最新推荐文章于 2024-05-11 21:30:00 发布
阅读量2.7k 收藏 10
点赞数 2
文章标签： java mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/starshine/article/details/125972666
版权

       正在学习利用R进行统计学相关知识的实验，实验数据计划采用北京市环境监测数据，此数据可以在这个网址“https://quotsoft.net/air/”中下载，目前可提供2013年12月至今的北京35个监测站点针对PM2.5、PM10、CO~2等监测数据，数据的格式如下：

datehourtype东四天坛官园万寿西宫奥体中心农展馆
201601010PM2.5250193183195209218
201601010PM2.5_24h706463686568
201601010PM10209209244200268
201601010PM10_24h110139123148189
201601010AQI94958710012092
201601011PM2.5193183193190211251
201601011PM2.5_24h777270767377
201601011PM10203187233203248
201601011PM10_24h116143132153195

       针对每个监测项目，每个小时获取一次数据，均为标准的csv文件，每年的文件为700多个，在统计研究中，计划采用一年的数据，并且按天对各个监测项目作均值处理，由于文件比较多，如果一个个单独处理，然后再合并，工作量比较大，因此想将这些数据导入到MySQL数据库中进行处理使用。

       MySQL提供数据文件导入的方式有两种：

  • LOAD DATA INFILE SQL命令
  •  mysqlimport 命令行命令（本质上是LOAD DATA的另一个实现）

        导入LOAD DATA来完成由于所要导入的文件中存在空值，mysqlimport命令行无法处理（或者说我没找到处理办法），如果命令行能够支持就更方便了，根本不用JAVA来实现了，直接采用PowerShell脚本即可实现。

        LOAD DATA 语法如下：

 LOAD DATA
 [LOW_PRIORITY | CONCURRENT] [LOCAL]
 INFILE 'file_name'
 [REPLACE | IGNORE]
 INTO TABLE tbl_name
 [PARTITION (partition_name [, partition_name] ...)]
 [CHARACTER SET charset_name]
 [{FIELDS | COLUMNS}
 [TERMINATED BY 'string']
 [[OPTIONALLY] ENCLOSED BY 'char']
 [ESCAPED BY 'char']
 ]
 [LINES
 [STARTING BY 'string']
 [TERMINATED BY 'string']
 ]
 [IGNORE number {LINES | ROWS}]
 [(col_name_or_user_var
 [, col_name_or_user_var] ...)]
 [SET col_name={expr | DEFAULT}
 [, col_name={expr | DEFAULT}] ...]

        导入数据文件的JAVA程序代码如下：

package loadData;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import java.io.File;


public class loadData {

	public static void main(String[] args) throws SQLException {
		
		Connection conn = null;
		Statement  st =null;
		
		String strDataDirectory = "D:\\Work\\data\\2016";
		
		if (args.length > 0)
		{
			strDataDirectory = args[0];
			strDataDirectory = strDataDirectory.replace("\\", "\\\\");
		}
		
		try
		{
			 Connect to mysql
			
			Class.forName("com.mysql.cj.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC","root","passw0rd");
			
			st = conn.createStatement();
			//ResultSet  rs;
			Long rt;
			
			// Read Files List from Directory
			
		//String strDataDirectory ;
	    strDataDirectory = strDataDirectory;
	    		
		File[] fList;
		
		File f = new File(strDataDirectory);
		//if (f.isDirectory()) {
		fList = f.listFiles();
		//}
	
		// Load data to database
		
		for (int i = 0;i< fList.length;i++) {
			
			
			// Declare a filename string
			String strName = fList[i].getAbsolutePath().toString();
		    
			strName = strName.replace("\\", "/").trim();
			//String strSql = "LOAD DATA infile '" + strName + "' into table loadata fields terminated by ',' lines terminated by '\\n' (jcrq,jcsj,jcxm,@zd_xzm,@zd_dzm,@zd_dsm) set zd_xzm=if(@zd_xzm='',null,@zd_xzm),zd_dzm=if(@zd_dzm='',null,@zd_dzm),zd_dsm=if(@zd_dsm='',null,@zd_dsm)";
			String strSql = "LOAD DATA infile '" ;
			strSql = strSql + strName;
			strSql = strSql + "' into table bj_2016 fields terminated by ',' lines terminated by '\\r\\n' ignore 1 lines ";
			strSql = strSql + " (jcrq,jcsj,jcxm,@zd_ds,@zd_tt,@zd_gy,@zd_wsxg,@zd_atzx,@zd_nzg,@zd_wl,@zd_bbxq,@zd_zwy,@zd_fthy,@zd_yg,@zd_gc,@zd_fs,@zd_dx,@zd_yz,@zd_tz,@zd_sy,@zd_cp,@zd_mtg,@zd_pg,@zd_hr,@zd_my,@zd_yq,@zd_dl,@zd_bdl,@zd_mysk,@zd_dgc,@zd_yld,@zd_yf,@zd_llh,@zd_qm,@zd_ydmn,@zd_xzmb,@zd_nsh,@zd_dsh) ";
			strSql = strSql + " set zd_ds=if(@zd_ds='',null,@zd_ds),zd_tt=if(@zd_tt='',null,@zd_tt),zd_gy=if(@zd_gy='',null,@zd_gy),zd_wsxg=if(@zd_wsxg='',null,@zd_wsxg),zd_atzx=if(@zd_atzx='',null,@zd_atzx),zd_nzg=if(@zd_nzg='',null,@zd_nzg),zd_wl=if(@zd_wl='',null,@zd_wl),zd_bbxq=if(@zd_bbxq='',null,@zd_bbxq),";
			strSql = strSql + " zd_zwy=if(@zd_zwy='',null,@zd_zwy),zd_fthy=if(@zd_fthy='',null,@zd_fthy),zd_yg=if(@zd_yg='',null,@zd_yg),zd_gc=if(@zd_gc='',null,@zd_gc),zd_fs=if(@zd_fs='',null,@zd_fs),zd_dx=if(@zd_dx='',null,@zd_dx),zd_yz=if(@zd_yz='',null,@zd_yz),zd_tz=if(@zd_tz='',null,@zd_tz),";
			strSql = strSql + " zd_sy=if(@zd_sy='',null,@zd_sy),zd_cp=if(@zd_cp='',null,@zd_cp),zd_mtg=if(@zd_mtg='',null,@zd_mtg),zd_pg=if(@zd_pg='',null,@zd_pg),zd_hr=if(@zd_hr='',null,@zd_hr),zd_my=if(@zd_my='',null,@zd_my),zd_yq=if(@zd_yq='',null,@zd_yq),zd_dl=if(@zd_dl='',null,@zd_dl),";
			strSql = strSql + " zd_bdl=if(@zd_bdl='',null,@zd_bdl),zd_mysk=if(@zd_mysk='',null,@zd_mysk),zd_dgc=if(@zd_dgc='',null,@zd_dgc),zd_yld=if(@zd_yld='',null,@zd_yld),zd_yf=if(@zd_yf='',null,@zd_yf),zd_llh=if(@zd_llh='',null,@zd_llh),zd_qm=if(@zd_qm='',null,@zd_qm),";
			strSql = strSql + " zd_ydmn=if(@zd_ydmn='',null,@zd_ydmn),zd_xzmb=if(@zd_xzmb='',null,@zd_xzmb),zd_nsh=if(@zd_nsh='',null,@zd_nsh),zd_dsh=if(@zd_dsh='',null,@zd_dsh)";
			
			System.out.println("Start Load the file " + strName);
			rt = st.executeLargeUpdate(strSql);
			System.out.println("Successful Loaded " + rt.toString() + " Records");
			
		}
		
		st.close();
		conn.close();
			
		} catch (ClassNotFoundException  e)		{
			System.console().printf("Occur a error");
			e.printStackTrace();
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		}
		finally {
			
			if(st !=null) st.close();
			if(conn != null) conn.close();
		}

		
	}

}

这里主要说明下主要使用的LOAD DATA的参数 ：

  • fields terminated by ： 设置数据文件中列的分割符，csv文件为逗号；
  • lines terminated by ：设置数据文件中行的分割符，csv文件在Windows下为‘\r\n’，这点要注意，我开始使用'\n'，造成最后一列如果有空值无法导入；
  • ignore 1 lines：忽略标题行；
  • (jcrq,jcsj,jcxm,@zd_ds,@zd_tt,……）：设置插入的列名，如果列存在空值或其他需要特殊处理的值，请将列名用变量方式表示，即@+列名；
  • set zd_ds=if(@zd_ds='',null,@zd_ds),……：利用用户变量（@开头）对数据文件的数据进行预处理，特别是在文件中存在空值或其他特殊值时非常有用，本例中是判断数据文件的数据是否是空串，如是则插入NULL，否则插入实际的值。

导入数据的表名称为bj_2016,其结构如下：

mysql> desc bj_2016;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| jcrq    | varchar(20) | YES  |     | NULL    |       |
| jcsj    | varchar(2)  | YES  |     | NULL    |       |
| jcxm    | varchar(20) | YES  |     | NULL    |       |
| zd_ds   | int         | YES  |     | NULL    |       |
| zd_tt   | int         | YES  |     | NULL    |       |
| zd_gy   | int         | YES  |     | NULL    |       |
| zd_wsxg | int         | YES  |     | NULL    |       |
| zd_atzx | int         | YES  |     | NULL    |       |
| zd_nzg  | int         | YES  |     | NULL    |       |
| zd_wl   | int         | YES  |     | NULL    |       |
| zd_bbxq | int         | YES  |     | NULL    |       |
| zd_zwy  | int         | YES  |     | NULL    |       |
| zd_fthy | int         | YES  |     | NULL    |       |
| zd_yg   | int         | YES  |     | NULL    |       |
| zd_gc   | int         | YES  |     | NULL    |       |
| zd_fs   | int         | YES  |     | NULL    |       |
| zd_dx   | int         | YES  |     | NULL    |       |
| zd_yz   | int         | YES  |     | NULL    |       |
| zd_tz   | int         | YES  |     | NULL    |       |
| zd_sy   | int         | YES  |     | NULL    |       |
| zd_cp   | int         | YES  |     | NULL    |       |
| zd_mtg  | int         | YES  |     | NULL    |       |
| zd_pg   | int         | YES  |     | NULL    |       |
| zd_hr   | int         | YES  |     | NULL    |       |
| zd_my   | int         | YES  |     | NULL    |       |
| zd_yq   | int         | YES  |     | NULL    |       |
| zd_dl   | int         | YES  |     | NULL    |       |
| zd_bdl  | int         | YES  |     | NULL    |       |
| zd_mysk | int         | YES  |     | NULL    |       |
| zd_dgc  | int         | YES  |     | NULL    |       |
| zd_yld  | int         | YES  |     | NULL    |       |
| zd_yf   | int         | YES  |     | NULL    |       |
| zd_llh  | int         | YES  |     | NULL    |       |
| zd_qm   | int         | YES  |     | NULL    |       |
| zd_ydmn | int         | YES  |     | NULL    |       |
| zd_xzmb | int         | YES  |     | NULL    |       |
| zd_nsh  | int         | YES  |     | NULL    |       |
| zd_dsh  | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

       其中前三列为监测日期、监测时间、监测项目，后35列为具体的站点，执行完成后，几百个文件在一分钟内即可完成导入，简单快捷。

       由于数据是以监测站点为列，监测项目为行，不能满足本次统计分析中不同站点间的分布、对比、发展趋势等分析需要，需要进行行列转换，将监测项目变为列，监测站点为行，并且将监测数据按天进行均值计算。

       行列转换，可以利用聚集函数（如Max、Min、Sum、Avg等）及case语句、if函数来实现，具体的示例如下：

本例子中，直接将数据插到表中（bj_2016_by_item）。
1、使用case语句实现
insert into bj_2016_by_item (jcrq,jczd,pm2_5,pm10,so2,no2,o3,co,aqi) 
 select jcrq,'东四',
 avg(case jcxm when 'PM2.5' then zd_ds else null end) as 'pm2.5',
 avg(case jcxm when 'PM10' then zd_ds else null end) as 'pm10',
 avg(case jcxm when 'SO2' then zd_ds else null end) as 'so2',
 avg(case jcxm when 'NO2' then zd_ds else null end) as 'no2',
 avg(case jcxm when 'O3' then zd_ds else null end) as 'o3',
 avg(case jcxm when 'CO' then zd_ds else null end) as 'co',
 avg(case jcxm when 'AQI' then zd_ds else null end) as 'aqi' 
 from bj_2016 
 group by jcrq ;

2、使用if函数实现
insert into bj_2016_by_item (jcrq,jczd,pm2_5,pm10,so2,no2,o3,co,aqi)  
select jcrq,'东四',
avg(if(jcxm='PM2.5',zd_ds,null)) as 'pm2.5',
avg(if(jcxm='PM10',zd_ds,null)) as 'pm10',
avg(if(jcxm='SO2',zd_ds,null)) as 'so2',
avg(if(jcxm='NO2',zd_ds,null)) as 'no2',
avg(if(jcxm='O3',zd_ds,null)) as 'o3',
avg(if(jcxm='CO',zd_ds,null)) as 'co',
avg(if(jcxm='AQI',zd_ds,null)) as 'aqi' 
from bj_2016 
group by jcrq;

上述例子只是转换了一个监测站点，可以针对每个站点写一个这样的语句，然后用UNION ALL将各个语句连接起来即可，最终效果如下：

mysql> select * from bj_2016_by_item limit 10;
+----------+------+-------+------+-----+-----+----+----+-----+
| jcrq     | jczd | pm2_5 | pm10 | so2 | no2 | o3 | co | aqi |
+----------+------+-------+------+-----+-----+----+----+-----+
| 20160101 | 东四 |   179 |  207 |  35 | 101 |  4 |  3 | 167 |
| 20160102 | 东四 |   278 |  349 |  41 | 118 |  5 |  4 | 277 |
| 20160103 | 东四 |   246 |  530 |  26 |  70 | 11 |  3 | 368 |
| 20160104 | 东四 |    46 |   13 |   5 |  30 | 46 |  1 | 175 |
| 20160105 | 东四 |    36 |   93 |   9 |  39 | 29 |  1 |  35 |
| 20160106 | 东四 |    26 |   38 |   8 |  38 | 31 |  1 |  65 |
| 20160107 | 东四 |    11 |   18 |   3 |  24 | 47 |  0 |  26 |
| 20160108 | 东四 |    35 |   51 |   8 |  37 | 34 |  1 |  25 |
| 20160109 | 东四 |    81 |  117 |  18 |  66 |  9 |  2 |  86 |
| 20160110 | 东四 |    49 |   63 |  19 |  55 | 16 |  2 |  95 |
+----------+------+-------+------+-----+-----+----+----+-----+
10 rows in set (0.09 sec)

至此，数据导入和转换完成，可以开始统计分析的正事儿了。

starshine
关注
  • 2
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
利用java语言将csv格式数据导入mysql数据库
bin的主栏
01-16 4969
利用java语言将csv格式数据导入mysql数据库 1.     首先要下载Connector/J，及将该驱动包加载到project中，见http://www.cnblogs.com/taoweiji/archive/2012/12/11/2812295.html 2.     提供数据库URL 连接URL定义了连接数据库时的协议、子协议、数据源标识。    l  书写形式：协议：子协
Java中如何把csv加载进数据库
Raqsoft
09-17 1981
Java应用中，有时需要把CSV中的数据加载到数据库。数据库一般有自带的工具，但大都有专门的界面，很难成为可被Java调用的例程。 借助集算器可以很方便地完成这件事。 我们以“销售数据的csv文件”加载到Mysql数据库的销售表（sales）为例。 销售数据的csv文件（sales.csv），部分数据如下： ORDERID CLIENT SELLERID AMOUNT ORDERDATE 1 LIHD 11 9893 2020/12/7 2
java将.csv文件导入mysql中，结合springmvc + mybatis
一个农民工的博客
08-01 3604
最近公司有这个需求，将.csv文件导入数据库中，之前是导入mysql，后面由于客户需要用oracle，又将数据导入oracle中（苦笑），所以讲.csv文件导入mysql和oracle都用了。这里主要是说说如何将.csv文件导入mysql中，后面在说说导入oracle中。 这里是结合的springmvc + mybatis的方式进行操作的，下面说说我的步骤： 第一步：建立数据库和对应的表，这里...
Java导出千万级大数据到CSV文件
qq_41577650的博客
05-11 651
在实际应用中，我们经常需要从数据库中导出大量数据到CSV文件。如果数据量很大，一次性加载所有数据可能会导致内存溢出或者性能问题。为了解决这个问题，我们可以使用流式查询的方式逐行读取数据库，并将数据写入CSV文件，从而减少内存占用并提高性能。本文将介绍如何使用Java实现这一功能，并给出详细的代码示例。
java代码将csv格式的文件导入数据库
qq_39845198的博客
11-14 4648
java代码将csv格式的文件导入数据库
java使用javacsv读取csv文件 导入Mysql数据库
06-08
NULL 博文链接：https://qq-24665727.iteye.com/blog/2339850
java 读取csv文件数据并保存到数据
nann-viiv 的博客
04-13 3377
项目需求，读取csv文件并将日志数据保存到数据库中： 首先，建表然后对应的xml、dao、实体类、service和实现类。 上实现代码： package cn.util.controller; import cn.util.entity.TlVacuumFurnaceLog; import cn.util.service.vfService; import cn.util.service.vfServiceImpl; import org.springframework.beans.factory.Bea
千万行csv大文件快速导入mysql
qq_43644046的博客
02-08 5896
最近尝试csv文件导入，因为之前太菜就用普通图形窗口导入，但是速度很慢，于是做了总结：目前我这里一共两种方法：一.图形化图形化界面，找到电脑左下角的开始，找到wokbench 这个可视化软件，没有的是当初安装mysql没有下载他的可视化捆绑程序，自己可以百度解决。 运行程序后，进入root 在下图空白处右键，Create schema建立新的数据库 然后找到刚刚建立的数据库左侧角标点击展开，在Table上鼠标右键，选择Table Data Import Wizard. 选择csv文件路径,然后点击next
java实现批量导入.csv文件到mysql数据库
08-25
java实现批量导入.csv文件到mysql数据库 概述 本文主要介绍了使用Java实现批量导入.csv文件到MySQL数据库的方法。该方法可以快速地将.csv文件中的数据批量导入到数据库中，具有很高的实用价值。 Java实现批量导入...
SQL导入MySQL数据库的源代码
最新发布
06-16
总结来说，SQL导入MySQL数据库涉及到数据预处理、创建数据库和表结构、使用`LOAD DATA INFILE`命令、错误处理、性能优化以及备份恢复等多个环节。了解并熟练掌握这些步骤对于有效地管理和操作数据库至关重要。
从文本批量读取数据到MySQL数据库并把名字也添加进去
06-06
通过以上步骤，你就可以将文本文件中的数据批量导入到MySQL数据库，并且在数据库中添加一个新列来记录每个数据行对应的文件名。这种方法适用于大规模数据的导入，提高了数据处理的效率。在实际应用中，根据数据格式...
csv导入数据库 java源代码
10-08
扫描csv文件的数据到数据库中 java源代码
JAVA读取CSV到数据库表中
10-15
本人以JAVA来实现以支付宝的账单表为例,实现JAVA读取CSV..csv是一种文件格式（如.txt、.doc等），也可理解.csv文件就是一种特殊格式的纯文本文件。即是一组字符序列，字符之间已英文字符的逗号或制表符（Tab）分隔。
area_ip.rar_MySQL数据库_Java_
08-11
具体到文件"area_ip.sql"，这通常是一个SQL脚本文件，用于导入到MySQL数据库中。打开这个文件，我们可以看到一系列CREATE TABLE语句，用于创建数据库表结构，可能包括省份、城市和对应的IP段。数据可能以INSERT INTO...
Java读取CSV文件内容到数据库
that summer
11-07 3575
CSV文件主要是以逗号分割数据的，代码如下： File csv = new File("E:\\GeoIP2-City-Locations-en.csv"); // CSV文件路径 BufferedReader br = null; br = new BufferedReader(new FileReader(csv)); String line = ""; Strin...
Java导入csv文件到mysql
zlj的博客
10-09 610
package CSV; import java.io.BufferedReader; import java.io.File; import java.io.FileReader; import java.io.IOException; import java.nio.charset.Charset; import java.sql.Connection; import java...
将大csv文件导入mysql数据库
weixin_44166997的博客
07-05 6699
我这边是利用pandas来将csv文件导入到MySQL数据库的 首先说一下实现逻辑，就是将csv读取到dataframe中，然后把按行读取数据，将每行的数据添加到列表中，在利用pymysql这个包将数据添加到数据库。 代码如下： import pandas as pd import pymysql #读取数据 test = pd.read_csv(r'Antai_AE_round1_item_a...
如何批量导入csv文件到db2数据库里
04-28
可以使用以下步骤批量导入CSV文件到DB2数据库： 1. 创建一个表来存储CSV文件中的数据，表的列数和数据类型应该与CSV文件的列数和数据类型相匹配。 2. 在数据库中创建一个LOAD文件，该文件将告诉DB2如何读取CSV文件。 3. 使用db2 load命令来将CSV文件中的数据加载到DB2表中。命令的语法如下： db2 load from [CSV文件路径] of del modified by [LOAD文件路径] replace into [表名] 4. 执行该命令后，DB2将读取CSV文件并将其加载到表中。 注意：在执行此操作之前，请确保CSV文件和LOAD文件的格式是正确的，并且所有必需的列都已包含在CSV文件中。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值