Skip first row

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I'm importing a CSV file into access using VBA code, now I want to skip the
first row which contains the field names, and start importing from the
second row all the way down till the EOF, how do I do that. ?

Thanks,

Scott
 
use vba to delete the line
post back if you need the code. I'm feeling too lazy to write it today.
HS
 
Create an Import Specification by using the manual Import
interface and save the Spec. You can specify the saved
Import Spec in your TransferText statement.

HTH
Van T. Dinh
MVP (Access)
 
Van,
ImportExport Specifications do not allow you to skip rows. - atleast as far
as I know.
There is certainly nothing in the UI where you can specify this.
this feature is available in DTS. Are you thinking of that, perhaps?

HS
 
If you could please send me the code I would appreciate it.

Here is the code I'm using:

*********

Dim strLine1 As String
Dim strLine2 As String
Dim strLine3 As String
Dim strLine4 As String
Dim strLine5 As String
Dim strLine6 As String
Dim strLine7 As String
Dim strLine8 As String
Dim strLine9 As String
Dim strLine10 As String
Dim strLine11 As String
Dim strLine12 As String
Dim strLine13 As String
Dim strLine14 As String
Dim strLine15 As String

Dim rst As DAO.Recordset
Dim FilesDir As String

Set rst = CurrentDb.OpenRecordset("MyTable")

FilesDir = Dir(Me.FilePath)
Do While FilesDir <> ""
Open Me.FilePath & FilesDir For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Loop until end of file.
Select Case gblSpec

Case 7
Input #1, strLine1, strLine2, strLine3, strLine4, strLine5,
strLine6, strLine7, strLine8, strLine9, strLine10, strLine11, strLine12,
strLine13, strLine14, strLine15
rst.AddNew
rst!MyField1 = Trim(strLine13)
rst!MyField2 = YYYYMMDD_To_Date(Left(strLine2, 8))
rst!MyField3 = Trim(strLine5)
rst!MyField4 = Trim(strLine6)
rst!MyField5 = gblMyVariable .
rst!MyField6 = Date .
rst.Update

End Select

Loop
Close #1

***************************************************

The above code was working good when I had no field header, but now I need
to start importing from the second line that contains the actual data and
not the first line, how do I do that.?

Thanks,

Scott
 
Set up a variable to count the rows as you loop through
them during the import.

If MyCount = 1 then
'do nothing
Else
'do something
End if
 
Scott,
You can use a schema.ini to skip the rows.
I'll post the code to do delete the line later today. I've gotta run now
HS
 
Van was right.
Although the import Export Specification does not contain an option to
specify skip rows,
when you use Docmd.transferText, you do have the option of specifying that
the file has field names.

The attached code will help you import data into a specified table using
import-export specifications.

HS

'------Begin Code ----------------------------------------------

Function ImportFile( _
ByVal StrFileName As String, _
ByVal ImportSpec As String, _
ByVal DestinationTable As String, _
Optional ByVal SkipRows As Integer = 0) As StatusCode


' Imports a specified text file to specified table.
' Uses import export specifications
' Optionally Use SkipRows. By default, no rows are skipped
' uses a hardcoded temporary file to copy data -
' change path of temp file below. Can also add it as another parameter


On Error GoTo ImportFile_err

Dim fso, srcFile, DestFile ' Objects
Dim strMsg As String, Currline As String, i As Integer

Const ForReading = 1 'FSO Constants
Const ForWriting = 2

' Use a temporary file for the import operation
Const DestFileName = "C:\temp\Datafile.txt"

' Handle inputs
StrFileName = Trim(StrFileName)


'instantiate file system object. We are using late binding here.
Set fso = CreateObject("Scripting.FileSystemObject")

'if sourceFile doesnt exist, quit now, returning a failure value
If Not fso.FileExists(StrFileName) Then
strMsg = "Sorry, the path and filename you entered is invalid. " &
vbCrLf
strMsg = strMsg & "Please check and reenter"
MsgBox strMsg, vbExclamation + vbOKOnly, "Check File Name"
ImportFile = Failure
Set fso = Nothing
Exit Function
End If


'create a new temp File - previous instances are automatically overwritten
Set DestFile = fso.opentextfile(DestFileName, ForWriting, True)

' open the source file in readonly mode
Set srcFile = fso.opentextfile(StrFileName, ForReading)

While Not srcFile.AtEndOfStream
i = i + 1 ' count lines
If i <= SkipRows Then
' just read the line to get move the pointer
Currline = srcFile.Readline
Else
DestFile.WriteLine (srcFile.Readline)
'Currline = srcFile.Readline
'DestFile.WriteLine Currline
End If
Wend

'close files and cleanup objects
srcFile.Close
DestFile.Close

Set srcFile = Nothing
Set DestFile = Nothing
Set fso = Nothing

' Now import the file
DoCmd.TransferText acImportDelim, ImportSpec, DestinationTable, DestFileName

'Return a success code to calling procedure
ImportFile = Success
Exit Function

ImportFile_err:
' Return failure status and show error desc
MsgBox "Err # " & Err.Number & vbCrLf & Err.Description, vbCritical +
vbOKOnly, "MdlCommon.ImportFile Error!"
ImportFile = Failure
Exit Function


End Function
'----------------End Code----------------------
 
Boy, I'm on a roll with screwups on this answer.
Please ignore the previous message.

Here is the complete and corrected code. You will need to add these two
Enumerations to the declarations section of your module or make necessary
changes.

HS


---------Begin Code ------------

Enum StatusCode
Unknown = -1
Failure = 0
Success = 1
End Enum

Enum ID_ImportType
ImportDelimited = 0
ImportFixedWidth = 1
End Enum

