locked
SQL Syntax to import CSV file into MS Access - without missing the first record!

    Question

  • Current S/W Access 2010 and Windows 7 (all up to date)  also using VS2008

    When I was using Access 2003 & 2007 the following VB.Net code line worked fine:
    dbs.Execute("SELECT * INTO TrialBOYTbl FROM [Text;FMT=CSVDelimited;HDR=Yes;DATABASE=" & myPath & ";].[TrialBOYDtl#csv];")

    But when I use Access 2010 the first data record is missing in every file that I upload - there are no other errors.

    The SCHEMA.INI file is:
    [TrialBOYDtl.csv]
    ColNameHeader = True
    Format=CSVDelimited
    MaxScanRows = 25
    CharacterSet = ANSI
    CurrencyPosFormat $1
    CurrencyNegFormat ($1)
    Col1=AccountDescription   Text      Width 100
    Col2=BalanceDebit         Currency  Width 19
    Col3=BalanceCredit        Currency  Width 19

    NOTE: This code line is executed in VB.Net (Interop) in conjunction with Access Automation.  Therefore, I believe that I must use DAO
          as that is how MS Access is based.

    My guess is "[Text;FMT=CSVDelimited;HDR=Yes;DATABASE=" looks like an ADO (not DAO???) Extended Properties specification which worked fine until Access 2010.  Thoughts?

    Any thoughts or other approaches greatly appreciated!!

    Dennis

     


    Monday, August 29, 2011 9:17 PM

Answers

  • MS Access is a self contained Integrated RDBMS with its own development environment.   You may be better off just reading the contents of the .csv file using VBA.  Here's a sample VBA sub which reads each line of a text file then parses each field using the Split function ( |  as field separator):


    Sub ReadFile()
       Dim DB As DAO.Database, RS As DAO.Recordset, RS2 As DAO.Recordset
       Dim str1 As String, str2() As String, i As Integer
      
       DoCmd.SetWarnings False
       DoCmd.RunSQL "Delete * From tblA1"
       DoCmd.RunSQL "Delete * From tblA"
       Set DB = CurrentDb
       Set RS = DB.OpenRecordset("tblA1")
      
       Close #1
       Open "C:\1A\testRead.txt" For Input As #1
       i = 0
       Do While Not EOF(1)
          Line Input #1, str1
          If i > 1 Then
             RS.AddNew
             RS(0) = str1
             RS.Update
          End If
          i = i + 1
       Loop
       Close #1
         
       RS.MoveFirst
       Set RS2 = DB.OpenRecordset("tblA")
       Do While Not RS.EOF
          str2 = Split(RS(0), "|")
          RS2.AddNew
          For i = 0 To UBound(str2)
             RS2(i) = str2(i)
          Next
          RS2.Update
          RS.MoveNext
       Loop
       DoCmd.SetWarnings True
    End Sub

     


    Rich P
    • Marked as answer by DennisCPA Wednesday, August 31, 2011 3:09 AM
    Tuesday, August 30, 2011 7:07 PM

