Read Specific Value In .csv File

[Ostranderbrown1]

Hi,
I am relatively new to this stuff, so please bear with me. I need to search a .csv file for a specific value (a stored variable), and then pull another value out of a different column on the same row. ... wow tough to explain. Let me try an example.

This is a sample of a .csv file.
"98650042","1","JTH004","DQCR721","IM68", (etc. etc.)

I have a set variable for DQCR721 in my script.

dqNumber = DQCR721

I need to open the .csv file, search for DQCR721, and then set two other variables:

soNumber = 98650042
setNumber = 1

I've searched and searched for examples, but can't find anything that works quite right. I've read two things, one about creating a Database object, which I don't know how to do, and the second is my sample below.

Const ForReading = 1

'Set objRegEx = CreateObject("VBScript.RegExp")
'objRegEx.Pattern = ""

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("F:\Am\EXCEL2.CSV", ForReading)

Do Until objFile.AtEndOfStream
strSearchString = objFile.ReadLine
str = InStr(strSearchString, "98650042")
If str <> 0 Then
msgbox(str)


'Set colMatches = objRegEx.Execute(strSearchString)
' If colMatches.Count > 0 Then
' msgbox(strSearchString)
' End If
Loop

objFile.Close

You can see I've tried various things, but I've not been able to figure it out.
Can someone help point me in the right direction?

Thank-you,
Christopher

[MarkT]

I’m making a few assumptions here.

1. The number you are looking for is in the 4th field in the line
2. soNumber is the first field of the line
3. setNumber will be the second field of the line

Option Explicit
Dim dqNumber, soNumber, setNumber
Dim fso, ts 
Dim strSource, strLine, strValues
Const ForReading = 1

	'Create the file system object
	Set fso = CreateObject("Scripting.FileSystemObject")
	
	'Initialize a few items
	strSource = "F:\Am\EXCEL2.CSV"
	dqNumber = "DQCR721"
	
	'Open the source file to read it
	Set ts = fso.OpenTextFile(strSource,ForReading)
	
	'Read the file line by line
	Do while not ts.AtEndOfStream
		strLine = ts.ReadLine
		'Remove the quotes from the string
		strLine = Replace(strLine, Chr(34), "")
		'Split the line on the comma into an array
		strValues = Split(strLine, ",")
		'Check if the dq number matches
		If strValues(3) = dqNumber Then
			'Get the other values you need
			soNumber = strValues(0)
			setNumber = strValues(1)
			'Exit the loop
			Exit Do
		End If
	Loop
	
	'Close the file
	ts.Close
	'Clean up
	Set ts = Nothing
	Set fso = Nothing
	
	MsgBox "soNumber = " & soNumber & vbcrlf & _
			"setNumber = " & setNumber

[Ostranderbrown1]

Thank-you very much for your help. I had to tweak it a little bit, but you gave me to concept which is what I was looking for.

~Christopher

[Ostranderbrown1]

I think I spoke too soon. It works ... almost. The sample .csv values I gave you were just that, samples. Here is a sample of the "real" data. The flaw in the code I discovered, is that there are commas in some of the other fields, and so to constantly refer to one field doesn't work. Is there another way to "delimit" the file? I would think that a series of "if statements" might work, if I have an approximate range as to where the field might be, but i keep getting an error that says "Subscript out of Range '[Number 21]' -2146828279"



"98650290","AP"," 1","",1,"REY003","Reynolds & Reynolds","UPS-DROP","IMREY003-301",1,500,"PREM BW","IMREY003-301",""Weston Kia" Business Card Shells/ FLAT IMPRINTS","BLK","-","-","-","DQCR843","IM68","00","Gary Clark",""Weston Kia" Business Card Shells/ FLAT IMPRINTS","2"," 486","UPS","GNDCOM",500,"","",0,"-","-","","","F","R","F","F","R","R","F","F"

[dilettante]

You are pretty much doomed here.

I don't know how this data was created (my guess from the names of things above is an Excel export) but you have some problems. I did not see commas within field values, but I did see quotation marks. This is a problem the file uses the default text delimiter of " as well.

A "smart" exporter will see this conflict and double up the "s in values, i.e.:

"Weston Kia" Business Card Shells/ FLAT IMPRINTS

becomes:

