J
JimS
I need to import a .tsv file. It has 132,000 rows, or I'd just go through
Excel. Using Access 2003...
Figured out how to point to it with ADO. Got it open. See the 132,000 rows.
Can't see any of the columns...all show as null. The file has two header
rows. Row 1 is the Report Name, and Row two is the Column Titles. Row 3
starts data. There are 35 Columns, and ADO sees 35 columns, just considers
them all null. I want 'em to all be text, so I can convert them as needed.
Or, various types, just under my control. Here's my code. Any ideas?
Sub ProcessTSV()
Const adCmdText = &H1
Dim strTSVPathFile As String
Dim strTSVPath As String
Dim strTSVFile As String
strTSVPathFile = GetTSVFileName("H:\mtv\secure\Construction\Access")
If IsNull(strTSVPathFile) Then
Exit Sub
End If
strTSVFile = ParseFileName(strTSVPathFile)
strTSVPath = ParsePath(strTSVPathFile)
Dim cnn As New ADODB.Connection
Dim rstInv As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strTSVPath & ";" & _
"Extended Properties='text;HDR=NO;FMT=TabDelimited'"
rstInv.Open "Select * FROM " & strTSVFile, _
cnn, adOpenStatic, adLockReadOnly, adCmdText
Debug.Print rstInv.RecordCount
Dim i As Long
While Not rstInv.EOF
For i = 0 To rstInv.Fields.Count - 1
Debug.Print i, ". "; rstInv.Fields.Item(i)
Next i
rstInv.MoveNext
Wend
rstInv.Close
cnn.Close
Set rstInv = Nothing
Set cnn = Nothing
End Sub
Excel. Using Access 2003...
Figured out how to point to it with ADO. Got it open. See the 132,000 rows.
Can't see any of the columns...all show as null. The file has two header
rows. Row 1 is the Report Name, and Row two is the Column Titles. Row 3
starts data. There are 35 Columns, and ADO sees 35 columns, just considers
them all null. I want 'em to all be text, so I can convert them as needed.
Or, various types, just under my control. Here's my code. Any ideas?
Sub ProcessTSV()
Const adCmdText = &H1
Dim strTSVPathFile As String
Dim strTSVPath As String
Dim strTSVFile As String
strTSVPathFile = GetTSVFileName("H:\mtv\secure\Construction\Access")
If IsNull(strTSVPathFile) Then
Exit Sub
End If
strTSVFile = ParseFileName(strTSVPathFile)
strTSVPath = ParsePath(strTSVPathFile)
Dim cnn As New ADODB.Connection
Dim rstInv As New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strTSVPath & ";" & _
"Extended Properties='text;HDR=NO;FMT=TabDelimited'"
rstInv.Open "Select * FROM " & strTSVFile, _
cnn, adOpenStatic, adLockReadOnly, adCmdText
Debug.Print rstInv.RecordCount
Dim i As Long
While Not rstInv.EOF
For i = 0 To rstInv.Fields.Count - 1
Debug.Print i, ". "; rstInv.Fields.Item(i)
Next i
rstInv.MoveNext
Wend
rstInv.Close
cnn.Close
Set rstInv = Nothing
Set cnn = Nothing
End Sub