VBA procedure to import csv file into access

Question

i need procedure in VBA  to import data into access from csv excel file without some records   as header and footer   Example    i have table in csv file  which contains some sentence  which not belong table date    A1   this is some sentence title        A2                                                         title A3        A7          DATA      DATA        DATA        DATA       DATA A8      rows     DATA     DATA        DATA        DATA       DATA              A256    DATA      DATA        DATA      DATA A257    this is some  sentence     My Acess shoud contain only rows  between A7 to A256  Does anyone knows procedure or whatever in VBA who solves my problems     thanks  a lot    Edit

User · Answer

Your file seems quite small  297 lines  so you can read and write them quite quickly  You refer to Excel CSV  which does not exists  and you show space delimited data in your example  Furthermore  Access is limited to 255 columns  and a CSV is not  so there is no guarantee this will work  Sub StripHeaderAndFooter   Dim fs As Object   FileSystemObject Dim tsIn As Object  tsOut As Object   TextStream Dim sFileIn As String  sFileOut As String Dim aryFile As Variant      sFileIn    z  docs FileName csv      sFileOut    z  docs FileOut csv       Set fs   CreateObject  Scripting FileSystemObject       Set tsIn   fs OpenTextFile sFileIn  1    ForReading      sTmp   tsIn ReadAll      Set tsOut   fs CreateTextFile sFileOut  True    Overwrite     aryFile   Split sTmp  vbCrLf         Start at line 3 and end at last line -1     For i   3 To UBound aryFile  - 1         tsOut WriteLine aryFile i      Next      tsOut Close      DoCmd TransferText acImportDelim     NewCSV   sFileOut  False End Sub   Edit re various comments  It is possible to import a text file manually into MS Access and this will allow you to choose you own cell delimiters and text delimiters  You need to choose External data from the menu  select your file and step through the wizard    About importing and linking data and database objects -- Applies to  Microsoft Office Access 2003  Introduction to importing and exporting data -- Applies to  Microsoft Access 2010  Once you get the import working using the wizards  you can save an import specification and use it for you next DoCmd TransferText as outlined by  Olivier Jacot-Descombes  This will allow you to have non-standard delimiters such as semi colon and single-quoted text

User · Answer

The easiest way to do it is to link the CSV-file into the Access database as a table  Then you can work on this table as if it was an ordinary access table  for instance by creating an appropriate query based on this table that returns exactly what you want  You can link the table either manually or with VBA like this DoCmd TransferText TransferType  acLinkDelim  TableName   quot tblImport quot         FileName   quot C  MyData csv quot   HasFieldNames  true   UPDATE Dim db As DAO Database    Re-link the CSV Table Set db   CurrentDb On Error Resume Next    db TableDefs Delete  quot tblImport quot     On Error GoTo 0 db TableDefs Refresh DoCmd TransferText TransferType  acLinkDelim  TableName   quot tblImport quot         FileName   quot C  MyData csv quot   HasFieldNames  true db TableDefs Refresh    Perform the import db Execute  quot INSERT INTO someTable SELECT col1  col2      FROM tblImport  quot        amp   quot WHERE NOT F1 IN   A1    A2    A3   quot  db Close    Set db   Nothing

[ms-access] VBA procedure to import csv file into access

Examples related to ms-access

Examples related to vba

Examples related to csv