Reading Data from nonstandard CSV file

davers5

Active Member
Joined
Feb 20, 2002
Messages
255
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top