locked
Test for blank contents for text file before importing RRS feed

  • Question

  • I have the following, rather lengthy segment of code that identifies from a list of files which ones are CSV files and then uploads them in a very efficient way without having to open them, get the data and close them. I love it. BUT...lately I have a problem where one of the CSV files will contain only headers (no data). The code does not know that and tries to import, causing an error. So now I have a paradoxical situation. I like the code because it does not examine the contents. It just gets the data and imports it to the worksheet, but I need to ignore the file if it has no data. 

    Any help on that? You can also feel free to recommend how to trim down this code. 

    Thanx,

    tod

    Friday, September 30, 2016 1:29 PM

Answers

  • To know if it has any data you have to open it which you do not want.

    In that case I think best thing is to insert an "On Error Resume Next" at top of the code.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    • Marked as answer by todtown Monday, October 3, 2016 3:29 PM
    Saturday, October 1, 2016 8:13 AM
    Answerer

All replies

  • You did not post the code. Guess it uses some SQL.

    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Friday, September 30, 2016 3:57 PM
    Answerer
  • Oops. Dang it!

    Here's the code:

    'Iterating through a list of file names using FSO. For each fsofile in fsofolder.files

                    If Left(fsofile.Name, 5) = "Agent" Then

                        strConnection = "TEXT;" & fsofile.Path

                        With AgentSheet.QueryTables.Add(Connection:= _

                            strConnection, Destination:=AgentSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0))

                            .Name = "temp"

                            .FieldNames = True

                            .RowNumbers = False

                            .FillAdjacentFormulas = False

                            .PreserveFormatting = True

                            .RefreshOnFileOpen = False

                            .RefreshStyle = xlInsertDeleteCells

                            .SavePassword = False

                            .SaveData = True

                            .AdjustColumnWidth = True

                            .RefreshPeriod = 0

                            .TextFilePromptOnRefresh = False

                            .TextFilePlatform = 437

                            .TextFileStartRow = 2

                            .TextFileParseType = xlDelimited

                            .TextFileTextQualifier = xlTextQualifierDoubleQuote

                            .TextFileConsecutiveDelimiter = False

                            .TextFileTabDelimiter = False

                            .TextFileSemicolonDelimiter = False

                            .TextFileCommaDelimiter = True

                            .TextFileSpaceDelimiter = False

                            .TextFileColumnDataTypes = Array(1)

                            .TextFileTrailingMinusNumbers = True

                            .Refresh BackgroundQuery:=False

                        End With

                        GoTo NextFile

                    End If

    Friday, September 30, 2016 4:02 PM
  • To know if it has any data you have to open it which you do not want.

    In that case I think best thing is to insert an "On Error Resume Next" at top of the code.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    • Marked as answer by todtown Monday, October 3, 2016 3:29 PM
    Saturday, October 1, 2016 8:13 AM
    Answerer
  • Okay. That's what I was thinking. Just wanted to see if there was an alternative. Thanx. 
    Monday, October 3, 2016 3:29 PM