Problem importing text field from file into Access via VB

[chupacabra]

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:

    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

[chupacabra]

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

[chupacabra]

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?

[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

[chupacabra]

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 !

[chupacabra]

I got it to work, Thanks!!

ps. here is the code i used to generate the schema.ini file:

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