How to handle empty CSV source

[znet]

Hi All,

My DTS package reads data from a csv file and populates it into the database.

Now, if the csv file is empty, I get an error.
What I need is, to check whether the file not contain any records.

I know if import txt file, I can use AX Script as

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(FileName)
If f.Size = 0 Then

But how to check the size of empty CSV. It seem its size is 1KB, but I am not sure.
Any idea.

Thanks advance

 

[unclerico]

My question is what you want to be done if the file is empty. If you want the step to fail then do something like this:

Function Main()
  On Error Resume Next

  Dim fso
  Dim f
  Dim line

  Set fso = CreateObject("Scripting.FileSystemObject")
  Set f = fso.OpenTextFile("filename.csv",1,false,0)

  If f.AtEndOfStream = True Then
    Main = DTSTaskExecResult_Failure
  Else
    Do Until f.AtEndOfStream = True
      line = f.ReadLine
      'msgbox line
    Loop
    
    If Err.Count = 0 Then
      Main = DTSTaskExecResult_Success
    Else
      Main = DTSTaskExecResult_Failure
    End If
  End If

  Set fso = Nothing
  Set f = Nothing
End Function

If you don't want anything to be done you can just do something like this:

On Error Resume Next

  Dim fso
  Dim f
  Dim line

  Set fso = CreateObject("Scripting.FileSystemObject")
  Set f = fso.OpenTextFile("filename.csv",1,false,0)

  Do Until f.AtEndOfStream = True
    line = f.ReadLine
    'msgbox line
  Loop
    
  If Err.Count = 0 Then
    Main = DTSTaskExecResult_Success
     
  Else
    Main = DTSTaskExecResult_Failure
  End If

  Set fso = Nothing
  Set f = Nothing
End Function

The first one will throw back a failure result if the file is empty, the second one will just simply keep going and only if there is some other error will a failure be thrown. Make sense??

 

[znet]

Thanks unclerico,

It works fine.

 

[znet]

Hi unclerico,
If the source file is empty EXCEL file, how to handle? Your solution example seem not work for excel file.
The value of variable: line = f.ReadLine is not blank when read content of empty excel file. Wait for your reply. Thanks in advance.

 

[unclerico]

it will not work for excel files, only text files or variations of them. Go to forum329 and ask the VBScript gurus how to do this sort of task. Sorry I couldn't be of more help.