'-----------------------------
Function ImportTextFile( _
ImportType As ID_ImportType, _
ImportSpec As String, _
DestinationTable As String, _
StrFileName As String, _
Optional SkipRows As Integer = 0) As StatusCode

' Imports a delimited or Fixed-width text file to specified table.
' Uses import export specifications
' Optionally Use SkipRows. By default, no rows are skipped
' uses a hardcoded temporary file to copy data -
' change path of temp file below. Can also add it as another parameter

On Error GoTo ImportTextFile_err

Dim fso, srcFile, DestFile ' Objects
Dim strMsg As String, Currline As String, i As Integer

Const ForReading = 1 'FSO Constants
Const ForWriting = 2

' Use a temporary file for the import operation
Const DestFileName = "C:\temp\Datafile.txt"

' Handle inputs
StrFileName = Trim(StrFileName)
DestinationTable = Trim(DestinationTable)

'instantiate file system object. We are using late binding here.
Set fso = CreateObject("Scripting.FileSystemObject")

'if sourceFile doesnt exist, quit now, returning a failure value
If Not fso.FileExists(StrFileName) Then
strMsg = "Sorry, the path and filename you entered is invalid. " &
vbCrLf
strMsg = strMsg & "Please check and reenter"
MsgBox strMsg, vbExclamation + vbOKOnly, "Check File Name"
ImportTextFile = Failure
Set fso = Nothing
Exit Function
End If


'create a new temp File - previous instances are automatically overwritten
Set DestFile = fso.opentextfile(DestFileName, ForWriting, True)

' open the source file in readonly mode
Set srcFile = fso.opentextfile(StrFileName, ForReading)

While Not srcFile.AtEndOfStream
i = i + 1 ' count lines
If i <= SkipRows Then
' just read the line to get move the pointer
Currline = srcFile.Readline
Else
DestFile.WriteLine (srcFile.Readline)
'Currline = srcFile.Readline
'DestFile.WriteLine Currline
End If
Wend

'close files and cleanup objects
srcFile.Close
DestFile.Close

Set srcFile = Nothing
Set DestFile = Nothing
Set fso = Nothing

' Now import the file
DoCmd.TransferText ImportType, ImportSpec, DestinationTable, DestFileName

'Return a success code to calling procedure
ImportTextFile = Success
Exit Function

ImportTextFile_err:
' Return failure status and show error desc
MsgBox "Err # " & Err.Number & vbCrLf & Err.Description, vbCritical +
vbOKOnly, "MdlCommon.ImportTextFile Error!"
ImportTextFile = Failure
Exit Function

End Function
----------End Code--------------
 
Thank You,

Scott


HSalim said:
Boy, I'm on a roll with screwups on this answer.
Please ignore the previous message.

Here is the complete and corrected code. You will need to add these two
Enumerations to the declarations section of your module or make necessary
changes.

HS


---------Begin Code ------------

Enum StatusCode
Unknown = -1
Failure = 0
Success = 1
End Enum

Enum ID_ImportType
ImportDelimited = 0
ImportFixedWidth = 1
End Enum

'-----------------------------
Function ImportTextFile( _
ImportType As ID_ImportType, _
ImportSpec As String, _
DestinationTable As String, _
StrFileName As String, _
Optional SkipRows As Integer = 0) As StatusCode

' Imports a delimited or Fixed-width text file to specified table.
' Uses import export specifications
' Optionally Use SkipRows. By default, no rows are skipped
' uses a hardcoded temporary file to copy data -
' change path of temp file below. Can also add it as another parameter

On Error GoTo ImportTextFile_err

Dim fso, srcFile, DestFile ' Objects
Dim strMsg As String, Currline As String, i As Integer

Const ForReading = 1 'FSO Constants
Const ForWriting = 2

' Use a temporary file for the import operation
Const DestFileName = "C:\temp\Datafile.txt"

' Handle inputs
StrFileName = Trim(StrFileName)
DestinationTable = Trim(DestinationTable)

'instantiate file system object. We are using late binding here.
Set fso = CreateObject("Scripting.FileSystemObject")

'if sourceFile doesnt exist, quit now, returning a failure value
If Not fso.FileExists(StrFileName) Then
strMsg = "Sorry, the path and filename you entered is invalid. " &
vbCrLf
strMsg = strMsg & "Please check and reenter"
MsgBox strMsg, vbExclamation + vbOKOnly, "Check File Name"
ImportTextFile = Failure
Set fso = Nothing
Exit Function
End If


'create a new temp File - previous instances are automatically overwritten
Set DestFile = fso.opentextfile(DestFileName, ForWriting, True)

' open the source file in readonly mode
Set srcFile = fso.opentextfile(StrFileName, ForReading)

While Not srcFile.AtEndOfStream
i = i + 1 ' count lines
If i <= SkipRows Then
' just read the line to get move the pointer
Currline = srcFile.Readline
Else
DestFile.WriteLine (srcFile.Readline)
'Currline = srcFile.Readline
'DestFile.WriteLine Currline
End If
Wend

'close files and cleanup objects
srcFile.Close
DestFile.Close

Set srcFile = Nothing
Set DestFile = Nothing
Set fso = Nothing

' Now import the file
DoCmd.TransferText ImportType, ImportSpec, DestinationTable, DestFileName

'Return a success code to calling procedure
ImportTextFile = Success
Exit Function

ImportTextFile_err:
' Return failure status and show error desc
MsgBox "Err # " & Err.Number & vbCrLf & Err.Description, vbCritical +
vbOKOnly, "MdlCommon.ImportTextFile Error!"
ImportTextFile = Failure
Exit Function

End Function
----------End Code--------------
Click to expand...
 
Back
Top