Reading Data from nonstandard CSV file

[davers5]

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

[Tom Schreiner]

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

[davers5]

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

[davers5]

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

[davers5]

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