Solved: Custom TransferText? Importing Txt data

[Touni102]

Hello, it's me again... with the next problem. Every time I post here I feel as if i know nothing...

Anyway, the problem is I need to import rows into a table from a Text file. I have been using TransferText to create / update tables before, but the problem is I need to only import certain rows in the txt file. To be exact, the first two rows of my text file should not be imported. And also to make things even more complicated, my first col needs to be a fixed value for each row (i.e. -- the name of the file being imported), and the remaining rows need to come from the txt file.

Where to start...
Thanks.

[CreganTur]

You could use an ADO connection to read the data out of your text file and into a recordset. Then you could use the MoveNext method a couple times to skip the firs two lines, and then work with the rest of the data. You can also create calculated fields, which is how you would add the filename as a field value.

This code will create a recordset from a comma delimited text file:

DIm conn As ADODB.Connection
Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFilepath & ";" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";"
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM [" & strFileName & "] ",conn

Now, strFilePath is the filepath to the folder that contains your text file. strFileName is just the name of the text file. You'll need to figure out how you want to get these values into the code.

The code creates a connection to a folder, and then selects all records from the specified text file and reads them into a recordset. Once they're in the recordset, you will have a host of methods that you can use to manipulate the records so you can import them into your Access database.

You will need a reference (Tools-> References) to Microsoft ActiveX Data Objects.

HTH:thumb

[stanl]

If you txt file is .csv you can use Logparser which permits skipping lines and will automatically export to a table; otherwise you might consider using SELECT INTO.... [a temp table] with a schema.ini entry which mirrors the real target table, then just delete the first 2 rows, update the first column, append to the target table and finally erase the temp table. .02 Stan

[CreganTur]

@Stan

I've never heard of logparser before; just looked it up and it looks like a great program.Thanks for the info!

[stanl]

@Stan

I've never heard of logparser before; just looked it up and it looks like a great program.Thanks for the info!

You might want to search for "logparser" on this site - I have posted a few snippets that might interest you. Stan

[Touni102]

Thanks for the reply, sorry for my late reply.
I tried the snippet of code you posted and used a FileDialog to get the filename. After selecting the file, it says that the file could not be found, make sure you are connected to the server where the file resides.

Something wrong with the connection string? For testing purposes, I have the MsgBox show to see if the method works.

For Each varFile In .SelectedItems
If InStr(varFile, "PTS-") = 0 Then
' Add the file name to the list of unavailable files
If errList = "" Then
errList = varFile
Else
errList = errList & vbCrLf & varFile
End If
Else
compStatus = Replace(varFile, "_Software.txt", "")
compStatus = Mid(compStatus, InStr(compStatus, "PTS-"))
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source='" & varFile & "';" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited(\t)"";"
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM [" & varFile & "] ", conn
MsgBox rst.GetString
'FixTable resizes the columns
FixTable compStatus
' Add the file name to the list of completed files
If compList = "" Then
compList = varFile
Else
compList = compList & vbCrLf & varFile
End If
End If
Next
.SelectedItems are the items selected by the FileDialog
Also, I wasn't sure how to specify Tab delimited, so I guessed and put "\t" to see if the test would work, but the file not found error comes up instead.

Edit: I found out to specify tab delimited you use FMT=TabDelimited... but still no luck on the file not found error.

[CreganTur]

You're getting the file not found error because you are sending the wrong paramters to both the connection and recordset objects.

You can only give the connection the filepath to the folder where your file resides, and the recordset can only be given the name of the file itself (with file extension).

[Touni102]

Oops... i should have paid closer attention... I fixed the problem and it works fine. Thanks so much!

[Touni102]

Hey sorry to bring this back up, everything works except i noticed the delimiting isn't working correctly.
In the text file the columns are Tab delimited, but when reading in the text file with FMT=TabDelimited, the data for each row is counted as one column... totally ignoring tabs:
This should work, right? What's wrong?

[CreganTur]

According to this (http://www.pcreview.co.uk/forums/thread-1245772.php), you'll have to setup a schema.ini file.

Didn't realize tab delimited required that:dunno

[Touni102]

Thanks, It works like a charm now.