import from utf-8 csv to SQL Server 2025 for Windows

目次

bcp import from utf-8 csv to SQL Server 2022 for Linux - end0tknr's kipple - web写経開発

以前のentryに対しての SQL Server 2025 express edition for Windows版

step1 - export from SAP ASE for Linux

SAP ASE ≒ SQL Server で、sqlも bcpコマンドも、ほぼ同様に利用できます。

SAP Help Portal | SAP Online Help

bcpコマンドでtableの全recordをexportする場合

$ bcp xerial..kousei  out kousei.csv -Uxsuser -Pxerial -Sxerial -c -t,

bcpコマンドでtableの特定のrecordをexportする場合

$ bcp "SELECT * FROM xerial..kousei WHERE body_mark='A' and ms in('F','M')" \
    queryout kousei.csv -Uxsuser -Pxerial -Sxerial -c -t,

step2 - import by strawberry perl

SQL Server 2025 express にも bcpコマンドは付属しますが、 なぜか、エラーになった為、今回はperl scriptでimport しています。

#!c:/Strawberry/perl/bin/perl
use utf8;
use strict;
use warnings;
use DBI;
use Data::Dumper;
use Encode;
use IO::Uncompress::Unzip qw/$UnzipError/;
use Text::CSV;

my $BCP_DIR = "c:/Users/????/dev/BCP";
my $BULK_INSERT_SIZE = 20;

main();

sub main {
    my $dbh = connect_db();

    my $tbl_names = [
        "plan_attr",
        "arrange_kmatrix",
        "arrange_oya",
        "arrange_rlkousei",
        "arrange_skousei"
        ];
    for my $tbl_name ( @$tbl_names ){
        print "TABLE : $tbl_name\n";
        my $zip_path = "$BCP_DIR/$tbl_name.zip";
        my $org_rows = load_zip_csv( $tbl_name, $zip_path );

        import_csv($dbh, $tbl_name, $org_rows);
    }

    $dbh->disconnect;
}

sub import_csv {
    my ($dbh, $tbl_name, $org_rows) = @_;

    my $col_size = scalar(@{$org_rows->[0]});
    my $sql_head = "INSERT INTO $tbl_name VALUES";
    my $values   = "(". join(",",("?")x scalar($col_size)) .")";
    my $sql = $sql_head . $values;
    my $sth = $dbh->prepare($sql);

    my $i = 0;
    my @buffer;

    for my $csv_cols ( @$org_rows ){
        $i++;
        if( $i % 5000 == 0 ){
            print "import_csv() for $tbl_name $i / ",
                scalar(@$org_rows) , "\n";
        }

        if ( scalar(@$csv_cols) < $col_size ){
            next;
        }

        push @buffer, $csv_cols;

        if (@buffer >= $BULK_INSERT_SIZE) {
            bulk_insert($sth, \@buffer);
            $dbh->commit;
            @buffer = ();
        }
    }

    bulk_insert($sth, \@buffer) if @buffer;
    $dbh->commit;
    print "DONE import_csv() for $tbl_name\n";
    return $i;
}

sub bulk_insert {
    my ($sth, $rows) = @_;
    return unless @$rows;

    my $col_count = @{ $rows->[0] };
    my @bind;

    for my $col (0 .. $col_count - 1) {
        push @bind, [ map { $_->[$col] } @$rows ];
    }
    $sth->execute_array({}, @bind);
}

sub load_zip_csv {
    my ($tbl_name, $zip_path) = @_;
    my $unzip = IO::Uncompress::Unzip->new( $zip_path )
        or die "$zip_path $UnzipError $!";
    my $csv = Text::CSV->new({binary=>1, auto_diag=>1, decode_utf8 =>1});

    my $ret_datas = [];
    while (1) {
        my $csv_name = $unzip->getHeaderInfo->{Name};
        #next unless $csv_name =~ /\.csv$/io;

        my $i = 0;
        while(my $row = $csv->getline($unzip)) {
            $i++;
            if( $i % 5000 == 0 ){
                print "load_zip_csv() for $tbl_name $i\n";
            }

            if( scalar(@$row) < 1 ){
                last;
            }

            if( $tbl_name eq "plan_attr" ) {
                shift( @$row );
                pop( @$row );
            }

            my $cols = [];
            for my $col ( @$row ){
                push( @$cols, trim($col) );
            }
            push(@$ret_datas, $cols);
        }

        my $status = $unzip->nextStream();
        last unless $status;
    }
    return $ret_datas;
}

sub connect_db {
    my $dsn = 'dbi:ODBC:nserial';
    my $dbh = DBI->connect(
        $dsn,
        undef,   # Windows 認証なので不要
        undef, {RaiseError=>1, AutoCommit=>0}) or die $DBI::errstr;

    my $sth = $dbh->prepare("use nserial");
    unless( $sth->execute() ){
        print STDERR $sth->errstr, "\n";
        return undef;
    }
    return $dbh;
}

sub trim {
    my ($val) = @_;
    $val =~ s/^[\s　]*(.*?)[\s　]*$/$1/go;
    if( length($val)==0){
        $val = "";
    }
    return $val;
}
__END__
1;