[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 675
  • Last Modified:

Getting Excel to export CSV with actual commas

I have a list of email addresses in an Excel Spreadsheet (Office 2007). This is the only column I have

I need to import these addresses into an online newsletter system which MUST be formatted as comma seperated

I can save as a CSV from Excel, but it doesnt actually add commas, it says its a CSV, but the seperator is a line break.

Can I force Excel to seperate by commas?
0
reedy2k
Asked:
reedy2k
  • 11
  • 8
1 Solution
 
MalicUKCommented:
Hi reedy2k,

You can manually print the info to a text file. The following code with write the activesheet to C:\myFile.txt

Sub PrintSheet()
Dim i As Long, j As Long, Output As String, FilePath As String
FilePath = "c:\myFile.txt"

On Error Resume Next
Kill FilePath
On Error GoTo 0
Open FilePath For Append As #1

For i = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Output = ""
    For j = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Column
        Output = Output & "," & Cells(i, j).Value
    Next
Output = Mid(Output, 2) & Chr(13)
Print #1, Output
Next

Close #1

End Sub

Cheers,
MalicUK.
0
 
reedy2kAuthor Commented:
ok
so where do i stick that code? And then how do I access it
0
 
MalicUKCommented:
Hi reedy2k,

From Excel press Alt+F11, which will open the vba editor. Now do Insert -> Module, and paste that code into the window that appears. Press Alt+F11 to return to Excel.

Now in Excel all you need to do is have the sheet you want to output as the active sheet, and do Tools -> Macro -> Macros, select "PrintSheet" from the list and click "Run".

Cheers,
MalicUK.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
reedy2kAuthor Commented:
i tried all this and it created the file. Its the same file as if I just went Save as CSV....i.e., no comma seperation
0
 
MalicUKCommented:
reedy2k,

The file created is 100% comma delimited, open it in notepad and you will see. The only part that isn't delimited is when it moves to a new row, however this is totally normal in delimited files - they still include line breaks.

If you have used data-> Text To Columns in the session of excel you have open, then excel may automatically apply this to any further data you bring in.

But what you have from that code is what you have asked for - a standard comma delimited text file. If you need something different then you need to explain what you currently have and what you want to get in more detail.

Cheers,
MalicUK.
0
 
reedy2kAuthor Commented:
The original question I asked was "Can I force Excel to seperate by commas?"

The file your script creates contains no commas between the data.
0
 
MalicUKCommented:
erm, it does...

        Output = Output & "," & Cells(i, j).Value

that line parses a comma (,) between every cell value on each row. When it reaches the end of a row it prints a comma delimited line to the file, and starts a new line.

You really need to open .txt files in notepad, nothing else, to view what they actually contain.
0
 
reedy2kAuthor Commented:
erm, no it doesn't...

whatever you think your code does, it doesn't. I opened myFile.txt in notepad (obviously) and it contains NO commas. Simple as that.
0
 
MalicUKCommented:
I've done some guess work and I feel the problem is your understanding of what a delimited FILE is. A delimited file will always contain 2 separate delimiters, the column delimiter (comma, tab, whatever) and also a delimiter at the end of every row (line break, semi-colon, etc). This is normal for all delimited files as usually they have to be read and returned into a column/row format. That is a delimited FILE.

If however, you are are looking for a delimited text STRING, this will have a single delimiter between each data item. This is because they don't need to be returned into a 2 dimentional array.

Amended code which gives you is a delimited string is:

Sub PrintSheet()
Dim i As Long, j As Long, Output As String, FilePath As String
FilePath = "c:\myFile.txt"

On Error Resume Next
Kill FilePath
On Error GoTo 0
Open FilePath For Append As #1

For i = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Output = ""
    For j = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Column
        Output = Output & "," & Cells(i, j).Value
    Next
Next

Output = Left(Output, Len(Output) - 1)
Print #1, Output
Close #1

End Sub

Cheers,
MalicUK.
0
 
MalicUKCommented:
Look at my explaination above. The problem was in how you described your current situation and the required outcomes.

From pure guessing I've worked out you have a vertical list in excel, in one column only. Creating a comma delimited file from this will give no commas, for the reasons I have explained (2 dimentional file vs 1 dimentional string).

I know not everyone here is techies, but experts aren't mind-readers. We do expect that the asker is expecting a delimited file outcome if that is what they ask for.

To save future confusion it is probably worth expanding on your explainations with more detail, even uploading a test file for the experts to actually see.

MUK.
0
 
reedy2kAuthor Commented:
with this new code, I get a text file, with the last email address only, and a comma before it
0
 
MalicUKCommented:
I am going to apologise now :P

I did miss the "This is the only column I have", so I am sorry.

However there are conflicting messages from the original question, but I would have got there faster if I'd seen that.

MUK.
0
 
MalicUKCommented:
Gah, should finally do it:

Sub PrintSheet()
Dim i As Long, j As Long, Output As String, FilePath As String
FilePath = "c:\myFile.txt"

On Error Resume Next
Kill FilePath
On Error GoTo 0
Open FilePath For Append As #1

For i = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Row
    For j = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Column
        Output = Output & "," & Cells(i, j).Value
    Next
Next

Output = Mid(Output, 2)
Print #1, Output
Close #1

End Sub
0
 
reedy2kAuthor Commented:
OK
I think we're getting closer

I now get a txt file with each cell seperated by a comma

However when trying to upload to the newsletter system I get the message:

"Do not use the seperator as a character inside a field."

Any ideas?
0
 
MalicUKCommented:
Huh? Barely a clue to be honest, it sounds like your data has a comma inside one of the data items?

You seem to be using some specialist software, so I'm guessing it has it's own rules. I'm really going to need an exact (even if it is faked) example of what your data currently looks like, and an exact example of what you are trying to get as an output (i.e. what the software requires as input).
0
 
reedy2kAuthor Commented:
0
 
MalicUKCommented:
OK, that really does explain what you need. This code should do the trick:

Sub PrintSheet()
Dim i As Long, j As Long, Output As String, FilePath As String
FilePath = "c:\myFile.txt"

On Error Resume Next
Kill FilePath
On Error GoTo 0
Open FilePath For Append As #1

For i = 1 To ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Output = """"","""","""","""","""","""","""","""","""","""","""","""","""",""" & Cells(i, 1).Value & ""","""","""","""","""","""","""","""","""",""""" & Chr(13)
Print #1, Output
Next

Close #1

End Sub

Cheers,
MalicUK.
0
 
reedy2kAuthor Commented:
youre a star, thats worked. Cheers.
0
 
MalicUKCommented:
No problems, glad we got there in the end. Just shows how much having an accurate overview helps ;)

Thanks for the grade! :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now