Solved

Reading a text / CSV file

Posted on 2004-10-06
11
455 Views
Last Modified: 2008-03-03
Greetings.

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.
     Next
     rsCSVFile.MoveNext
Loop
rsCSVFile.Close
Set rsCSVFile = Nothing
CSVDataConn.Close
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. )
0
Comment
Question by:ScottParker
  • 4
  • 4
  • 3
11 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 12238399
The file extension does not match those listed in the connection string
0
 
LVL 5

Expert Comment

by:gary_j
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)
loop

0
 
LVL 3

Author Comment

by:ScottParker
ID: 12238765
GrahamSkan.
I tried changing the ext on the file to .csv and got the same resulting error message.

gary_j
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.

http://beta.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21036026.html
0
 
LVL 76

Expert Comment

by:GrahamSkan
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.

0
 
LVL 3

Author Comment

by:ScottParker
ID: 12238912
GrahamSkan
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.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 5

Expert Comment

by:gary_j
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
0
 
LVL 3

Author Comment

by:ScottParker
ID: 12239024
gary_j

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.
0
 
LVL 5

Expert Comment

by:gary_j
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 ...
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 12239180
Scott,
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
0
 
LVL 3

Author Comment

by:ScottParker
ID: 12239898
GrahamSkan,
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now