8 Replies - 461 Views - Last Post: 31 August 2013 - 02:26 PM Rate Topic: -----

#1 swjslj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-August 13

I'm unable to properly format .csv file

Posted 31 August 2013 - 12:46 PM

Hello, I'm trying to write an app that will allow a user to paste a column of fax numbers from Excel into a text box, click a button and have the fax numbers written to a .csv . Each row will contain several columns of static text; only the fax numbers will be different for each row. The format of the .csv is:

x,x,,,,17125556666,,,,G3,,,0,,,,,0
x,x,,,,17125557777,,,,G3,,,0,,,,,0

In a previous post on this subject _Hawk_ gave me some code that with only minor tweaks has me nearly there. What I'm getting now looks exactly like the above format in Notepad, but I can't import it into my fax machine address book app. If I open the .csv in Excel, or paste the text into Word the format looks like this:

x,x,,,,17125556666,,,,G3,,,0,,,,,0
x,x,,,,
17125557777,,,,G3,,,0,,,,,0
x,x,,,,
17125557777,,,,G3,,,0,,,,,0


The first line is correct, but each succeeding line is like the above. It looks like a new line is being inserted where it doesn't belong, but I can't figure out why.

This is what I have now:

Imports System
Imports System.IO
Imports System.Text

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Using sw As New StreamWriter("c:\test.csv")
            'get each line

            Dim prefix As String = "x," & "x" & ",,,,"
            Dim suffix As String = ",,,," & "G3" & ",,," & "0" & ",,,,," & "0"


            For Each line As String In TextBox1.Text.Split(Environment.NewLine)
                'replace the spaces with commas
                Dim swLine As String = line 

                sw.WriteLine(prefix & swLine & suffix)

            Next

        End Using
        'close the stream

    End Sub
End Class  


I thought this was going to be a simple enough project that even my very limited programming knowledge would be enough, but this has been beating me up pretty good. Any suggestions will be greatly appreciated. Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: I'm unable to properly format .csv file

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: I'm unable to properly format .csv file

Posted 31 August 2013 - 01:34 PM

I doubt that the following is the issue, but for debugging purposes I would first try:

TextBox1.Text.Split(vbCrLf)

vbCrLf is the carriage return, linefeed combination, but this is equivalent (in Windows) to Environment.NewLine. Environment.NewLine is much better anyway, but this is just part of a debugging process that I would consider.

I have a debugging tutorial linked in my signature.

This post has been edited by andrewsw: 31 August 2013 - 01:40 PM

Was This Post Helpful? 1
  • +
  • -

#3 swjslj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-August 13

Re: I'm unable to properly format .csv file

Posted 31 August 2013 - 01:41 PM

View Postandrewsw, on 31 August 2013 - 01:34 PM, said:

I doubt that the following is the issue, but for debugging purposes I would first try:

TextBox1.Text.Split(vbCrLf)

vbCrLf is the carriage return, linefeed combination, but this is equivalent (in Windows) to Environment.NewLine. Environment.NewLine is much better anyway, but this is just part of a debugging process that I would consider.


Thanks for the suggestion, I'll give it a try. It just boggles my mind that the output looks perfect in notepad, but the bad formatting show up in virtually any other program. I can even correct the formatting in Word and save as .txt and it will import properly; weird situation!
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: I'm unable to properly format .csv file

Posted 31 August 2013 - 02:06 PM

Again, just as part of a debugging process, I would try setting prefix and suffix to just "*" to see what is output, even though the characters "," etc. shouldn't be an issue.
Was This Post Helpful? 0
  • +
  • -

#5 swjslj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-August 13

Re: I'm unable to properly format .csv file

Posted 31 August 2013 - 02:06 PM

@andrewsw Many thanks! You led me to the solution. I installed Notepad++ and could plainly see the CR LF that I needed to remove. I changed, sw.WriteLine(prefix & swLine & suffix)) to sw.WriteLine(prefix.TrimEnd(Chr(10), Chr(13) & swLine & suffix)) and now all is good! Thanks again, my weekend is now looking better!

This post has been edited by andrewsw: 31 August 2013 - 02:08 PM
Reason for edit:: Removed previous quote

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3371
  • View blog
  • Posts: 11,420
  • Joined: 12-December 12

Re: I'm unable to properly format .csv file

Posted 31 August 2013 - 02:09 PM

Glad you sorted :)
Was This Post Helpful? 0
  • +
  • -

#7 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1052
  • View blog
  • Posts: 4,077
  • Joined: 02-July 08

Re: I'm unable to properly format .csv file

Posted 31 August 2013 - 02:11 PM

This: "x," & "x" & ",,,," is the same as "x,x,,,," - fyi.

Maybe your getting some special chars in the paste from excel. Maybe reading each line in the textbox would give better results.
Was This Post Helpful? 0
  • +
  • -

#8 swjslj  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 29-August 13

Re: I'm unable to properly format .csv file

Posted 31 August 2013 - 02:22 PM

View Post_HAWK_, on 31 August 2013 - 02:11 PM, said:

This: "x," & "x" & ",,,," is the same as "x,x,,,," - fyi.

Maybe your getting some special chars in the paste from excel. Maybe reading each line in the textbox would give better results.


_HAWK_, thanks for all the help. You got me most of the way there the other day, and with andrewsw's help today, I'm gold! Nothing left now but some simple cosmetic things. Have a great holiday weekend!
Was This Post Helpful? 0
  • +
  • -

#9 AdamSpeight2008  Icon User is offline

  • MrCupOfT
  • member icon


Reputation: 2257
  • View blog
  • Posts: 9,445
  • Joined: 29-May 08

Re: I'm unable to properly format .csv file

Posted 31 August 2013 - 02:26 PM

Have a look at one of my tutorials called:- The TextFieldParser.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1