vba - Import 150 text files to Access as Tables using SQL -


i have 150 text files in csv format using pipe | seperator. files in different folders on network. filenames date specific , have suffix holds date. have created table has 3 fields: file location; file name excluding suffix; suffix (in format yymmdd).

i want create sql script import each of 150 files named in table have created 150 seperate access 2007 tables named file name excluding suffix. tried using vba filenames contain full stops , vba didn't this.

each of files has different column structure first row headers. can use features native access 2007 organisation work not allow third party addons or applications. don't have sql server available me.

i complete novice when comes access , struggling achieve approaching this. can help?

i struggled achieve wanted in access , went on excel instead. code below creates each of text files sheet within single excel workbook master table holding filename / path etc "master" sheet within workbook. deletes of sheets other "master" before recreating them , if file not found leave blank sheet.

sub importfiles()  'this script looks @ master list of files import , imports each own tab 'the files pipe (|) delimited , can in addressable location 'if files not found, import of file skipped, leaving blank worksheet  'close report file before starting import     application.displayalerts = false     on error resume next     windows("report.xlsx").activate     windows("report.xlsx").close savechanges:=true     on error goto 0     application.displayalerts = true  'start looking @ sheet contains master list of files     sheets("master").activate  'declare 3 arrays of unknown length     dim filename() string     dim filepath() string     dim fullname() string  'initially there no files     dim numberfiles integer     numberfiles = 0  'loop on of file cells 'the master file needs structured filename, filepath , fullname in order 'change c2 cell containing first filename in master list     dim filecell range     dim topcell range     dim filerange range      set topcell = range("c2")     set filerange = range(topcell, topcell.end(xldown))     each filecell in filerange  'we've found file!     numberfiles = numberfiles + 1  'for each file found, extend arrays     redim preserve filename(numberfiles - 1)     redim preserve filepath(numberfiles - 1)     redim preserve fullname(numberfiles - 1)  'now store filename, path , fullname of new file     filename(numberfiles - 1) = filecell.value     filepath(numberfiles - 1) = filecell.offset(0, 1).value     fullname(numberfiles - 1) = filecell.offset(0, 2).value  next filecell  'delete existing sheets except master , create new blank sheets each sheet in application.worksheets     application.displayalerts = false     if sheet.name <> "master"     sheet.delete     end if     next sheet     sheets("master").activate     = 1 (numberfiles)     worksheets.add(after:=sheets(sheets.count)).name = "sheet" &     next     thisworkbook.sheets("sheet1").select  'start process of import  'create workbook object workbook current macro running     dim ws worksheet  'import each file own sheet     = 0 (numberfiles - 1)      set ws = thisworkbook.sheets("sheet" & + 1) 'the current sheet  'ignore missing files , carry on processing     application.displayalerts = false     on error resume next  'this imports delimited files     ws.querytables.add(connection:="text;" & fullname(i),     destination:=ws.range("$a$1"))         .name = "a" &         .fieldnames = true         .rownumbers = false         .filladjacentformulas = false         .preserveformatting = true         .refreshonfileopen = false         .refreshstyle = xlinsertdeletecells         .savepassword = false         .savedata = true         .adjustcolumnwidth = true         .refreshperiod = 0         .textfilepromptonrefresh = false         .textfileplatform = 850         .textfilestartrow = 1         .textfileparsetype = xldelimited         .textfiletextqualifier = xltextqualifierdoublequote         .textfileconsecutivedelimiter = false         .textfiletabdelimiter = true         .textfilesemicolondelimiter = false         .textfilecommadelimiter = false         .textfilespacedelimiter = false         .textfileotherdelimiter = "|"         .textfilecolumndatatypes = array(1, 1, 1)         .textfiletrailingminusnumbers = true         .refresh backgroundquery:=false      end  'rename tabs name on master list     ws.name = "" & filename(i)      next  'reopen report file     workbooks.open filename:=thisworkbook.path & "\report.xlsx"  end sub

Comments

Post a Comment

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

i have following sample code used pre-decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(--c); cout << c << endl; return; } } this function give output input 4 : dp3 dp2 dp1 dp0 0 1 2 3 but when use post decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(c--); cout << c << endl; return; } } output goes in infinite loop dp4 can explain me in detail why happens ? this happens because function(c--) called same value of c , when finishes c decremented. recursively called, hence called same value no chance of return , hit stack overflow error. assume this, int x = 1, y = 0; y = x++ result y == 1 , x == 2. if y = ++x , bot x , y 2.
Read more

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

i added new project. made mistake in projectname.conf file in sites-available apache2, led me reinstalling apache2. after reinstalling , re-enabling projects on every page of every project .. <body> run(); </body> .. i've run composer update, restarted service, restarted pc, checked config files, tried disabling new project. there nothing in error log, , access.log: 127.0.0.1 - - [15/may/2015:01:40:47 +0200] "get / http/1.1" 304 181 "-" "mozilla/5.0 (x11; ubuntu; linux x86_64; rv:38.0) gecko/20100101 firefox/38.0" maybe i'm forgetting apache2 reinstall? don't think did different last time installed it. looks haven't setup php correctly. take @ this great guide , maybe you're forgetting link php config apache2.
Read more

php - How can I echo out this array? -

i don't know how go grabbing data in variable , put foreach loop. class tree{ private $info = array( array('name'=>'jim','state'=>'nu','company'=>'nu','phone'=>array('cell'=>'5615111111','office'=>'5611111111'),'email'=>array('primary'=>'exs@example.com','ex@there.com')), array('name'=>'joe smith','city'=>'phoenix','phone'=>'4805551111','email'=>'jsmith@some_email.com'), array('name'=>'john doe','city'=>'chandler','company'=>'doe co.','email'=>array('jdoe@gmail.com','personal'=>'email@email.com'),'phone'=>'6025550002') ); } if you're inside of class, can access private variable using $this->variablename . example, i...
Read more