All replies

  • try this:

     

    dbs.Execute("SELECT * INTO TrialBOYTbl FROM [Text;FMT=CSVDelimited;HDR=No;DATABASE=" & myPath & ";].[TrialBOYDtl#csv];")
    


    thanks for any help
    Tuesday, August 30, 2011 2:15 PM
  • When I was using Access 2003 & 2007 the following VB.Net code line worked fine:
     dbs.Execute("SELECT * INTO TrialBOYTbl FROM [Text;FMT=CSVDelimited;HDR=Yes;DATABASE=" & myPath & ";].[TrialBOYDtl#csv];")
     
    But when I use Access 2010 the first data record is missing in every file that I upload - there are no other errors.
     
    The SCHEMA.INI file is:
     [TrialBOYDtl.csv]
     ColNameHeader = True
     Format=CSVDelimited
     MaxScanRows = 25
     CharacterSet = ANSI
     CurrencyPosFormat $1
     CurrencyNegFormat ($1)
     Col1=AccountDescription   Text      Width 100
     Col2=BalanceDebit         Currency  Width 19
     Col3=BalanceCredit        Currency  Width 19


    For what ever reason, Access 2010 appears to be cranky as to outside data.

    No difference (1st data row is missed every time) when I use RunSQL command and stipulate "HDR" as either "No" or "Yes." 
    I have spent many hours attempting to find a fix or a bug.

    I can get all of the data if I use TransferText:
      .DoCmd.TransferText(AcTextTransferType.acImportDelim, , "TrialBOYTbl", myPath & "TrialBOYDtl.csv", False)

    But, and there is always a But!, the Schema.ini file is ignored entirely by Access 2010.

    What that means is that:
    1) column/Field names are not pulled from the Schema.ini file -nor-
    2) the Field-Width parameter is ignored causing additional coding to add Field-names and change widths!

    Examples: "AccountDescription" field is named "F1" with a width of 255
              Whereas the Schema.ini sets a Width of 100

    In Short, I can get all of the data into the "TrialBOYTbl" table programatically. but then I have to individually
    specify Field-names and Field widths via VBA/SQL.  Any thoughts as to why the "TransferText" ignores the SCHEMA.INI file?

    Dennis

    Tuesday, August 30, 2011 5:45 PM
  • MS Access is a self contained Integrated RDBMS with its own development environment.   You may be better off just reading the contents of the .csv file using VBA.  Here's a sample VBA sub which reads each line of a text file then parses each field using the Split function ( |  as field separator):


    Sub ReadFile()
       Dim DB As DAO.Database, RS As DAO.Recordset, RS2 As DAO.Recordset
       Dim str1 As String, str2() As String, i As Integer
      
       DoCmd.SetWarnings False
       DoCmd.RunSQL "Delete * From tblA1"
       DoCmd.RunSQL "Delete * From tblA"
       Set DB = CurrentDb
       Set RS = DB.OpenRecordset("tblA1")
      
       Close #1
       Open "C:\1A\testRead.txt" For Input As #1
       i = 0
       Do While Not EOF(1)
          Line Input #1, str1
          If i > 1 Then
             RS.AddNew
             RS(0) = str1
             RS.Update
          End If
          i = i + 1
       Loop
       Close #1
         
       RS.MoveFirst
       Set RS2 = DB.OpenRecordset("tblA")
       Do While Not RS.EOF
          str2 = Split(RS(0), "|")
          RS2.AddNew
          For i = 0 To UBound(str2)
             RS2(i) = str2(i)
          Next
          RS2.Update
          RS.MoveNext
       Loop
       DoCmd.SetWarnings True
    End Sub

     


    Rich P
    • Marked as answer by DennisCPA Wednesday, August 31, 2011 3:09 AM
    Tuesday, August 30, 2011 7:07 PM
  • Rich,

    Even though you did not answer my question, I must give full style-points for a plan B.  Where others avoid or take shots, you reach out to help/solve.

    Thanks


    For all those who still need some thoughts on how to approach the original question:

     

      Symptoms observed when issues related to MS Access Import/Export are problematic
      * First line/row/record is missed/not-imported into an Access Table/Recordset
      * First line/row/record is imported as data (even though represent Field Headers) but is inconsistent with Data-type
      * SCHEMA.INI is 1) a true ANSI/ASCII file; 2) in the correct directory; 3) but seems to be ignored by Access.
      * Data is imported without correct headers and have generic headers like "F1", "F2", "F3", "F4", "F5", "F6"
      * Field widths/lengths are not as defined in Schema.ini

      Be aware inconsistency between Schema.ini settings and the Transfer method syntax can cause what appears to be
        MS Access ignoring the Schema.ini and/or importing errors
      Do make sure the Schema.ini is a true ANSI/ACSII file
      Do make sure the Schema.ini is in the directory containing the data file to be imported
      Be aware that Schema.ini does NOT change your computer's registry but will override certain registry settings
      Do make sure the Schema.ini is "syntaxed"/populated correctly:
           http://support.microsoft.com/kb/241477   <<<<<<< ************* !!!!!!!!!!!!!!!!!
           http://msdn.microsoft.com/en-us/library/ms709353(v=vs.85).aspx
           http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512
           http://support.microsoft.com/kb/230265
      Be aware of Access Transfer techniques:
         http://msdn.microsoft.com/en-us/library/aa141567(v=office.10).aspx
         http://www.databasejournal.com/features/msaccess/article.php/3853531/Working-with-external-text-files-in-MS-Access.htm


      Relevent Registry Key:  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text
      Name           Type  Data

      Default   REG_SZ  ﴾value not set﴿
      CharacterSet   REG_SZ  ANSI
      DisabledExtensions     REG_SZ  !txt, csv, Tab, Asc, tmp, htm, html)
      ExportCurrencySymbols  REG_BINARY 01
      Extensions   REG_SZ  txt,csv,tab,asc
      FirstRowHasNames  REG_BINARY 01
      Format   REG_SZ  CSVDelimited
       ImportFixedFormat  REG_SZ  RaggedEdge
       ImportMixedTypes  REG_SZ  Majority Type
      MaxScanRows   REG_DWORD 0x00000019 ﴾25﴿
      UseZeroMaxScanAs  REG_SZ  One
      Win32    REG_SZ  C:\Windows\system32\mstext40.dll

      It is necessary to have AccAppNew (Access Application) set "AccAppNew.Visible = True" so that the (focus)
      Vb.Net/Access Automation will know which Access Application ("Tools" or "AccAppNew") to process. This issue
      was not evident when Access 2007 was used but is an issue with Access 2010.
      The previous issue coupled with "TransPerGetAcct" Table/Query issues caused a great deal of wasted time
      attempting to find/fix both issues.  Access 2007 was forgiving as to whether TransPerGetAcct was a Query or a Table.
      This becomes important realizing that a Query is momentary as it refreshes each time it is run.  Whereas a Table
      accumulates data each time a Select Query is run. In addition, a Query can not be used in VB.Net to transfer
      (Import/Export) to Excel files.  Thus the workaround was that a Query became a Table that needed to be
      cleared/flushed each loop (in this "Do Until") else too many and inappropriate Transaction Numbers accumulating
      in the Account & Contra-account analyses. In short:

      * AccAppNew must be "Visible" (If "Office Automation" or other direct control of Access is in play)
      * SCHEMA.INI must be written in ANSI and all command words utilized as specified in the Schema.ini documentation.
      * TransPerGetAcct is populating a Table (so the data can be exported to Excel) then must be cleared each loop!
      * Need Constency with SQL commands as to file contents and the file(s) containing Fieldnames and data widths!!
         If the Text or cvs file does not have headers in the data file then, File Headers; Data Types; and Data Widths
         will come from SCHEMA.INI or another "Specifications" file, the format of which, depends whether DAO; ADO;
         or other code paradyne is used.  In this case the SQL code line was:  (it works just fine!  NOTICE the "HDR=No"

      * SYNTAX #1: SQL   (Context:  VB.Net coupled with MS Access Interop)  USE THIS METHOD IT WORKS !!!
          "SELECT * INTO TransactionsTbl FROM [Text;FMT=CSVDelimited;HDR=No;DATABASE=" & myPath & ";].[TransactionsDtl#csv];", _
             DAO.RecordsetOptionEnum.dbFailOnError)

      * SYNTAX #2: VBA/VB.Net   (Context:  VB.Net coupled with MS Access Interop)
           DoCmd.TransferText(AcTextTransferType.acImportDelim, , "TransactionsTbl", myPath & "TransactionsDtl.csv", False)
         NOTE: Do NOT use this Method as there is a bug! see: http://support.microsoft.com/kb/241477

      * CONSISTENCY:  Schema.ini Must have "ColNameHeader=False" -AND-
         1)    SYNTAX #1: SQL the SELECT command contains "HDR=No"
         2)    SYNTAX #2: VBA/VB.Net note the "blank space" between ", ," which causes the code interpreter to
                             to assume that there is a Schema.ini in the default directory folder.  If not,
                             one must enter file name containing "Specifications"
                             Also, "False" means no header information is included in the data file.
         If all above are true, and the proper info is entered into the Schema.ini file (like below!), then the data
         present in the data file to be imported into a MS Table with the (Schema.ini specified):
               Field Names;
               Data Type;
               Field Width
      * If the Scheme.ini file specification syntax is written properly (See examples below)
      * If the Scheme.ini file contains "ColNameHeader=False" then the Schema file info will be used to create the table! 
              Also, realize that you can have multiple templates within a single Scheme.ini file. 
              Example of three specification templates within one ANSI-based (written in Notepad)
              "SCHEMA.INI" file next below:

               [TransactionsDtl.csv]
               ColNameHeader = False
               Format = CSVDelimited
               TextDelimiter = ""
               MaxScanRows = 10
               CharacterSet = ANSI
               CurrencySymbol=$
               CurrencyPosFormat $1
               CurrencyNegFormat ($1)
               Col1="TranNum"            Char     Width 10
               Col2="AccountDescription" Char     Width 100
               Col3="ContraAccDescript"  Char     Width 100
               Col4="Date"               DateTime Width 8
               Col5="Amount"             Double   Width 19
               Col6="DocSeqNumber"       Char     Width 10
               Col7="Class"              Char     Width 25
               Col8="ProcessName"        Char     Width 25
               Col9="AccountType"        Char     Width 25
               Col10="CustomerName"      Char     Width 75

               [TrialBOYDtl.csv]
               ColNameHeader = False
               Format = CSVDelimited
               TextDelimiter = ""
               MaxScanRows = 10
               CharacterSet = ANSI
               CurrencySymbol=$
               CurrencyPosFormat $1
               CurrencyNegFormat ($1)
               Col1="AccountDescription" Char     Width 100
               Col2="BalanceDebit"       Double   Width 19
               Col3="BalanceCredit"      Double   Width 19

               [TrialEOYDtl.csv]
               ColNameHeader = False
               Format = CSVDelimited
               TextDelimiter = ""
               MaxScanRows = 10
               CharacterSet = ANSI
               CurrencySymbol=$
               CurrencyPosFormat $1
               CurrencyNegFormat ($1)
               Col1="AccountDescription" Char    Width 100
               Col2="BalanceDebit"       Double  Width 19
               Col3="BalanceCredit"      Double  Width 19

      * The Schema.ini file must be in exactly the same Directory as the file to be imported/exported
      * MaxScanRows is a very important setting.  MS Access in contrast to MS Excel is very particular
               about the "Type" of data being imported.  I.e., let us assume that the the data field
               "LicenseNumber" in Excel, cvs, text file is populated with four records:
                    AKS-FRG
                    B-222-A
                    23 24 25
                    836466
               In Excel, if a column is formated as "Text" all the above would be displayed/processed as text
               Whereas Access (if Access is to import the Excel information) would generate a file called
               ????.... ImportErrors and possibly not import any of them into the desired table!  This means
               user intervention is needed to overcome Access's need for data putity.  
               Simply, Access states only pure numbers will be entered into a column Access-formated as
               an Integer; Long; Dec; Currencyl etc.  One work around I use is to manually prepend a single-quote
               to the beginning of (i.e. from above) '836466 while it is in the data file to be imported.
               Therefore all data prepended with  a single quote will be imported as text into Access.
      * MaxScanRows is a setting that overrides Access's Registry setting for "How many records should I (Access)
               review before deciding the data-type for the respective field?"  A setting of "0" means Access will
               review ALL records in the data to be imported and decide which data-type (majority-wins!)
               All other values are the exact number of records to review.  Default (in Registry) is 25.

    • Edited by DennisCPA Thursday, September 01, 2011 7:56 PM Provide Closure
    Wednesday, August 31, 2011 3:12 AM