Import Text File Into Table

[chalupabatma]

I feel I am on the brink of a breakthrough with what I am needing to achieve. I have a text file that I have the field mappings for, and I need to import the text file into an access table. Below are the field mappings (well a snippet of them, enough data to get point)

Attributes DataType FieldName IndexType SkipColumn SpecID Start Width
0 10 1 0 FALSE 2 1 15
0 10 2 0 FALSE 2 16 15
0 10 3 0 FALSE 2 31 15
0 10 4 0 FALSE 2 46 15


Now I have this VBA which will allow me to select my text file and (I think) iterate over each record in the file, but how do I import the data

Private Sub ImportTextFileToTable_Click()

Const ForReading = 1, ForWriting = 2, ForAppending = 8

Dim fso, MyFile
Dim fDialog As Object
Dim TextLine As String
Dim FileName As String

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog
.AllowMultiSelect = False
.Title = "Select a File To Copy"
.Filters.Clear
.Filters.Add "Text", "*.txt"
.Filters.Add "Text", "*.csv"

.InitialFileName = Application.CurrentProject.Path
.Show
If .SelectedItems.Count = 0 Then
MsgBox "No file selected."
Else
FileName = fDialog.SelectedItems(1)
End If
End With


Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.OpenTextFile(FileName, ForReading)
Do While MyFile.AtEndOfStream <> True
TextLine = MyFile.ReadLine
'How to import the data into appropriate columns in tableo it's appropriate field in the table? That is where I am stuck. Below is the VBA I have to this point...

[chalupabatma]

--**BUMP**--

Anyone with
VBA skeals know how to achieve this?

[jonh]

https://msdn.microsoft.com/en-us/library/office/ff835958.aspx

[chalupabatma]

I saw that article, but I do not see how it allows me to input my own custom field mappings.

[jonh]

Use the import wizard to import the file manually. If you look at the options it should give the chance to save the import settings with a filename. Use that filename as the importspec (specificationname).

[jonh]

Your opening post doesn't describe the problem very well. Most of the code appears to be missing.

[chalupabatma]

Your opening post doesn't describe the problem very well. Most of the code appears to be missing.

I am wanting to import a text file with no delimitation. Each field has custom mappings (which I provided in my initial post) - I am wanting to have VBA to import the text file using the custom field mappings provided in my initial post.

[jonh]

as far as i recall, the field spec handles that. try it.

[chalupabatma]

as far as i recall, the field spec handles that. try it.

Field spec in which piece of the process? The VBA or the import process you recommended?

[jonh]

Do it manually ,use the wizard, to save the spec.

Then use the spec in VBA.

To save the spec click 'advanced' before 'finished' in the wizard.