I am looking for a way to create a button on one of my forms that will do the following:
1) Import a CSV file (which has tons of garbage fields that need to be removed).
2) Format the CSV (remove the useless fields, will always be the same fields).
3) save into a specific Table (overwrite the table data if table already exists).
Is this even possible? Right now I got the following code (doesn't format just imports) but it keeps failing due to very long fields in my CSV:
Run-time error '3047': Record is too large
Code:
Private Sub Image10_Click()
If MsgBox("This will open the Excel folder for spreadsheet imports. Continue?", vbYesNoCancel) = vbYes Then
Dim i As Integer
Dim tblStr As String
Dim varItem As Variant
i = 1
tblStr = ""
With Application.FileDialog(msoFileDialogFilePicker)
With .Filters
.Clear
.Add "All Files", "*.*"
End With
.AllowMultiSelect = True
.InitialFileName = "c:\"
.InitialView = msoFileDialogViewDetails
If .Show Then
For Each varItem In .SelectedItems
For i = 1 To Len(varItem)
If IsNumeric(Mid(CStr(varItem), i, 1)) Then
tblStr = tblStr & Mid(CStr(varItem), i, 1)
End If
Next i
If Right(CStr(varItem), 4) = ".csv" Then
DoCmd.TransferText acImport, , "tmpExport", CStr(varItem)
i = i + 1
DoCmd.OpenTable tblStr, acViewNormal, acReadOnly
MsgBox "Data Transferred Successfully!"
DoCmd.Close
tblStr = ""
End If
Next varItem
DoCmd.Close
End If
End With
End If
End Sub