How to automate a large number of txt files to be imported from Perl to a SQL Server using DBI? -


i have attempted transfer 1 txt file sql server perl, need create db in sql combines hundreds of txt files. there automated way of doing save manually transferring txt files? insert attempt of coding 1 txt file. believe need use bulk insert achieve unsure code use. advice on problem appreciated, , if there anymore code wish see or specific thing forgot mention please comment , edit. thank you.

#!/usr/bin/perl -w use strict; use dbi; use carp;      # connect data source , handle connection. $dbh = dbi->connect('dbi:odbc:regressiondbs') ||     confess "connection failed: $dbi::errstr";      # query generates result set 1 record in it. $sql = "select 1 test_col"; $tablename = "regressiontable";  open(fh, "newregression.txt") || die "cant open file"; $sqlh = $dbh->prepare("insert regressiontable (ticker, tradedate, highprice, lowprice, tradeprice, totalvolume, totalvalue) values (?,?,?,?,?,?,?);") || confess $dbi::errstr; while (my $line = <fh> ) { $line =~/^(.*)(..)(..)$/; ($curticker, $wtradedate, $whighprice, $wlowprice, $wtradeprice, $wtotalvolume, $wtotalvalue);     $curticker=$1     #$wtradedate=     $whighprice=$2;     $wlowprice=$1;     #$wtradeprice=$1;     #$wtotalvolume=$1;     #$wtotalvalue=$4;  $sqlh->execute($curticker, $wtradedate, $whighprice, $wlowprice, $wtradeprice, $wtotalvolume, $wtotalvalue) || confess $dbi::errstr;  }  $sqlh->finish(); $dbh->disconnect();

ok, run

insert regressiontable (   ticker, tradedate, highprice, lowprice, tradeprice, totalvolume, totalvalue )  values (?, ?, ?, ?, ?, ?, ?)

for every line in newregression.txt, , want multiple files. why not wrap code above in subroutine, insert_file(), call per file in loop...

my @files = qw(red.txt green.txt blue.txt); $file (@files) {   insert_file($file); }

or if target table varies per file, define hash:

my %files = (   'red.txt'  => 'redtable',    'blue.txt' => 'bluetable' ); while ( ($file, $table) = each %files ) {   insert_file($file, $table); };

not sure how you'd use bulk insert given field separator in data file seems indeterminate (i.e. regex /(.*)(..)(..)/ defines field boundaries). may need use formatfile option. also, if generate bulk insert statement using dbi, aware user sql server runs must able read data files on filesystem (networked, local or otherwise).


Comments

Post a Comment

Popular posts from this blog

c++ - CryptStringToBinary API behavior -

i seeing strange behavior cryptstringtobinary cryptography api. please see below code (config: x64 debug): #include "stdafx.h" #include <windows.h> #include <strsafe.h> #include <iostream> #include <exception> void main() { dword dwskip; dword dwflags; dword dwdatalen; //lpcwstr pszinput = l"jaaaaaecaaadzgaaakqaagdnnl1l56bwgfjdgr3rpxtqqqn6daw3usv2emkjym4t"; //this works fine lpcwstr pszinput = l"mytest"; //doesnt work, api returns false,error code 0x0000000d // determine size of byte array , allocate memory. if(! cryptstringtobinary( pszinput, _tcslen( pszinput ) + 1, crypt_string_base64, null, &dwdatalen, &dwskip, &dwflags ) ) { dword dw = getlasterror(); //0x0000000d: data invalid throw std::exception( "error computing byte length." ); } byte *pbytebyte = null; try { ...
Read more

java.util.scanner - How to read and add only numbers to array from a text file -

i'm learning java , have question regarding reading file want read numbers file contains strings well. here example of file: 66.56 "3 java 3-43 5-42 2.1 1 and here coding: public class test { public static void main (string [] args){ if (0 < args.length) { file x = new file(args[0]); try{ scanner in = new scanner( new fileinputstream(x)); arraylist<double> test = new arraylist<>(); while(in.hasnext()){ if(in.hasnextdouble()){ double f=in.nextdouble(); test.add(f);} else {in.next();} } catch(ioexception e) { system.err.println("exception during reading: " + e); } } my problem add 66.56,2.1 , 1 doesn't add 3 after "3 or ignores 3-43 , 5-42 can tell me how skip strings , add doubles here? thanks all said 3 ie; "3, 3-43 , 4-42 strings either u read string , split , check number @ " , - or put in space between characters , integers. jvm after compilation tre...
Read more

iphone - Three second countdown in cocos2d -

i trying have 3 second countdown display when level beat in game. did: -(void) gamesegmentbeat { [self pauseschedulerandactions]; // overlay game opaque background ccsprite *opaquebg = [ccsprite spritewithfile:@"background1.png"]; opaquebg.position = screencenter; [self addchild:opaquebg z:10000]; // these 3 labels 3 seconds cclabelttf *three = [cclabelttf labelwithstring:@"3" fontname:@"helvetica" fontsize:100]; three.position = ccp(screencenter.x, screencenter.y); cclabelttf *two = [cclabelttf labelwithstring:@"2" fontname:@"helvetica" fontsize:100]; two.position = ccp(screencenter.x, screencenter.y); cclabelttf *one = [cclabelttf labelwithstring:@"1" fontname:@"helvetica" fontsize:100]; one.position = ccp(screencenter.x, screencenter.y); // secondspast specified in update method secondspast = 0; if (secondspast == 1) { [self addchild:thr...
Read more