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!
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
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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.