"""Weston Kia"" Business Card Shells/ FLAT IMPRINTS"

Of course even processing that means you need a smart importer too.

The usual fix is to have the exporter use an alternative field delimiter (e.g. Tab instead of comma) and specify no text delimiter. But you may not have control over the source of this data.

[MarkT]

The 2 options that I see for you is

1. Automate Excel and open the csv file and then read through the cells
2. Create your on split function

Here is an example of the second one. Here I'm spliting the string on the comma but I'm ignoring them if they are inside a field with quotes around it. The rest is the same code as before.

Option Explicit
Dim dqNumber, soNumber, setNumber
Dim fso, ts 
Dim strSource, strLine, strValues()
Const ForReading = 1

	'Create the file system object
	Set fso = CreateObject("Scripting.FileSystemObject")
	
	'Initialize a few items
	strSource = "F:\Am\EXCEL2.CSV"
	strSource = "C:\Documents and Settings\Mark\Desktop\test.CSV"
	dqNumber = "DQCR721"
	
	'Open the source file to read it
	Set ts = fso.OpenTextFile(strSource,ForReading)
	
	'Read the file line by line
	Do while not ts.AtEndOfStream
		strLine = ts.ReadLine

		SplitString strLine
		
		If strValues(3) = dqNumber Then
			'Get the other values you need
			soNumber = strValues(0)
			setNumber = strValues(1)
			'Exit the loop
			Exit Do
		End If
	Loop
	
	'Close the file
	ts.Close
	'Clean up
	Set ts = Nothing
	Set fso = Nothing
	
	MsgBox "soNumber = " & soNumber & vbcrlf & _
			"setNumber = " & setNumber
			
Private Function SplitString(Byval sInput)
dim i, index
Dim StartPos, EndPos
Dim blnOpenQuote
Dim sChr
	
	'Remove leading and trailing spaces form the string
	sInput = Trim(sInput)
	
	'loop through each character
	For i = 1 To Len(sInput)
		sChr = Mid(sInput,i,1)
		
		Select Case sChr
			Case Chr(34)' "
				If i = 1 Then
					'Check if the first field is enclosed in quotes
					blnOpenQuote = True
					StartPos = 2
				ElseIf blnOpenQuote Then
					'If you have an open qoute and reach another qoute
					'check the next character to see if it is a comma.
					'If it is you have the end of a field
					If Mid(sInput, i + 1, 1) = "," Then
						blnOpenQuote = False
					End if
				ElseIf Not blnOpenQuote Then
					'If you reach a quote and you don't have an open quote
					'already then you found the start of the field.
					blnOpenQuote = True
					StartPos = i + 1				
				End If
			Case Chr(44) ' ,
				'If you reach a comma and don't have an open qoute then
				'you have found the end of the field
				If Not blnOpenQuote  Then
						EndPos = i - 1
				End If
		End Select
		
		If i = Len(sInput) Then 
			'The end of the last field will be at the end of the document
			EndPos = i
		End If
		
		'Make sure you have found a field
		If EndPos >= StartPos  And (Not blnOpenQuote Or i = Len(sInput)) Then
			'Recreate the array
			Redim Preserve strValues(index)
			'Add the field value to the array
			strValues(index) = Mid(sInput, StartPos, EndPos - StartPos)
			'Get the index value ready for the next array item
			index = index + 1
			'Reset the start and end position for the next loop
			StartPos = i + 1
			EndPos = 0
		End If	
	Next
End Function

[Ostranderbrown1]

This may not be "proper" programming, but this is what I did, and this is what works. I can guess the approximate range that my number is going to be in, so this is what I did.

ForReading1 = 1

'Create the file system object
Set fso = CreateObject("Scripting.FileSystemObject")

'Initialize a few items
strSource = "F:\Am\EXCEL.CSV"

'Open the source file to read it
Set ts = fso.OpenTextFile(strSource,ForReading1)

'Read the file line by line
Do while not ts.AtEndOfStream
strLine = ts.ReadLine
'Remove the quotes from the string
strLine = Replace(strLine, Chr(34), "")
'Split the line on the comma into an array
strValues = Split(strLine, ",")
'Check if the dq number matches

If strValues(17) = dqNumber Then
'Get the other values you need
soNumber = strValues(0)
setNumber = strValues(2)
sideNumber = strValues(4)
'Exit the loop
Exit Do
End If
If strValues(18) = dqNumber Then
'Get the other values you need
soNumber = strValues(0)
setNumber = strValues(2)
sideNumber = strValues(4)
'Exit the loop
Exit Do
End If
If strValues(19) = dqNumber Then
'Get the other values you need
soNumber = strValues(0)
setNumber = strValues(2)
sideNumber = strValues(4)
'Exit the loop
Exit Do
End If
If strValues(20) = dqNumber Then
'Get the other values you need
soNumber = strValues(0)
setNumber = strValues(2)
sideNumber = strValues(4)
'Exit the loop
Exit Do
End If
If strValues(21) = dqNumber Then
'Get the other values you need
soNumber = strValues(0)
setNumber = strValues(2)
sideNumber = strValues(4)
'Exit the loop
Exit Do
End If
If strValues(22) = dqNumber Then
'Get the other values you need
soNumber = strValues(0)
setNumber = strValues(2)
sideNumber = strValues(4)
'Exit the loop
Exit Do
End If
If strValues(23) = dqNumber Then
'Get the other values you need
soNumber = strValues(0)
setNumber = strValues(2)
sideNumber = strValues(4)
'Exit the loop
Exit Do
End If
If strValues(24) = dqNumber Then
'Get the other values you need
soNumber = strValues(0)
setNumber = strValues(2)
sideNumber = strValues(4)
'Exit the loop
Exit Do
End If
Loop

'Close the file
ts.Close

It seems to work if I move the number into the various fields on my original file.

[MarkT]

If you just want to pick through the array then this may be cleaner

ForReading1 = 1
Dim i, blnFound

'Create the file system object
Set fso = CreateObject("Scripting.FileSystemObject")

'Initialize a few items
strSource = "F:\Am\EXCEL.CSV"

'Open the source file to read it
Set ts = fso.OpenTextFile(strSource,ForReading1)

'Read the file line by line
Do while not ts.AtEndOfStream
	strLine = ts.ReadLine

	'Remove the quotes from the string	
	strLine = Replace(strLine, Chr(34), "")

	'Split the line on the comma into an array
	strValues = Split(strLine, ",")

	'Check if the dq number matches	
	For i = 17 to 24
		If strValues(i) = dqNumber Then
			'Get the other values you need
			soNumber = strValues(0)
			setNumber = strValues(2)
			sideNumber = strValues(4)
			'Set flag that say the value was found
			blnFound = True
			'Exit the loop
			Exit For
		End If
	Next
	
	If blnFound then Exit Do
Loop

'Close the file
ts.Close

[Ostranderbrown1]

I'll try that.

Thank-you. Trust me, I am going to have a lot of code to "clean up". I have no formal training, just picking apart other scripts and learning where I can.

Thanks for the help, I appreciate it.

[Ostranderbrown1]

I am cleaning up some of this code, and put in your suggestion (works great), but I am stumbling on something. I want to put up a message box if the value was not found, and search for my next number. I can't figure out how to do it.

The entire project is something like this: (This Code Doesn't Do What I Want It To)

for each file in folder1.files
 
    if (Left(file.Name,Len(file.Name) - InStr(file.Name,".ci"))) = "DQ" then
 
        cidFileName = file.Name
        dqNumber = replace(file.name,".ci","")
 
        ForReading1 = 1
 
        'Create the file system object
        Set fso = CreateObject("Scripting.FileSystemObject")
    
        'Initialize a few items
        strSource = "F:\Am\EXCEL.CSV"
        
        'Open the source file to read it
        Set ts = fso.OpenTextFile(strSource,ForReading1)
    
        'Read the file line by line
        Do while not ts.AtEndOfStream
            strLine = ts.ReadLine
            'Remove the quotes from the string
            strLine = Replace(strLine, Chr(34), "")
            'Split the line on the comma into an array
            strValues = Split(strLine, ",")
            'Check if the dq number matches
 
            For i = 17 to 24
    If strValues(i) = dqNumber Then
        'Get the other values you need
        soNumber = strValues(0)
        setNumber = strValues(2)
        sideNumber = strValues(4)
        Exit Do
                Else
                    Msgbox(dqNumber& " was not entered. It will not be processed.")
                    Exit Do
                End If
                
            Next
 
        Loop
 
 
'Close the file
ts.Close
 
'Perform Some Actions 
 
Next