Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Reading Data from nonstandard CSV file

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a bad CSV file with rows of data that have varying numbers of elements in them. I want to read the values into arrays depending on the first value in a line. Here is an example:

    "Joe", "Miller", "$545.67", "CA"
    "", "00-6500", "$342.34"
    "Mary", "Jones", "$778.67", "MA"
    "$342.21", "$564.00", "$980.00", "432", "1995"

    I'd like to be able to go through this file and only read the lines of data that start with nothing ("") or a number. I can do the testing to see if the values are what I'm looking for, my question is: how do I skip to the next line after I figure out that I don't want the data in the current line?

    I'd the above data to be printed as (in excel with the spaces denoting cell seperation):
    00-6500 $342.34
    $342.21 $564.00 $980.00 432 1995

    Thanks for your help!

    Dave

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Run this routine to open the damaged file and save the data you want to a new file.

    Two dialogs will appear.
    The first will be for the file you are wanting to open. The second for the new file.

    Let me know if the filtering depth is adequate or not.

    Thanks,
    Tom

    Sub CleanFile()
    Dim FileToOpen
    Dim FileToSave
    Dim FileNum(2) As Integer
    Dim ThisRow

    FileToOpen = Application.GetOpenFilename
    If FileToOpen = False Then Exit Sub
    FileToSave = Application.GetSaveAsFilename
    If FileToSave = False Then Exit Sub

    FileNum(1) = FreeFile
    Open FileToOpen For Input As #FileNum(1)
    FileNum(2) = FreeFile
    Open FileToSave For Output As #FileNum(2)
    Do Until EOF(FileNum(1))
    Line Input #FileNum(1), ThisRow
    Debug.Print ThisRow

    If IsNumeric(Mid(ThisRow, 2, 1)) Or _
    IsNumeric(Mid(ThisRow, 3, 1)) Or _
    Mid(ThisRow, 2, 1) = """" Then _
    Print #FileNum(2), ThisRow
    Loop
    Close #FileNum(1)
    Close #FileNum(2)
    End Sub


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks TsTom! I figured it was probably best to use a line input statement and then test the text string. I guess that's what I'll do. Thanks for sharing your code!

    Dave

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, so I'm having another problem. For some reason, when I write a full line to a file it adds a bunch of quotes. So when I read from the file, I can't seperate the values correctly. For example:

    I read the whole line into a variable:
    line input #1, strFullLine
    Here's a value from the initial file I get data from:
    "","60-1111-0000-000","$28.84"
    I test the data and if I want it, I print the line to an output file:
    Print #2, strFullLine
    In the new file it becomes:
    ","60-1111-0000-000","$28.84"
    I tried using
    Write #2, strFullLine
    And with that syntax it becomes:
    """,""60-1111-0000-000"",""$28.84"""

    Is there anyway to solve this? I've tried using Instr() to find single quotations(") but since they're special characters you can't just search for one.

    Thanks for the advice,

    Dave

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, I solved the problem. Thanks for listening! My problem was the difference between the PRINT keyword and the WRITE keyword. I tried to get rid of some qoutes by using a mid statement because I was using WRITE instead of PRINT. So when I switched to PRINT (upon further looking at TsTom's code), I kept the mid statement but I no longer needed it.

    My question now becomes what's the difference between Print and Write?

    Dave

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •