Solved: Comma delimited CSV

[ukdane]

I have the following macro, which is supposed to create a COMMA DELIMITED csv file.
[vba] Sub runCSV()
Dim mylastcell As String
On Error Resume Next
Application.DisplayAlerts = False
mylastcell = ActiveSheet.Range("AC65536").End(xlUp).Row
Range("AC2:AC" & mylastcell).Select
Selection.Copy
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ChDir "C:\Diverse"
ActiveWorkbook.SaveAs Filename:= _
"C:\Diverse\netrates.csv", FileFormat:= _
xlCSV, CreateBackup:=False

ActiveWorkbook.Close
End Sub
[/vba]

I have another program (not Excel) that is reading the CSV, and extracting the data to variables.
HOWEVER it's not being delimited at the comma. Instead each new line is being assigned to a variable.

BUT
if I copy paste the same data, and save as a csv, then import the data it works (and is assigned correctly to the variable).
Basically everything I do is the same except in this 2nd process, I do the copy/paste/save of the raw data manually, instead of running the above code.

I don't think it's necessary, but the code I'm using to read the data into the variables is:
[vba]filename = InputBox("Input filename")
path = "C:\Diverse\" & filename & ".csv"
Open path For Input As #1

Do While Not EOF(1)
Input #1, myvariable1, myvariable2, myvariable3 'etc etc [/vba]

Why isn't it comma delimiting correctly?

Thanks

[Bob Phillips]

When I run it nothing happens, but as you are copying a single column, you want get any comma separated data as far as I can see.

[JONvdHeyden]

Are you wanting the colum of data to appear as a single line in the .csv file?

If so you need to transpose it when you paste it to the new workbook.

[ukdane]

XLD: I've adjusted the code from

Selection.PasteSpecial Paste:=xlPasteValuesAnd Formats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

to

Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False


Jon: No, I don't want it all on one line.
I need each row to be copied as is, and the variables to be read per line, hence this:
Do While Not EOF(1)
Input #1,

Hope that helps explain things.

[Bob Phillips]

dane,

I think you are missing the point we are making.

You are only copying a single column. If you don't want to transpose it, your output book will still contain the single column vector, no comma separation.

If you want it comma separated you have to transpose it

[vba]

Sub runCSV()
Dim mylastcell As String
On Error Resume Next
Application.DisplayAlerts = False
mylastcell = ActiveSheet.Range("AC65536").End(xlUp).Row
Range("AC2:AC" & mylastcell).Copy
Workbooks.Add
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
ChDir "C:\Diverse"
ActiveWorkbook.SaveAs filename:= _
"C:\Diverse\netrates.csv", FileFormat:= _
xlCSV, CreateBackup:=False

ActiveWorkbook.Close
End Sub
[/vba]

[ukdane]

OK, I think I see where we have missunderstood each other, and it's my fault for not explaining better.

The Transpose must be set to False otherwise no data is copied, and here's why....

.... the information I forgot to tell you was that each row in the column AC includes a string.
for example;
AC1 = "A,1,2,3,4,5"
AC2 = "B,1,1,1,1,1"
AC3= "C,1,2,3,4,5"
and so on.

So when I import the data into the other program, each line is looped, and the data should be assigned to variables once for each loop.
So for loop 1, variable1 = "A", variable2 = "1", variable3 = "2" and so on
for loop 2, variable1 = "B", variable2 = "1", variable3 = "1" and so on

I have tried changing the Transpose to true, as suggested, but if I do that, nothing is copied.

I hope I've understood you, and that providing this missing info has helped clarify.

Cheers

[JONvdHeyden]

For a start I think the quotes are going to mess things up because they are text qualifiers.

[JONvdHeyden]

Try using FSO to create the csv file...

[VBA]Sub RunCSV()
Dim objFSO As Object
Dim objOutputFile As Object
Dim rngCell As Range
Dim lngLastRow As Long
lngLastRow = Range("AC" & Rows.Count).End(xlUp).Row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile("C:\Diverse\netrates.csv")
For Each rngCell In Range("AC1:AC" & lngLastRow)
objOutputFile.writeline Mid$(rngCell.Text, 2, Len(rngCell.Text) - 2)
Next rngCell
Set objFSO = Nothing
Set objOutputFile = Nothing
Set rngcelll = Nothing
End Sub
[/VBA]

[Bob Phillips]

[vba]

Sub runCSV()
Dim mylastcell As String
On Error Resume Next
Application.DisplayAlerts = False
mylastcell = ActiveSheet.Range("AC65536").End(xlUp).Row
Range("AC2:AC" & mylastcell).Copy
Workbooks.Add
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns(1).Replace What:="""", Replacement:="", LookAt:=xlPart
Application.CutCopyMode = False
Columns(1).TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Comma:=True, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
Application.CutCopyMode = False
ChDir "C:\Diverse"
ActiveWorkbook.SaveAs filename:= _
"C:\Diverse\netrates.csv", FileFormat:= _
xlCSV, CreateBackup:=False

ActiveWorkbook.Close
End Sub
[/vba]

[ukdane]

Would it help if I said there are NO quotes, I put the strings in quotes, purely to indicate that they were text strings.

I'm working in Excel 2003.

[JONvdHeyden]

Here's mine updated:

[VBA]

Sub RunCSV()
Dim objFSO As Object
Dim objOutputFile As Object
Dim rngCell As Range
Dim lngLastRow As Long
lngLastRow = Range("AC" & Rows.Count).End(xlUp).Row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile("C:\Diverse\netrates.csv")
For Each rngCell In Range("AC1:AC" & lngLastRow)
objOutputFile.WriteLine rngCell.Text
Next rngCell
Set objFSO = Nothing
Set objOutputFile = Nothing
Set rngCell = Nothing
End Sub

[/VBA]

[Bob Phillips]

Would it help if I said there are NO quotes, I put the strings in quotes, purely to indicate that they were text strings.

I'm working in Excel 2003.

My code doesn't care, quotes or no quotes.

[ukdane]

Hi Jon,
I've tried your code and it looks like it works as I require.

Any explanation as to why this method works, but mine doesn't?

Cheers, and thanks for your help.
(You to xld).

[ukdane]

Jon,
I've just come across a problem with your code.

Assume I have my data that I'm ready to copy across.
For example:

02099890486,147,1,12345,0401536217,589,2394
02099890486,95,1,2468,0401536218,609,2395
02099890486,22.05,1,1357,0401536219,629,2396

When it has been copied across, the data reads as follows.
2099890486,147,1,12345,0401536217,589,2394
2099890486,95,1,2468,0401536218,609,2395
2099890486,22.05,1,1357,0401536219,629,2396

In effect the code removes the 0 at the start of each line.
This is incorrect, as the 0 must be included IF it appears, (Otherwise the other program can't read it).

What is causing the 0 to be removed (I assume it must be formated as a number, not text), and how do I get Excel to not remove the 0.

Thanks for your help.

[JONvdHeyden]

I believe that the leading zero is there but it gets dropped when you reopen the CSV in excel. Have you tested an upload? I'm almost certain that the zero will be there.

If not will your system allow a text format (.txt) where the fields are delimited with a comma? If so test an upload in a .txt format.

You need to change this line:
[VBA]Set objOutputFile = objFSO.CreateTextFile("C:\Diverse\netrates.csv")[/VBA]
to
[VBA]Set objOutputFile = objFSO.CreateTextFile("C:\Diverse\netrates.txt")[/VBA]

Or to use Bob's code you can change this:
[vba]FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True[/vba]
to
[vba]
FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), _
Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True
[/vba]

[ukdane]

Thanks Jon, I'll give your code a go.
I can't get XLD's code to work at all.
It stops with an error at this line:
[VBA]Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
[/VBA]
But it doesn't matter if I can get yours to work.
I'll revert

[JONvdHeyden]

What particular error message do you encounter? XLD's code works for me...

XLD's code may become your only option if it turns out that my method does actuallly drop the zero. His code allows you to set the format of the field to text so that it shouldn't lose the leading zero.

[ukdane]

Jon,
Your code generates a .txt file ok. But it won't work as the other program imports it as one long string, and not comma delimited. It also adds quotation marks at the begning and end of each line. Which doesn't help.

I think I'll have to have a closer look at XLD's code.

[JONvdHeyden]

Did you try mine with a .csv format? Does the upload drop the leading zero?

[ukdane]

Hi Jon,
Yes I tried your csv version, and it works, apart from the fact that it drops the leading 0.
However I have now found a solution to that by programming the code of the program that reads the data- it counts the length of the variable (as that is always a set length) and if it is shorter than it should be, it adds a 0 to the front until it is the correct length.
(This seems to work as it is).

I have another question about your code:
Using your code
[VBA]

Sub RunCSV()
Dim objFSO As Object
Dim objOutputFile As Object
Dim rngCell As Range
Dim lngLastRow As Long
lngLastRow = Range("AC" & Rows.Count).End(xlUp).Row
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile("C:\Diverse\netrates.csv")
For Each rngCell In Range("AC2:AC" & lngLastRow)
objOutputFile.WriteLine rngCell.Text
Next rngCell
Set objFSO = Nothing
Set objOutputFile = Nothing
Set rngCell = Nothing
End Sub

[/VBA]

Is it possible to select a specific range/cell within a specific worksheet where the data is written.
In this instance, the user needs to be able to add extra data to the bottom of an existing list. (In otherwords, the code above is fine for CREATING the inital list, but afterwards, they will need to add to the EXISTING list).

Thanks as always.