Create a spec file or set field data types

[DavidPhillips]

Looking for a way to use transfertext to make a table and create a spec file from csv files I am importing. I want all fields in the table to be shorttext.

Otherwise I need another way to bring the files into a table.

Update:

I have a generic spec to use that brings in any data as text.

Created the spec from a delimited file with 255 text fields... Field001, Field002, etc.

However the field names need to be changed. My field names are in the first row of a table. I will need to read each fields data in the first row and rename the field.




Thanks

 

[DavidPhillips]

I got something that works. Not sure if it is the best way but it will do for now. Instead of using a spec file I decided to read the header line from the first file imported and use that to create a table with all fields as text because the headers are all text.

Private Sub cmdImportLVData_Click()
    On Error Resume Next
    
    Dim varItem As Variant
    Dim cnt As Integer
    Dim pbStep As Integer
    Dim fDialog As Object
    
    Access.DoCmd.SetWarnings False
    Access.Application.AutomationSecurity = msoAutomationSecurityForceDisable
    
    Set fDialog = Application.FileDialog(1)
        
    With fDialog
        .AllowMultiSelect = True
        .Title = "Select One or More Lineviewer Files"
        .Filters.Clear
        .Filters.Add "LVDatabaseInfo Files", "*.csv"
        If .Show = True Then
            Me.pbar.Width = 0
            Me.pbar.Visible = True
            cnt = fDialog.SelectedItems.COUNT
            pbStep = 3000 / cnt
    
            For Each varItem In .SelectedItems
                'Check for new table
                If TableExists("LVDatabaseInfo") <> True Then 'Create new table
                    
                    Dim FileSysObj, OpenFile, TxtLine
                    Const ForReading = 1, ForWriting = 2, ForAppending = 8
                    Set FileSysObj = CreateObject("Scripting.FileSystemObject")
                    'Open selected file
                    Set OpenFile = FileSysObj.OpenTextFile(varItem, ForReading, False)
                    'Read the header
                    TxtLine = OpenFile.readline
                    'Close file
                    OpenFile.Close
                    'Get temp folder
                    Tmpfldr = IIf(Environ$("tmp") <> "", Environ$("tmp"), Environ$("temp"))
                    'Open temp file
                    Set OpenFile = FileSysObj.OpenTextFile(Tmpfldr & "\tmpfile.csv", ForWriting, True)
                    'Write header to tmp file
                    OpenFile.WriteLine TxtLine
                    'Close file
                    OpenFile.Close
                    'Import header file to new table
                    DoCmd.TransferText acImportDelim, , "LVDatabaseInfo", Tmpfldr & "\tmpfile.csv", True
                    'delete temp file
                    Kill Tmpfldr & "\tmpfile.csv"
                    
                    Set FileSysObj = Nothing
                    Set OpenFile = Nothing
                    Set TxtLine = Nothing
                    
                End If
                
                Me.pbar.Width = pbar.Width + pbStep
                Me.Repaint
                Access.DoCmd.TransferText acImportDelim, , "LVDatabaseInfo", varItem, True
                Main.DeleteImportErrorTables
            Next

        End If
    End With
    
    Me.pbar.Width = 0
    pbar.Visible = False
    
    Set fDialog = Nothing
End Sub

edit:

Found a problem where . is invalid in a table header):

Fixed it like this.

Set OpenFile = FileSysObj.OpenTextFile(Tmpfldr & "\tmpfile.csv", ForWriting, True)
                    'Remove .
                    TxtLine = Replace(TxtLine, ".", "")
                    'Write header to tmp file
                    OpenFile.WriteLine TxtLine