PDA

View Full Version : Solved: Comma delimited CSV



ukdane
06-19-2009, 05:49 AM
I have the following macro, which is supposed to create a COMMA DELIMITED csv file.
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


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:
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

Why isn't it comma delimiting correctly?

Thanks

Bob Phillips
06-19-2009, 06:06 AM
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
06-19-2009, 06:10 AM
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
06-19-2009, 06:22 AM
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
06-19-2009, 06:45 AM
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



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

ukdane
06-19-2009, 07:13 AM
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
06-19-2009, 07:24 AM
For a start I think the quotes are going to mess things up because they are text qualifiers.

JONvdHeyden
06-19-2009, 07:31 AM
Try using FSO to create the csv file...

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

Bob Phillips
06-19-2009, 08:47 AM
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

ukdane
06-19-2009, 11:47 AM
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
06-19-2009, 01:44 PM
Here's mine updated:



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

Bob Phillips
06-19-2009, 03:01 PM
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
06-22-2009, 02:12 AM
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
06-24-2009, 01:28 AM
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
06-24-2009, 03:24 AM
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:
Set objOutputFile = objFSO.CreateTextFile("C:\Diverse\netrates.csv")
to
Set objOutputFile = objFSO.CreateTextFile("C:\Diverse\netrates.txt")

Or to use Bob's code you can change this:
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
to

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

ukdane
06-24-2009, 04:07 AM
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:
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

But it doesn't matter if I can get yours to work.
I'll revert

JONvdHeyden
06-24-2009, 04:31 AM
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
06-24-2009, 07:06 AM
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
06-24-2009, 07:13 AM
Did you try mine with a .csv format? Does the upload drop the leading zero?

ukdane
06-26-2009, 12:44 AM
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


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


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.

JONvdHeyden
06-26-2009, 01:23 AM
What do they need to add? Is it constant data that can be written into the VBA?

ukdane
06-26-2009, 03:39 AM
The user needs to be able to add new data to the existing csv file, which wasn't available first time they ran the code.
So in principal it's something like this:

Sub addme() ' Add to existing csv
Dim objFSO As Object
Dim objOutputFile As Object
Dim rngCell As Range
Dim lngLastRow As Long
Dim filename As String
Dim filepath As String
Dim lastcell As Long
'Application.DisplayAlerts = False
filename = InputBox("What is the file name?", "File Name")
lngLastRow = Range("K" & Rows.Count).End(xlUp).Row
filepath = "C:\Diverse\" & filename & ".csv"
'Open the existing csv file, and locate the last cell containig the csv data

'Add the current csv data (from this workbook) to the list in row A (after the existing data)
'of the existing workbook defined above as filename
For Each rngCell In Range("K2:K" & lngLastRow)
objOutputFile.WriteLine rngCell.Text
Next rngCell

Set objFSO = Nothing
Set objOutputFile = Nothing
Set rngCell = Nothing

MsgBox (filename & " is now updated.")
End Sub

So the data it needs to add is the same- but additional data as what would have been added in your initial code.

For example, in the initial code, the user creates the csv file that contains:
02099890486,147,1,12345,0401536217,589,2394
02099890486,95,1,2468,0401536218,609,2395
02099890486,22.05,1,1357,0401536219,629,2396

He then (at a later date) needs to add the following to the above csv file.
02099890486,147,1,25368,0401536220,589,2397
02099890486,95,1,69473,0401536221,609,2398
02099890486,22.05,1,156328,0401536222,629,2399

WITHOUT overwritting the initial csv data.

ukdane
06-26-2009, 04:43 AM
Nevermind, I've found what I needed to do:


Sub adddetails() ' Add to existing workbook
Dim objFSO As Object
Dim objOutputFile As Object
Dim rngCell As Range
Dim lngLastRow As Long
Dim filename As String
Dim filepath As String
Dim lastcell As Long
Application.DisplayAlerts = False
filename = InputBox("What is the file name?", "File name")
lngLastRow = Range("K" & Rows.Count).End(xlUp).Row
filepath = "C:\Diverse\" & filename & ".csv"
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.OpenTextFile(filepath, ForAppending)
For Each rngCell In Range("K2:K" & lngLastRow)
objOutputFile.WriteLine rngCell.Text
Next rngCell
Set objFSO = Nothing
Set objOutputFile = Nothing
Set rngCell = Nothing
MsgBox (filename & " has been updated.")
Application.DisplayAlerts = True
End Sub

JONvdHeyden
06-27-2009, 01:38 PM
Good for you, well done! :)