如何把excel数据导入数据库

这里介绍2种把excel数据导入oracle数据库的方法。

 

1. 在excel中生成sql语句。

 

1)在数据列的右侧,第一行的任何位置输入=”insert into table(xx,yyy..) values(‘”&A2&”‘, ‘”&B2&”‘….);”

注意:

  •  整个sql语句用双引号套着。
  • values值中使用 “&A2&”  来引用A列的第二行数据,再外面的单引号是因为values中的值(varchar型)需要单引号。

2)写完一行后往下拖,自动生成其他行的sql。

3)把这些sql copy到pl/sql dev工具中执行。

 

这种方法适合数据量不太大,且只含有varchar(或者说转入数据库中都为varchar类型字段)这种简单的数据类型的数据。对于含有日期类型的要注意,如果excel格式中有日期类型的,使用这种方法引用出来的值是个浮点数。需要首先把日期格式转换成文本格式。再写sql语句,它才能引用正确的日期格式的值。insert into user_test(seq, msisdn,user_name,dt,comment1,comment2) values(‘1′,’13597093898′,’张三42’,to_date(‘2013/2/8 20:00:01′,’yyyy/mm/dd HH24:mi:ss’),’yyyy’,’yyyyy’);

 

 

但存在以下情况不建议用这种方法了。

1. 对于数据量很大,几十万,上百万的数据。

2. 如果数据库表存在由序列生成的列。

3. 存在日期格式列的excel。——我没有找到很方便的直接把日期格式转为文本格式的方法(如直接转换成文本,它会显示成浮点),因此存在日期格式的excel列不建议采用这种方法。

 

当然第二点,还是可以通过其他方法解决的,就是写触发器。

在写insert语句插入的列不包含序列的列,写触发器,当表进行insert之前生成序列号插入。

 

假设数据库表如下

 

create table user_test

(

   seq number(10) primary key,

   msisdn varchar2(11) not null,

   user_name varchar2(50) not null,

   dt     date,

   comment1 varchar2(50),

   comment2 varchar2(50)

);

 

create sequence user_test_seq

INCREMENT BY 1 -- 每次加几个

START WITH 1 -- 从1开始计数

 

insert变成这样写:

insert into user_test(msisdn,user_name,dt,comment1,comment2) values('13597093898','张三42',to_date('2013/2/8 20:00:01','yyyy/mm/dd HH24:mi:ss'),'yyyy','yyyyy'

 

 

触发器:

 

CREATE OR REPLACE TRIGGER my_trigger

  BEFORE INSERT

  ON user_test  FOR EACH ROW

WHEN (new.seq is null)

BEGIN

  SELECT user_test_seq.nextval INTO :new.seq  FROM DUAL;

END my_trigger;

 

 

其中:new.seq是指新列中的seq字段。

 

 

为更好的解决这些问题, 下面介绍第二种方法。

 

2. 写loader

 

1) 把excel中的数据另存为.csv文件,字段逗号分隔。假设保存为gov.csv

2 )写ctl文件:

 

 gov.ctl

LOAD DATA

INFILE 'gov.csv'

APPEND

INTO TABLE  user_test

FIELDS TERMINATED BY ','

trailing nullcols

//下面列先后顺序同文件中数据字段的顺序。

(

seq "user_test_seq.nextval",

MSISDN,

user_name,

DT date "yyyy/mm/dd HH24:mi",

comment1 ,

comment2

)

 

 

3) 写par文件, 假设名为test.par,其内容为:

userid=用户/密码

control=./gov.ctl

log=./t.log

bad=./t.bad

skip=1            //跳过第一行标题

rows=10000   //每n行commit一次

 

 

4)sqlldr parfile=test.par

 

在我的服务器上,导入百多万的数据应该是一分钟之内的事。

发表在 技术, 未分类 | 留下评论

vmware给linux虚拟机增加磁盘空间(基于lvm分区)

1. 进入 vm  setting ,对要增加空间的磁盘 expand,指定变更空间大小(如由20G 调整到 50G),vmware需要处理一段时间。
 
