stltodaycom
asked on
Pulling data from a CSV file
What am I doing wrong in code? I want to pull data from a CSV file. I get an error that says 'Invalid procedure call or argument'.
==========================
Public Function GetDataFromFile(ByVal OrderNumber As String)
Dim f As Integer
Dim strAll As String
Dim strOrder() As String
Dim strField() As String
Dim strOurOrder As String
Dim strTemp As String
'throw the whole file into a string
f = FreeFile
Open "C:\Customers\Superior Advertising\UPSEXP.CSV" For Binary As #f
strAll = Space$(LOF(f))
Get #f, , strAll
Close #f
'trim off everything before the first instance of the order
strAll = Mid$(strAll, InStrRev(strAll, OrderNumber))
GetDataFromFile = Left$(strAll, InStr(strAll, vbCrLf) - 1)
End Function
==========================
If I make a copy of the CSV file and open it in Notepad and change the file extension to .txt - then it works, it pulls data like it should.
All data in each line item is separated by a comma and quotes surround each field.
Example of some of the data:
"B143437","PROMARK SOLUTIONS LLC",".","P.O. BOX 6878","","","JEFFERSON CITY","MO","65102","(573)6
"S143437","LAKEVIEW RESORT","ATTN: JAKE DRAKE","328 LAKEVIEW RESORT BLVD.","","","SUNRISE BEACH","MO","65079",""
"B143456","KAMIST PROMOTIONS INC.",".","P O BOX 740017","","","BOYNTON BEACH","FL","334740017","(
"S143456","DR. BRADLEY D. KAUFMAN","","925 EAST HENRIETTA ROAD","","","ROCHESTER","N
I copied four lines. Each line begins with 'S######' or 'B######'.
Thanks as always!
==========================
Public Function GetDataFromFile(ByVal OrderNumber As String)
Dim f As Integer
Dim strAll As String
Dim strOrder() As String
Dim strField() As String
Dim strOurOrder As String
Dim strTemp As String
'throw the whole file into a string
f = FreeFile
Open "C:\Customers\Superior Advertising\UPSEXP.CSV" For Binary As #f
strAll = Space$(LOF(f))
Get #f, , strAll
Close #f
'trim off everything before the first instance of the order
strAll = Mid$(strAll, InStrRev(strAll, OrderNumber))
GetDataFromFile = Left$(strAll, InStr(strAll, vbCrLf) - 1)
End Function
==========================
If I make a copy of the CSV file and open it in Notepad and change the file extension to .txt - then it works, it pulls data like it should.
All data in each line item is separated by a comma and quotes surround each field.
Example of some of the data:
"B143437","PROMARK SOLUTIONS LLC",".","P.O. BOX 6878","","","JEFFERSON CITY","MO","65102","(573)6
"S143437","LAKEVIEW RESORT","ATTN: JAKE DRAKE","328 LAKEVIEW RESORT BLVD.","","","SUNRISE BEACH","MO","65079",""
"B143456","KAMIST PROMOTIONS INC.",".","P O BOX 740017","","","BOYNTON BEACH","FL","334740017","(
"S143456","DR. BRADLEY D. KAUFMAN","","925 EAST HENRIETTA ROAD","","","ROCHESTER","N
I copied four lines. Each line begins with 'S######' or 'B######'.
Thanks as always!
ASKER
This is the line of code that error's out:
GetDataFromFile = Left$(strAll, InStr(strAll, vbCrLf) - 1)
EDDYHT: I tried your suggestion but I still receive the same error: "invalid procedure call or argument".
GetDataFromFile = Left$(strAll, InStr(strAll, vbCrLf) - 1)
EDDYHT: I tried your suggestion but I still receive the same error: "invalid procedure call or argument".
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what is this
InStr(strAll, vbCrLf)
gives you?
may be used vbCr or VbLf
InStr(strAll, vbCrLf)
gives you?
may be used vbCr or VbLf
There is also this possible solution.
'place this in the General Declarations of a module
Type Rec_struc
OrderNum As String
Name As String
Contact As String
Addr1 As String
Addr2 As String
Addr3 As String
City As String
State As String
Zip As String
Phone As String
End Type
Public Function GetDataFromFile(ByVal OrderNumber As String) As Rec_struc
Dim f As Integer
f = FreeFile
Open "C:\Customers\Superior Advertising\UPSEXP.CSV" For Input As #f
Do Until Eof(#f)
With GetDataFromFile
Input #f, .OrderNum , .Name , .Contact , .Addr1 , .Addr2 , .Addr3 , .City , .State , .Zip , .Phone
If .OrderNum = OrderNumber Then
Exit Do
End If
End With
Loop
Close #f
End Function
'place this in the General Declarations of a module
Type Rec_struc
OrderNum As String
Name As String
Contact As String
Addr1 As String
Addr2 As String
Addr3 As String
City As String
State As String
Zip As String
Phone As String
End Type
Public Function GetDataFromFile(ByVal OrderNumber As String) As Rec_struc
Dim f As Integer
f = FreeFile
Open "C:\Customers\Superior Advertising\UPSEXP.CSV" For Input As #f
Do Until Eof(#f)
With GetDataFromFile
Input #f, .OrderNum , .Name , .Contact , .Addr1 , .Addr2 , .Addr3 , .City , .State , .Zip , .Phone
If .OrderNum = OrderNumber Then
Exit Do
End If
End With
Loop
Close #f
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
GetDataFromFile = Left$(strAll, InStr(strAll, vbCrLf) - 1)
with
GetDataFromFile = """" & Left$(strAll, InStr(strAll, vbCrLf) - 1)