Reading a text / CSV file


I "Borrowed" the following code from a post made by TMacT

'=== start of code
Private Sub Command1_Click()
' ***************************************************
Dim CSVDataConn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim sFld As ADODB.Field
Dim rsCSVFile As New ADODB.Recordset
Dim sFilePath As String

sFilePath = "\\ma31\TandE\Data\"  ' You do not need a file name yet.

'If no HEADER then ...
CSVDataConn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & sFilePath & ";Extensions=asc,csv,tab,txt;HDR=NO;Persist Security Info=False"

'Now you pass the file name of the CSV file.
rsCSVFile.Open "Select * From 08180401.cgl", CSVDataConn, adOpenStatic, adLockReadOnly, adCmdText
Do While Not rsCSVFile.EOF
     For Each sFld In rsCSVFile.Fields
          Debug.Print sFld.Value 'Do what you need to capture the data in the fields here.
Set rsCSVFile = Nothing
Set CSVDataConn = Nothing
' ***************************************************
'OpenCsvFile "08180401.cgl"
End Sub
'=== end of code

First let me say that he had
Dim sFld As New ADODB.Field
and I leave that "New" in there I get an "Invalid use of New" error.  Taking out the "New" seems to work though.

Now on the rsCSVFile.Open  I am getting an error saying "Cannot update. Database or object is read-only"
I know the file itself does not have the "read only" attribute checked in its properties.
Actually, I don't want to "Update" the file anyway.  I am just trying to read it in so I can transfer it to our AS/400.

I have also tried some of the other code displayed in various questions to do this but always seem to end up getting stuck on that same line with that same error when opening.

I know I do have FULL security access to the file.  Even when I put it on my local hard-drive it still comes back with the same error.   I have also tried to change the ext to .csv.  

I know the answer is probably simple, but I need this answered asap so I can move on to other aspects of this development.

(The good news is that after next month I will no longer be a "Self Taught" VB (wannabe) programer. My company is sending me to some "Real" VB.Net Training. Its only a 5 day course but hey its better then nothing. )
Who is Participating?
GrahamSkanConnect With a Mentor RetiredCommented:
I created a two-line file called abc.csv from your posting and it works OK. If I rename it to abc.cgl, it gives the error that you report.
Cheers, Graham
The file extension does not match those listed in the connection string
i'm confused; why are you using an ado connection to read a text file?

reading a text file is as simple as

open "c:\yourpath\yourfile" for input as #1

do while not eof(1)
     input #1, strIn  (or Line Input #1)

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

ScottParkerAuthor Commented:
I tried changing the ext on the file to .csv and got the same resulting error message.

The reason I am useing an ADO Connection is because it was said it would work. That I wouldn't have to deal with all the "String" functions of takeing out each field of the .csv.
Here is the tread I got the code from.  He explains it better then I could.
Are you saying that your file is a CSV with lines like this?

"John Smith", "London", 25
"Annnabel Jones", "Edinburgh", 20

The code that you are using was intended to treat a csv (Comma separated values) as a Recordset, i.e. a series of data fields. If your file isn't of the CSV format, you will get the error.

However,it's not clear that you want to read it as a series of text lines, either.

This will read it into a single string variable

Dim strBuffer as string
Dim f as integer
f = freefile
Open "myfile" For Binary As #f
strBuffer  = Input(LOF(f), f)

What are you doing to save the file to the mainframe? That could have a bearing on the best way to go.

ScottParkerAuthor Commented:
yes the .cgl file is in CSV format.  here is some test data in it.
"","08/21/04","A Company",45.00,"        1002","1002","9","","","","",0,0,0,0
"","082104","B Company",284.00,"        1002","1002","81030","","","","",0,0,0,0

And what I wanted to do to get the records to the AS/400 is to just open up a file I created useing another connection.
The records that get written depend on values in certian fields in the csv.
This part I have done before so I know how to do it.
seems to me that if you use line input

(The Line Input # statement reads from a file one character at a time until it encounters a carriage return (Chr(13)) or carriage return–linefeed (Chr(13) + Chr(10)) sequence. Carriage return–linefeed sequences are skipped rather than appended to the character string. -- from vb help)

then the split function

arrayString = split(strInput,",")

it would be far easier
ScottParkerAuthor Commented:

If I could ensure that there would not be any carriage return's or linefeeds in a record then yes that would probably work fine.
My problem though is I have no control over the csv file. It is sent to me by another company.  I do know that one of the fields in the file comes from a "Memo" type field.  Users can enter any text they want.  And like most "Memo" style fields the application does let them hit "Enter" to go to the next line in the memo field text box.  (if that makes sense).  I have no control over the application thats used to "Enter" this data so I can not change that.

I need to make sure this data can not be "corrupted" because it ends up going into our G.L.

If I cant get the ado method to work though I will have to do as you suggested.
probably the binary method mentioned by GrahamSkan is a better option, but it's the same general theory ...

If you know the exact field count every time, then there are other things you can do to watch for the "embeded" (valid) commas as opposed to the field separator commas ...
ScottParkerAuthor Commented:
ACKKK   I knew it was something simple.

I had changed the extention on the test file but forgot to change it in the following line of code.
rsCSVFile.Open "Select * From 08180401.cgl"  

Thanks for looking at it.
Great. Now you're cooking. Thanks for the grade.
When you've had your VB.NET course, I guess you'll be posting in that area, so good luck there.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.