2. 开linux虚拟机,fdisk -l 会发现磁盘空间大了,但文件系统的分区大小还是原来size。
(以下对需要调整空间的磁盘分区称为“原分区”)
 
3. 备份原分区资料 cp 或 mv 到其他硬盘分区。
 
4. 通过fdisk删除原分区,重建新分区,把50G大小分给此分区,也即是这个硬盘只有一个分区。当然也可以有多个分区,但本次只想对一个分区增加空间。
  最后把此分区标记为8e 类型(LVM) 
 
5. pvcreate /dev/sdb1, 以分区 /dev/sdb1 创建物理卷。
 
6. vgcreate xjf_data /dev/sdb1  在物理卷中创建一个逻辑卷组。名字自己指定(此处命名为 xjf_data)  一般系统在/dev/ 中创建。
 
7. lvcreate -L30G -n lv_xjf_data xjf_data   在逻辑卷组中创建一个逻辑卷( lv_xjf_data),并指定大小。  一般在 /dev/xjf_data/  中。
 
8. mkreiserfs /dev/xjf_data/lv_xjf_data      在这逻辑卷中创建reiserfs文件系统。
 
9. mount /dev/xjf_data/lv_xjf_data /data    挂载到现有目录/data 中。
 
10. 想开机直接挂载,需要修改/dev/fstab
 
/dev/mapper/xjf_data-lv_xjf_data               /data                  reiserfs          defaults   1 2
 
第一个字段名称,在df -h 中可以看到
 
/dev/mapper/xjf_data-lv_xjf_data   32G   15G   18G   46% /data
 
发表在 linux, 技术, 未分类 | 标签为 | 留下评论

带统一认证的轻量级网关系统

        

      由于公司现有系统中,注册、认证鉴权都耦合到网关服务端中,难以融入其他系统中,因此开发一套轻量级的,带认证服务的系统,以便嵌入到其他系统中。

 

本系统实现注册、鉴权、心跳、push消息等功能。  数据库由redis充当。流程方案大概如下,许多业务细节不表。

 

 

 

 

有以下几个模块

A 统一认证服务器   B 终端 (安卓/ios 设备)  C 网关服务器

 

注册流程

1. 终端上传用户名和(用户名+密码)MD5给A.

2. A检查用户名唯一性,已注册返回失败,未注册则保存用户名和(用户名+密码)md5,生成唯一用户id,并返回id和成功状态给B

  

登录流程

1.  B登录C的时候,C发现报文未携带ticket,返回信息引导其到A进行认证;

     如果已携带ticket,把用户id和ticket发送到A进行鉴权,如果不通过则返回错误给B,要求其重新输入密码,如果通过则进入4.

2. B 发送用户名和用户名和密码的md5值给A。

3. A检查用户名和md5值,如果和数据库中的md5值匹配则通过,生成唯一的ticket,记录到数据库中并返回ticket和成功状态给C;否则返回认证失败状态。

4. C收到A的认证结果,如认证成功,记录B的登录session,返回带ticket的ack,否则返回错误给B。

5.B接收到ack报文,检查状态,如果成功则保存ticket。

 

心跳

1. 终端发送心跳报文,用于维持连接。

2. 网关接收心跳信息,返回心跳ack报文。

 

push

1. 网关检测redis中消息队列,获取消息,根据消息中的用户id,检查该用户是否在线,如果在线则直接发送,否则放回队列尾。

 

 

发表在 技术, 未分类 | 留下评论

ORA-02292主键约束问题

 

  通常在删除某个表A的时候,会出现这个错误。原因是另一个表B的某个字段引用了A表的某个字段作为约束(这个的另一个说法是外键)。

假如引用的字段叫field,当B.field = A.field , 而你想删掉A.field,这时就爆出这个错误。解决办法是先把B.field (B.field = A.field的部分) 干掉,再干掉你想删除的A.field

 

问题是,如何知道哪个表引用了A表的field字段或其他字段?报错信息里并没有给出,只给出了约束的名称。这时可以查all_constraints。

 

select  table_name from all_constraints where constraint_name = ‘约束的名称’ (一般如 FK_xxx)

