VBA Access: Import CSV with additonal header data -

i new coding vba. wondering if me? have csv file structured following: - first 22 rows cover specfic header data(this loads in 1 column in excel) - column headers table in row 23 - data located row 24 onward.

what code needs insert data in new table right column titles. while inserting needs input file name , header data in first few columns of table.

so far have imported entire csv array believe:

see have far:

sub readcsv() dim fs object dim fso new filesystemobject dim tsin object dim sfilein, filename string dim aryfile, aryheader, arybody variant  sfilein = "c:\doc\test.csv"  set filename = fso.getfilename(sfilein)  set fs = createobject("scripting.filesystemobject") set tsin = fs.opentextfile(sfilein, 1) stmp = tsin.readall aryfile = split(stmp, vbcrlf)  = 1 22     aryheader(1, i) = aryfile(i) next  = 23 ubound(aryfile)     arybody(i) = split(aryfile(i), ",")     docmd.runsql "insert mains values (filename,aryheader(1),arybody(i))" next  end sub 

is correct? can see of taking right approach

update - recoded bit

i bit irked use of multiple arrays in code (which super confusing, me, anyway, because looking @ counters everywhere) thought post alternative you. if can way, more power you, if run problems, can try this. code below more verbose, may save time in future if hand off or have come , have no idea going on (lol):

sub readcsv() on error goto errorhandler          dim db dao.database     dim rst dao.recordset      dim fso scripting.filesystemobject     dim tst scripting.textstream      dim strfilename string     dim intcurrentline integer      dim strcurrentline string     dim intheaderrows integer     dim strheader string     dim strheaderdeliminfield string      'consider these 'constants', don't come code in month     'and wonder random numbers mean.     intheaderrows = 22              'number of header rows in csv.     strheaderdeliminfield = "~"     'the character(s) want separate each                                     'header line, in field.      strfilename = "c:\irregularcsv.csv"     intcurrentline = 1      'keep track of line in file on.      'next 2 lines reference table; add data via dao , not sql,     'to avoid messy dynamic sql.     set db = currentdb()     set rst = db.openrecordset("mains", dbopendynaset)      set fso = new scripting.filesystemobject     set tst = fso.opentextfile(strfilename, forreading)      'instead of storing data in arrays, let's go through file line line     'and work need do.     tst         until .atendofstream             strcurrentline = .readline              if intcurrentline <= intheaderrows                 strheader = strheader & strheaderdeliminfield & strcurrentline             else                 'add records via dao here.                 rst.addnew                     'in dao, rst.fields("fieldname") columns in table.                     rst.fields("filename") = strfilename                      'remove leading delimiter right.                     rst.fields("headerinfo") = right(strheader, len(strheader) - 1)                       'note split returns zero-based array                     'and unaffected option base statement.                     'the way below less efficient storing                     'the return of split, less confusing, imo.                     rst.fields("field1") = split(strcurrentline, ",")(0)                     rst.fields("field2") = split(strcurrentline, ",")(1)                     rst.fields("field3") = split(strcurrentline, ",")(2)                 rst.update             end if              intcurrentline = intcurrentline + 1         loop     end      tst.close     rst.close  exitme:     set tst = nothing     set fso = nothing     set rst = nothing     set db = nothing      exit sub errorhandler:     debug.print err.number & ": " & err.description     goto exitme  end sub 

to honest, think there lot of gotchas way going it. not saying won't work, because think can, method more robust. unexpected single quote won't ruin work , using data object inserts not prone (well, less, @ least) sql injection issues. , i've done no persisted arrays. anyway, food thought. luck.