parsing imported csv

[nicktred]

Hi

Can anyone help im tying to remove the first three lines of an imported csv before i add it to the database.

eg

name :fred
date : 02/05/2003
ref: 1052355

then the fields that i want

i need to remove the top three lines automatically bofore the data is added to the table

thanks in advance

Nick

 

[alr0]

Hi Nick,

One way to do this is to use the FileSystemObject and the SkipLine command for the fields you do not want.

How are you importing the file now?

al

 

[nicktred]

At the moment im reading in the file and then using

DoCmd.TransferText acImportDelim, "matchdataimportspec", "match_data", strInputFileName, hasfieldnames - 0

to add it into the table.

Nick

 

[alr0]

Hi Nick,

The only way I know how to accomplish this is with the following objects.

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim tso As TextStream
Set tso = fspenTextFile(FilePathName)

tso.SkipLine

If you have never used these before there is a treatment in Getz, Litwin series for Access 2k and most of the commands are available in help but you need to go to several different sections to get them all.

If you try this and have trouble, let me know, I went through it myself very recently.

BOL,

al

 

[nicktred]

What would be the best method to integrate this with my import statement?

Nick

 

[alr0]

This would replace your current import statement and permit you to read a line at a time in VBA to import the file.

It may be to much extra work if there is another way to skip the lines you don't need but I don't know of another way.

al

 

[nicktred]

Ok thanks a lot ill give it a try

Nick

 

[RoyVidar]

Hi!

Here's a little sample using filesystemobject to open textfile, skip three lines, and append all the rest to another file. This requires that you check the Scripting Runtime library (in any module, Tools | References)

[tt] Dim fs As FileSystemObject
Set fs = New FileSystemObject
Dim txtInn As TextStream
Dim txtOut As TextStream
Dim lCounter As Long
Dim sText As String
Set txtInn = fs.OpenTextFile("c:\test.txt", ForReading)
Set txtOut = fs.CreateTextFile("c:\test_2.txt", True)
Do While Not txtInn.AtEndOfStream
lCounter = lCounter + 1
sText = txtInn.ReadLine
If lCounter > 3 Then
txtOut.WriteLine sText
End If
Loop
txtInn.Close
txtOut.Close
Set txtInn = Nothing
Set txtOut = Nothing
Set fs = Nothing[/tt]

With some modifications, you could probably use this prior to your transfertext, just modifying the path thingies. This might ease up a bit on the work...

HTH Roy-Vidar

 

[nicktred]

Thanks alot gave it a try and it does pretty much what a wanted needs a bit of tweeking but its nearly there.

Thanks again

Nick