PDA

View Full Version : VBA to write to CSV in certain way



Tudka
06-29-2016, 09:57 AM
Hi all, I am a newbie here and I am trying to get some help on VBA script to be able to get the data over into a new CSV in a certain way that I showed below. Also I have started the VBA script but got lost and I can't manage to write the date and time value into a next column. Also I am not sure how to get the whole range of C3:C23 cells into a new csv A5:A25 range.


1649616497

Hope you could help me with this or get me in the right direction.

Thank you!

snb
06-29-2016, 12:18 PM
Please post a sample workbook.

Tudka
06-29-2016, 01:12 PM
1649816499

I hope this works. The original.csv one is I want to convert into a Result.csv

snb
06-29-2016, 01:52 PM
Here you go:


Sub M_snb()
With CreateObject("scripting.filesystemobject")
sn = Split(.opentextfile("J:\download\original.csv").readall, vbCrLf)

sn(0) = "'text',Title: Water Industry Standard Data File" & vbCrLf & "'text',Site: London" & vbCrLf & "'ch',1,pressure,m"
sn(1) = "'time'," & Left(sn(2), 17) & "15," & UBound(sn) - 2

For j = 2 To UBound(sn)
sn(j) = Mid(sn(j), 18)
Next

.createtextfile("J:\download\original_001.csv").write Join(sn, vbCrLf)
End With
End Sub

Tudka
07-02-2016, 07:41 AM
Thank you! It's not doing exactly what I need, but I will put more work on it and hopefully make it happen.

snb
07-02-2016, 08:37 AM
It does exactly what you asked for.....:confused2

Tudka
07-02-2016, 08:49 AM
Below is the CSV i get when running this VBA code.

16533

D4 cell needs to be 15 and A5 to A10 is not transferred for some reason.

I am sorry, but I don't think I can do anything wrong by running this code.

Kenneth Hobs
07-02-2016, 05:00 PM
I don't know how you got that. SNB's code did mostly what you wanted. A few single quote marks did not delimit the field word values. One field 'min' was missing.

Try to learn concepts so that you can build on what others teach you.


Sub M_snb2()
With CreateObject("scripting.filesystemobject")
sn = Split(.opentextfile(ThisWorkbook.Path & _
"\original.csv ").readall, vbCrLf)

sn(0) = "'text','Title: Water Industry Standard Data File'" & _
vbCrLf & "'text','Site: London'" & vbCrLf & "'ch',1,'pressure','m'"
sn(1) = "'time'," & Left(sn(2), 17) & "15,'min'," & UBound(sn) - 2

For j = 2 To UBound(sn)
sn(j) = Mid(sn(j), 18)
Next

.createtextfile(ThisWorkbook.Path & "\" & _
"\original_002.csv").write Join(sn, vbCrLf)
End With
End Sub

Tudka
07-04-2016, 02:11 AM
Kenneth, you are right - I am trying to learn it now. Thanks for your help.

Do you have an idea how to change the date format that is exported from "dd/mm/yyyy" to "dd/mm/yy". It does not seem to work with the code I have tried writing myself. Like the one below:


Sub LoggerExport()

Dim myfile As String, i As Integer, ival As Integer

myfile = Application.DefaultFilePath & "\Plots\" & ThisWorkbook.Name
ival = Application.WorksheetFunction.Count(Range("C:C"))

Open myfile For Output As #1

Write #1, "'text'", "'Title: Water Industry Standard Data File'"
Write #1, "'text'", "'Site: '"
Write #1, "'ch'", 1, "'pressure'", "'m'"
Write #1, "'Time'", Format(Cells(3, 1), "'dd/mm/yy"), Format(Cells(3, 2), "hh:mm"), 15, "'min'", ival

For i = 3 To (ival + 2)
Write #1, Cells(i, 3)
Next i

Close #1
End Sub



I don't know how you got that. SNB's code did mostly what you wanted. A few single quote marks did not delimit the field word values. One field 'min' was missing.

Try to learn concepts so that you can build on what others teach you.


Sub M_snb2()
With CreateObject("scripting.filesystemobject")
sn = Split(.opentextfile(ThisWorkbook.Path & _
"\original.csv ").readall, vbCrLf)

sn(0) = "'text','Title: Water Industry Standard Data File'" & _
vbCrLf & "'text','Site: London'" & vbCrLf & "'ch',1,'pressure','m'"
sn(1) = "'time'," & Left(sn(2), 17) & "15,'min'," & UBound(sn) - 2

For j = 2 To UBound(sn)
sn(j) = Mid(sn(j), 18)
Next

.createtextfile(ThisWorkbook.Path & "\" & _
"\original_002.csv").write Join(sn, vbCrLf)
End With
End Sub

Kenneth Hobs
07-04-2016, 06:28 AM
Remove the single quote from your date string format?

Write #1, "'Time'", Format(Cells(3, 1), "dd/mm/yy"), Format(Cells(3, 2), "hh:mm"), 15, "'min'", ival