Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Open and write to a csv file using vba

Posted on 2012-08-16
6
Medium Priority
?
722 Views
Last Modified: 2012-08-17
I need to do something that seems very simple but I can't seem to find the answer. I have a small csv file with about 8 columns (filename ibm.csv) .

Using vba code I need to open it, put a Y flag to the right of the last column if the last column equals something I pass in to the function and then save the file. Can anyone tell me how to do this?

Thanks in advance!
0
Comment
Question by:boukaka
  • 3
  • 2
6 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38301769
csv file is simple text file, you can open it, read, split into columns and write to a new file. If you upload some sample file, we can help with code.
0
 
LVL 40

Expert Comment

by:als315
ID: 38301874
You can try this sub (new file will be saved with "_out" in the name (ibm_out.csv)
Sub read_f(filen As String)
' filen - full path to file (c:\tmp\ibm.csv)
Dim Str As String, FileIN As Integer, FileO As Integer
Dim Arr() As String
FileIN = FreeFile
Open filen For Input As #FileIN
FileO = FreeFile
Open Left(filen, Len(filen) - 4) & "_out.csv" For Output As #FileO

Do While Not EOF(FileIN)
    Line Input #FileIN, Str
    Arr = Split(Str, ";")
    If Arr(7) = "OK" Then Str = Str & ";Y"
    Print #FileO, Str
Loop
Close #FileIN
Close #FileO
End Sub

Open in new window

0
 

Author Comment

by:boukaka
ID: 38302041
I need to read and write back to the same file - not a different one...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Accepted Solution

by:
nathaniel earned 2000 total points
ID: 38303560
you may want to follow this approach:

'to read text from ibm.csv and save it to tblibm table (this assumes that ibm table is already created.
DoCmd.TransferText acImportDelim, , "tblibm", "ibm.csv"

'use the update SQL script to update a field based on the conditions you set
DoCmd.RunSQL "Update SQL statement to put a Y flag to the last column or anywhere"

'then create a ibm.csv file based on the content of table tblibm (this includes the updated data)
DoCmd.TransferText acExportDelim, , "tblibm", "ibm.csv"
0
 
LVL 40

Expert Comment

by:als315
ID: 38303671
You can't read and write in the same file in the same time. You can rename first file, read, write to the same name and delete first. Is it acceptable?
Sub read_f()
Dim filen As String
Dim Str As String, FileIN As Integer, FileO As Integer
Dim Arr() As String, fil As String
filen = "c:\tmp\ibm.csv"
FileIN = FreeFile
fil = Left(filen, Len(filen) - 4) & "_out.csv"
Name filen As fil
Open fil For Input As #FileIN

FileO = FreeFile
Open filen For Output As #FileO
Do While Not EOF(FileIN)
    Line Input #FileIN, Str
    Arr = Split(Str, ";")
    If Nz(Arr(7), "") = "OK" Then Str = Str & ";Y"
    Print #FileO, Str
Loop
Close #FileIN
Close #FileO
Kill fil
End Sub

Open in new window

0
 

Author Closing Comment

by:boukaka
ID: 38304496
Thank you - this will work for me.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

571 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