Edit cells in csv file via VBA (1 Viewer)

[phinix]

I have a csv file with exported data form Access.
Problem is that I need to add 2 rows on top of the data in that csv.
Simply push down all data I exported in that csv (including headers) and add 2 rows with specific values.

I managed to write a vba to push whole data down by 2 rows, however for a love of god, I cannot edit those top blank rows now

Lets say I have this:

Header1	Header2	Header3
1	A	C
2	B	D

And need to have this:

Something here	Something else
Bla bla bla	bla bla
Header1	Header2	Header3
1	A	C
2	B	D

I managed to make 3 blank rows on top, but how can I edit specific cells?

Ideally, I would "paste" another access query results into those blank rows, which would be one row with headers (so basically adding 2 rows on top of that initial data).

 

[plog]

Ideally, I would "paste" another access query results into those blank rows,

You could use a UNION. Essentially it strings together the results of different data sources. Let's say you have RealQuery that produces your results with field names F1, F2, F3:

F1, F2, F3
1, A, C
2, B, D

For simplicity's sake, let's say you have TableHeaders (this method works for both Tables and Queries). In TableHeaders you have 3 fields: F1, F2, F3. You can add 2 records to it, putting whatever you want in those 3 fields. Then to get RealQuery and TableHeaders together you would do this:

SELECT F1, F2, F3 FROM TableHeaders
UNION ALL
SELECT F1, F2, F3 FROM RealQuery;

Then save that as a query object (e.g. QueryUNION) and work with that query in VBA instead of RealQuery

 

[Pat Hartman]

Flat files cannot be edited directly. If you want to modify the data in a flat file, you need to read the file using one filespec and then write the updated version with the second filespec.

If all you want to do is to add two rows to the top of the file, then the union solution suggested by plog will probably work but I would turn off the file headers and write three lines rather than two so the third line includes the file headers. Otherwise, you could run afoul of data types. since the Union query will expect all data in each column to be the same data type. So if column 1 is numeric data, you can't union it with a column that is text.

 

[phinix]

Flat files cannot be edited directly. If you want to modify the data in a flat file, you need to read the file using one filespec and then write the updated version with the second filespec.

If all you want to do is to add two rows to the top of the file, then the union solution suggested by plog will probably work but I would turn off the file headers and write three lines rather than two so the third line includes the file headers. Otherwise, you could run afoul of data types. since the Union query will expect all data in each column to be the same data type. So if column 1 is numeric data, you can't union it with a column that is text.

Thanks guys for your responses. Sorry not getting back earlier, I was caught in different project and now came back to this one.

Yep, problem with union would be data types, I think.

But I got it working other way. I found this solution somewhere and it works.
I used FreeFile(), then Open For Input and then Open For Output, writing those extra rows with initial content.

Dim data As String
Dim hF As Integer: hF = FreeFile()

Open "C:\xxx.csv" For Input As #hF
   data = Input$(LOF(hF), #hF)
Close #hF

Open "C:\xxx.csv" For Output As #hF
   Print #hF, "my extra rows" & vbCrLf & data'
Close #hF

But now I have another issue.
Initial data exported as csv from Access has "#" sign in first header. Access for some reason doesn't export # as #, but changes it to dot.
I have NO IDEA why, but now before I run the above code to add extra rows, I need to edit first character and change that dot to hash.

How can I do that? How can I edit first character from vb level?

 

[CJ_London]

why not export without headers, then when inserting the two rows, make it 3 and include the header row

 

[arnelgp]

you can replaced the "." in the header to "#" before manipulating further.

Dim file As String
Dim data As String
Dim varData As Variant
file = Environ$("userprofile") & "\documents\book1.csv"
'read data
With CreateObject("scripting.filesystemobject").opentextfile(file, 1)
    data = .readall
    .Close
End With
varData = Split(data, vbCrLf)
Debug.Print varData(0)
'replace first occurrence of "." in the header
varData(0) = Replace$(varData(0), ".", "#", 1, 1)
'write data back
With CreateObject("scripting.filesystemobject").opentextfile(file, 2, 0)
    .write Join(varData, vbCrLf)
    .Close
End With

 

[gemma-the-husky]

I don't understand the point about the # and . characters

If you try to assert the first row as column headers, in the csv import you will actually find a dot is an illegal character in a field name and will be replaced by an underscore in the field name. (I think an underscore, offhand), whereas a hash # is legal. Strange, but maybe the hash isn't treated as a normal printable character for some reason.

 

[phinix]

I don't understand the point about the # and . characters

If you try to assert the first row as column headers, in the csv import you will actually find a dot is an illegal character in a field name and will be replaced by an underscore in the field name. (I think an underscore, offhand), whereas a hash # is legal. Strange, but maybe the hash isn't treated as a normal printable character for some reason.

Dot and # is another problem I found. when working on that export extra rows.
Access doesn't export # as first character in header, it automatically changes it to dot.
I don't know why, but it does that.

 

[phinix]

you can replaced the "." in the header to "#" before manipulating further.

Dim file As String
Dim data As String
Dim varData As Variant
file = Environ$("userprofile") & "\documents\book1.csv"
'read data
With CreateObject("scripting.filesystemobject").opentextfile(file, 1)
    data = .readall
    .Close
End With
varData = Split(data, vbCrLf)
Debug.Print varData(0)
'replace first occurrence of "." in the header
varData(0) = Replace$(varData(0), ".", "#", 1, 1)
'write data back
With CreateObject("scripting.filesystemobject").opentextfile(file, 2, 0)
    .write Join(varData, vbCrLf)
    .Close
End With

Thank you, I need to try it out.
What does this "scripting.filesystemobject" mean here?

 

[arnelgp]

it's an object that can manipulate files/folders.
don't worry Every computer (32 bit or 64 bit) has them.

 

[phinix]

it's an object that can manipulate files/folders.
don't worry Every computer (32 bit or 64 bit) has them.

Oh, I see, thanks. I thought its some value I need to type in
I'll try and add your code to mine, will get back once tested

 

[phinix]

it's an object that can manipulate files/folders.
don't worry Every computer (32 bit or 64 bit) has them.

It worked perfectly!
Thank you again
Now I need to google bits from it, I want to learn what exactly it does