查看选出的table_name 就知道索引了A的哪个字段。

 

 

 

发表在 技术, 未分类 | 留下评论

curl遇到longjmp causes uninitialized stack frame的处理办法

摘自 http://stackoverflow.com/questions/9191668/error-longjmp-causes-uninitialized-stack-frame

 

I ran into the same issue; as noted above, it is a curl bug. I thought I would put an answer up here to pull together all of the available information on the problem.

From the Red Hat bug report:

libcurl built without an asynchronous resolver library uses alarm() to time out DNS lookups. When a timeout occurs, this causes libcurl to jump from the signal handler back into the library with a sigsetjmp, which effectively causes libcurl to continue running within the signal handler. This is non-portable and could cause problems on some platforms. A discussion on the problem is available at http://curl.haxx.se/mail/lib-2008-09/0197.html

The “problems on some platforms” apparently refers to crashes on modern Linux systems at least. Some deeper technical details are at the link from the quote above:

There’s a problem with the way libcurl currently handles the SIGALRM signal. It installs a handler for SIGALRM to force a synchronous DNS resolve to time out after a specified time, which is the only way to abort such a resolve in some cases. Just before the the DNS resolve takes place it initializes a longjmp pointer so when the signal comes in the signal handler just does a siglongjmp, control continues from that saved location and the function returns an error code.

The problem is that all the following control flow executes effectively inside the signal handler. Not only is there a risk that libcurl could call an async handler unsafe function (see signal(7)) during this time, but it could call a user callback function that could call absolutely anything. In fact, siglongjmp() itself is not on the POSIX list of async-safe functions, and that’s all the libcurl signal handler calls!

There are a couple ways to solve this problem, depending upon whether you built libcurl or if you’re stuck with one that was provided by your distribution or system admin:

  • If you can’t rebuild libcurl, then you can call curl_easy_setopt(curl, CURLOPT_NOSIGNAL, 1) on all curl handles that you use. The documentation for CURLOPT_NOSIGNAL notes:

    Pass a long. If it is 1, libcurl will not use any functions that install signal handlers or any functions that cause signals to be sent to the process. This option is mainly here to allow multi-threaded unix applications to still set/use all timeout options etc, without risking getting signals. (Added in 7.10)

    If this option is set and libcurl has been built with the standard name resolver, timeouts will not occur while the name resolve takes place. Consider building libcurl with c-ares support to enable asynchronous DNS lookups, which enables nice timeouts for name resolves without signals.

    DNS timeouts are obviously desirable to have in most cases, so this isn’t a perfect fix. If you have the ability to rebuild libcurl on your system, then you can…

  • There is an asynchronous DNS resolver library called c-ares that curl is capable of using for name resolution. Using this library is the preferred solution to the problem (and I would imagine most Linux packagers have figured this out by now). To enable c-ares support, first build and install the library, then pass the --enable-ares flag to curl’s configure script before you build. Full instructions are here.

发表在 linux, 技术, 未分类 | 标签为 | 留下评论

ACE6.10的安装

在网上找一些ace的安装文章,居然还有人用configure配置,但ACE6.1的版本已经没有configure了。这里记录下6.1版本的安装方式。

 

