Solved

Reading a text / CSV file

Posted on 2004-10-06
11
456 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

911 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

21 Experts available now in Live!

Get 1:1 Help Now