[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


Reading a text / CSV file

Posted on 2004-10-06
Medium Priority
Last Modified: 2008-03-03

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. )
Question by:ScottParker
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
LVL 76

Expert Comment

ID: 12238399
The file extension does not match those listed in the connection string

Expert Comment

ID: 12238427
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)


Author Comment

ID: 12238765
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.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 76

Expert Comment

ID: 12238830
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.


Author Comment

ID: 12238912
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.

Expert Comment

ID: 12238917
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

Author Comment

ID: 12239024

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.

Expert Comment

ID: 12239152
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 ...
LVL 76

Accepted Solution

GrahamSkan earned 2000 total points
ID: 12239180
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

Author Comment

ID: 12239898
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.
LVL 76

Expert Comment

ID: 12239971
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.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question