1. 从 ACE 官网(http://download.dre.vanderbilt.edu/)下载 ACE+TAO+CIAO-6.1.2.tar.bz2,放在/home/yourname; 用

   tar xvjf ACE+TAO+CIAO-6.1.2.tar.bz2 解压得到 /home/yourname/ACE_wrappers

   su 到 root 账户下

 

2. 设置环境变量: ACE_ROOT=/home/yourname/ACE_wrappers; export ACE_ROOT

备注: 这里是临时的环境变量设置,如果想后面使用的时候每次都有这个,把设置写进 .bash_profile

 

3. 创建文件 $ACE_ROOT/ace/config.h 写入如下内容 #include “ace/config-linux.h”

   备注: 这里可以定义宏,改变 ACE 的某些特性

 

4. 创建文件 $ACE_ROOT/include/makeinclude/platform_macros.GNU 写入 include $(ACE_ROOT)/include/makeinclude/platform_linux.GNU

备注: 这里是 build configuration, include 前不需要 #

这步比较重要,否则make可能会出错.

 

5. 可以在 platform_macros.GNU 里写入 INSTALL_PREFIX = /home/yourname/ace_install_612/ 来制定输出目录

 

6. 设置环境变量 LD_LIBRARY_PATH=$ACE_ROOT/lib:$LD_LIBRARY_PATH; export LD_LIBRARY_PATH

10. make

备注: 这步时间比较长

 

在我的环境(fedora20 64bit),编译过程中出现ace SAXPrint compile error,解决办法是在config.h中增加一句

#define ACE_GCC_HAS_TEMPLATE_INSTANTIATION_VISIBILITY_ATTRS 1

 

 

7make install  【 在rootexport ACE_ROOT=/home/yourname/software/ACE_wrappers

备注: 这里是把 lib, include, share, 和 bin 输出到 INSTALL_PREFIX 目录下。

 

 

发表在 技术, 未分类 | 留下评论

G14手机无法获取当前位置天气的解决办法

 

      每当新rom安装后,总有一段时间内置天气服务无法获取当前位置,所以也无法显示当前位置的天气,只能手动添加城市天气。

这样太不爽了,原以为是rom的问题。后来发现是内置天气服务需与海外服务器连接,而大家都知道因为墙的存在访问外网不是那么畅通。

所以只要可以翻墙,或者链接一个能翻墙的wifi就可以马上获取当前地天气。

发表在 未分类 | 留下评论

redhat6.3企业版安装oracle11g过程

创建用户等前置工作完成后, 
 
export DISPLAY=:0.0
/home/oracle/database/runInstaller -jreLoc /usr/lib/jvm/java/jre
 
报错
No protocol specified
Exception in thread “main” java.lang.NoClassDefFoundError: Could not initialize class sun.awt.X11.XToolkit
 
google后,发现要在root下执行
xhost +127.0.01 后再到oracle用户下export DISPLAY=:0.0,然后再安装。
 
但发现xhost 出错
[root@m2m-46 database]# xhost +local:oracle
No protocol specified
xhost:  unable to open display “:0.0”
 
咨询同事得知这种情况要安装vncserver,但redhat的这个安装iso居然没有这个软件,
在网上查找到安装包VNC-5.0.3-Linux-x64-ANY.tar.gz(http://www.realvnc.com/download/get/1185/) ,解压后是一堆绿色软件,直接使用它自带的脚本安装到系统bin下。
 
在root下直接vncserver,说要添加key,于是又上网查找key(根据错误提示很容易查找)
添加key后vncserver成功。
 
继续xhost +127.0.01 成功
转oracle用户,
export LANG=C
/home/oracle/database/runInstaller
 
顺利进入安装界面。
 
参考:http://blog.sina.com.cn/s/blog_a32eff280100ykyq.html
发表在 未分类 | 留下评论

ld编译链接时默认搜索路径

我总以为LD_LIBRARY_PATH是ld的编译链接时的默认搜索路径,其实不是,只是程序运行时的搜索库路径。

于是有了以下的问题

明明在LD_LIBRARY_PATH配置了boost_thread lib的路径,ld却报找不到。

 

g++ thread.cpp -lboost_thread
/usr/bin/ld: cannot find -lboost_thread
collect2: 错误:ld 返回 1

 

 

对这编译链接,一定要用-L指定路径。

 

发表在 未分类 | 留下评论

vsftpd的安装及使用问题

安装了fedora有一年了,还没搞懂怎么使用ftp,真是惭愧。今天下定决心解决这个问题。

 

关于安装

从网上下载了vsftpd源码包,vsftpd-2.1.0.tar.gz

 

tar –zxf vsftpd-2.1.0.tar.gz 解压

安装:

make,

make insatall

 

cp vsftpd.conf /etc

 

配置文件

vsftpd.conf 的主要内容,有些项目屏蔽掉需要去掉屏蔽字符“#”。

以下红色部分我认为是很重要的,缺少了可能ftp有问题,紫色的是辅助功能。

anonymous_enable=NO

#

# Uncomment this to allow local users to log in.

local_enable=YES

#

# Uncomment this to enable any form of FTP write command.

write_enable=YES

#

# Default umask for local users is 077. You may wish to change this to 022,

# if your users expect that (022 is used by most other ftpd’s)

local_umask=022

#

# Uncomment this to allow the anonymous FTP user to upload files. This only

# has an effect if the above global write enable is activated. Also, you will

# obviously need to create a directory writable by the FTP user.

#anon_upload_enable=YES

#

# Uncomment this if you want the anonymous FTP user to be able to create

# new directories.

#anon_mkdir_write_enable=YES

#

# Activate directory messages – messages given to remote users when they

# go into a certain directory.

dirmessage_enable=YES

#

# Activate logging of uploads/downloads.

xferlog_enable=YES

#

# Make sure PORT transfer connections originate from port 20 (ftp-data).

“/etc/vsftpd.conf” [readonly] 119L, 4514C

#

# Make sure PORT transfer connections originate from port 20 (ftp-data).

connect_from_port_20=YES

#

# If you want, you can arrange for uploaded anonymous files to be owned by

# a different user. Note! Using “root” for uploaded files is not

# recommended!

#chown_uploads=YES

#chown_username=whoever

#

# You may override where the log file goes if you like. The default is shown

# below.

#xferlog_file=/var/log/vsftpd.log

#

# If you want, you can have your log file in standard ftpd xferlog format.

# Note that the default log file location is /var/log/xferlog in this case.

#xferlog_std_format=YES

#

# You may change the default value for timing out an idle session.

#idle_session_timeout=600

#

# You may change the default value for timing out a data connection.

#data_connection_timeout=120

#

# It is recommended that you define on your system a unique user which the

# ftp server can use as a totally isolated and unprivileged user.

#nopriv_user=ftpsecure

#

# Enable this and the server will recognise asynchronous ABOR requests. Not

# recommended for security (the code is non-trivial). Not enabling it,

# however, may confuse older FTP clients.

#async_abor_enable=YES

#

# By default the server will pretend to allow ASCII mode but in fact ignore

# the request. Turn on the below options to have the server actually do ASCII

# mangling on files when in ASCII mode.

# Beware that on some FTP servers, ASCII support allows a denial of service

# attack (DoS) via the command “SIZE /big/file” in ASCII mode. vsftpd

# predicted this attack and has always been safe, reporting the size of the

# raw file.

 

# predicted this attack and has always been safe, reporting the size of the

# raw file.

# ASCII mangling is a horrible feature of the protocol.

#ascii_upload_enable=YES

#ascii_download_enable=YES

#

# You may fully customise the login banner string:

#ftpd_banner=Welcome to blah FTP service.

#

# You may specify a file of disallowed anonymous e-mail addresses. Apparently

# useful for combatting certain DoS attacks.

#deny_email_enable=YES

# (default follows)

#banned_email_file=/etc/vsftpd.banned_emails

#

# You may specify an explicit list of local users to chroot() to their home

# directory. If chroot_local_user is YES, then this list becomes a list of

# users to NOT chroot().

#chroot_list_enable=YES

# (default follows)

#chroot_list_file=/etc/vsftpd.chroot_list

#

# You may activate the “-R” option to the builtin ls. This is disabled by

# default to avoid remote users being able to cause excessive I/O on large

# sites. However, some broken FTP clients such as “ncftp” and “mirror” assume

# the presence of the “-R” option, so there is a strong case for enabling it.

#ls_recurse_enable=YES

#

# When “listen” directive is enabled, vsftpd runs in standalone mode and

# listens on IPv4 sockets. This directive cannot be used in conjunction

# with the listen_ipv6 directive.

#这是本人增加的

listen=YES

listen_port=21

#

# This directive enables listening on IPv6 sockets. To listen on IPv4 and IPv6

# sockets, you must run two copies of vsftpd whith two configuration files.

# Make sure, that one of the listen options is commented !!

#listen_ipv6=YES

 

#这是本人增加的

userlist_enable=YES #这个是支持用户列表屏蔽,凡是在列表中的用户都禁止ftp

pam_service_name=vsftpd  #这个相当关键,后面讲。

userlist_file=/etc/vsftpd.user_list  #用户列表的绝对路径

check_shell=NO    #这个说是不检查用户shell

 

 

 

启动

使用root帐号登录

[root@localhost etc]# which vsftpd

/usr/local/sbin/vsftpd

启动:

/usr/local/sbin/vsftpd &

 

 

本地登录

[root@localhost etc]# ftp 127.0.0.1

Connected to 127.0.0.1.

220 (vsFTPd 2.1.0)

530 Please login with USER and PASS.

530 Please login with USER and PASS.

KERBEROS_V4 rejected as an authentication type

Name (127.0.0.1:root): xjf

331 Please specify the password.

Password:

530 Login incorrect.

Login failed.

 

本地用户不能登录的解决方法

密码是对的,但提示登录不了。于是根据“530 Login incorrect google了一大堆问题以及所谓的解决方法,结果都没有用。最后,在vsftpd的安装目录中的FAQ中找到了答案。

 

其中有一段是关于本地用户不能登录的一些解决办法,红色这段正式我遇到的问题的解决之道。
Q) Help! Local users cannot log in.

A) There are various possible problems.

