DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Problem importing text field from file into Access via VB

  1. #1
    Join Date
    Jun 2006
    Posts
    168

    Problem importing text field from file into Access via VB

    Hey guys. i am using VB6 to create a SQL string to import a text file (comma delimited) into Access.

    the code i am using is this:

    Code:
        Write #fnum, "Formulating SQL string for import....."
        sqlString = "INSERT INTO [" & tableName & "] (" & vbCrLf & vbTab
    
        intfields = UBound(Fields())
        For i = 0 To intfields Step 1
            sqlString = sqlString & "[" & Fields(i) & "]"
            If Not i = intfields Then
                sqlString = sqlString & ", " & vbCrLf & vbTab
            End If
        Next i
        sqlString = sqlString & vbCrLf & ")" & vbCrLf & " SELECT "
        
    '    For i = 0 To intfields Step 1
    '      sqlString = sqlString & "[" & Fields(i) & "]"
    '      If Not i = intfields Then
    '          sqlString = sqlString & ", " & vbCrLf & vbTab
    '      End If
    '    Next i
        
        sqlString = sqlString & "*"
    
        sqlString = sqlString & vbCrLf & " FROM [Text;DATABASE=" & textFilePath & "].[" & textFname & "];"
    the commented out area was something else i tried to make work, by relisting the fields from the text file.

    everything loads correctly, except for one field.

    this field in the database structure is text, and in the txt data file the first 50 lines or so are "-999" (without quotes). when doing a File->external data->Import in access, most of the time it uses a long int for this field, and also loses the data, unless field is manually changed to text.

    however, I do not understand why this field is not being imported when i do this with my code.


    the -999's are imported, but none of the other values. ALL the other data is imported except for these values.

    what could be the cause? I'm kind of lost
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  2. #2
    Join Date
    Jun 2006
    Posts
    168
    update:


    I made a very small sample file which included 4 lines. normal data that we take, just a variety in the RFmod field.

    in this order (only listing the rfmod field):
    text
    text
    -999

    the data input was flawless.

    i then added the 4th line, to make it like so:
    -999
    text
    text
    -999

    the rfmod field was NOT imported (aside from the -999 data).

    WHY would this do this?

    and again, this does not happen when doing file->external data->import file inside of access.

    what gives? and what can i do to correct this?

    is it something in my sql statement?

    maybe i should do some other kind of ado command to get the data into access?

    help is appreciated
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  3. #3
    Join Date
    Jun 2006
    Posts
    168
    hello?


    in a large dataset, if there are more than 8 -999's at the beginning of the file, then the other items are skipped.

    I think the problem is that although the field is designed as a text field, the method of importation I am using through the Access database engine sees the -999 and assumes it is a number . then , because there are so many -999's in the beginning of the file, Access assumes that the other entries in that field were human error and should also be numbers as well, and omits them.

    This in itself is a computer error.

    is there a way around this, or a way to prevent it?


    and, does anything similar happen with SQLServer or MySQL databases?
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  4. #4
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    You can use a SCHEMA.INI file to explicitly tell the Access database engine the type of each column: http://www.google.com/search?q=vb+access+schema.ini
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  5. #5
    Join Date
    Jun 2006
    Posts
    168
    Quote Originally Posted by Phil Weber
    You can use a SCHEMA.INI file to explicitly tell the Access database engine the type of each column: http://www.google.com/search?q=vb+access+schema.ini

    Hm I will try this.

    I was looking at this linK: http://support.microsoft.com/kb/q155512/

    it is written with DAO i think from VBA, but is there anything pre-written to do this (create schema.ini file programattically from an existing database) in ADO from VB6?

    I'm searching, but haven't found anything yet.

    right now when I import the text file, if a table does not already exist, i create my own using the first line (header) as a template and declaring data types based on the names of the fields. so i may just write my own and re-use some of that same code.
    thanks, phil !
    Last edited by chupacabra; 06-18-2007 at 10:18 AM.
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  6. #6
    Join Date
    Jun 2006
    Posts
    168
    I got it to work, Thanks!!

    ps. here is the code i used to generate the schema.ini file:
    Code:
    Public Function CreateAccessSchemaFile(FieldArr() As String, ByVal textFpath As String) As String
      'creates schema file for access to use to import file.  returns filepath for schema file
      On Error GoTo errH
      Dim i As Integer, Handle As Integer, numFields As Integer
      Dim SchemaPath As String
      Dim DataInfo As String
      
      numFields = UBound(FieldArr())
      
      SchemaPath = textFpath
      textFpath = getFileName(SchemaPath, True)
      MakePath SchemaPath
      
      SchemaPath = SchemaPath & "Schema.ini"
      Handle = FreeFile
      Open SchemaPath For Output As #Handle
      Print #Handle, "[" & textFpath & "]"          'file name of text file to import
      Print #Handle, "ColNameHeader = True"         'header contains column names
      Print #Handle, "CharacterSet = ANSI"          'character set of text file
      Print #Handle, "Format = Delimited(,)"        'Comma delimited
      Print #Handle, "MaxScanRows=0"                'whole file is scanned
      
      For i = 0 To numFields Step 1
        DataInfo = GetDBDataType(FieldArr(i))
        If InStr(1, DataInfo, "TEXT") <> 0 Then
          DataInfo = "CHAR Width " & GetStrBetween(DataInfo, "TEXT(", ")")
        End If
        Print #Handle, "Col" & Format$(i + 1) & "=" & FieldArr(i) & Space$(1) & DataInfo
      Next i
      
      Close #Handle
      
      CreateAccessSchemaFile = SchemaPath
      Exit Function
    errH:
      MsgBox Err.Description
    End Function
    Last edited by chupacabra; 06-18-2007 at 02:07 PM.
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

Similar Threads

  1. Replies: 6
    Last Post: 06-24-2005, 01:49 AM
  2. Replies: 0
    Last Post: 07-17-2001, 02:13 PM
  3. Re: Access DB Problem
    By Sridhar Nivarty in forum VB Classic
    Replies: 1
    Last Post: 01-07-2001, 12:11 AM
  4. Replies: 0
    Last Post: 04-17-2000, 01:29 PM
  5. Importing a text file with code
    By Gerry in forum VB Classic
    Replies: 0
    Last Post: 04-16-2000, 04:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center