A1) By default, vsftpd disables any logins other than anonymous logins. Put

local_enable=YES in your /etc/vsftpd.conf to allow local users to log in.

A2) vsftpd tries to link with PAM. (Run “ldd vsftpd” and look for libpam to

find out whether this has happened or not). If vsftpd links with PAM, then

you will need to have a PAM file installed for the vsftpd service. There is

a sample one for RedHat systems included in the “RedHat” directory – put it

under /etc/pam.d

A3) If vsftpd didn’t link with PAM, then there are various possible issues. Is

the user’s shell in /etc/shells? If you have shadowed passwords, does your

system have a “shadow.h” file in the include path?

A4) If you are not using PAM, then vsftpd will do its own check for a valid

user shell in /etc/shells. You may need to disable this if you use an invalid

shell to disable logins other than FTP logins. Put check_shell=NO in your

/etc/vsftpd.conf.

 

# ldd /usr/local/sbin/vsftpd

        linux-gate.so.1 =>  (0x001e3000)

        libpam.so.0 => /lib/libpam.so.0 (0x4e8d3000)

        libdl.so.2 => /lib/libdl.so.2 (0x4efa3000)

        libnsl.so.1 => /lib/libnsl.so.1 (0x4e4ea000)

        libresolv.so.2 => /lib/libresolv.so.2 (0x4e507000)

        libutil.so.1 => /lib/libutil.so.1 (0x4e921000)

        libcap.so.1 => /lib/libcap.so.1 (0x4e650000)

        libc.so.6 => /lib/libc.so.6 (0x4ee3b000)

        libaudit.so.0 => /lib/libaudit.so.0 (0x4e8be000)

        /lib/ld-linux.so.2 (0x4e46c000)

 

按提示ldd看,果然看到使用了libpam.so.就是说vsftpd开启后需要链接对应的PAM文件,而这个PAM文件应该安装在/etc/pam.d这个目录里。但目前在/etc/pam.d里没有vsftpd这个文件。这个文件在安装目录的RedHat目录里。

[root@localhost RedHat]# ls

README.spec  vsftpd.log  vsftpd.pam

 

cp vsftpd.pam /etc/pam.d/vsftpd

配置文件中的pam_service_name=vsftpd  正是指示所安装的pam文件。

 

重新启动vsftpd

 

[root@localhost etc]# ftp 127.0.0.1

Connected to 127.0.0.1.

220 (vsFTPd 2.1.0)

530 Please login with USER and PASS.

530 Please login with USER and PASS.

KERBEROS_V4 rejected as an authentication type

Name (127.0.0.1:root): xjf

331 Please specify the password.

Password:

230 Login successful.

Remote system type is UNIX.

Using binary mode to transfer files.

 

大功告成!!

看来有时候解决不问题不一定要google,看看faq帮助也很大。

 

发表在 linux, 技术, 未分类 | 标签为 | 留下评论