csv with delimited to Mysql

[nubie.net]

hello, my appreciating, if you give example code in my problem
i have 1 file csv with dilimited only column A
example
column a
name | address | phone | birthofdate
deby | jersey | 123456 | 01-March-1990

my case is
I want to convert to txt with delimited | and then insert to Mysql table.
basically I can separate delimited csv using excel and then insert to mysql by import.
I hope I can create a program using vb.net and you can available to give me some code, please.

Thank you

[Begginnerdev]

You will want to open the file, read/split all lines, then rehash them with your delimiter:

This code is written on the fly.

Try
    'Reader/Writer for file operations.
    Dim sr As New StreamReader("PathToMyFile")
    Dim sw As New StreamWriter("PathToFile")

    'Lists for storage
    Dim lstAllLines As New List(Of List(Of String))
    Dim lstFinal As New List(Of String)

    'Read/Split all lines.
    Do While sr.Peek <> -1
        lstAllLines.Add(sr.ReadLine().Split(",").ToList)
    End While

    'Cycle through and delimit
    For Each lst As List(Of String) in lstAllLines
      Dim str As String = String.Empty
      For i = 0 to lst.Count - 1
          'Check to make sure you are not on the last element.
          If lst(i) < lst.Count - 1 Then
              str &= lst(i) & "|"
          Else
              str &= lst(i)
          End If
      Next
      lstFinal.Add(str)
    Next

    'Write all lines
    For Each s As String in lstFinal
        sw.WriteLine(s)
    Next
Catch ex As Exception
    MsgBox(ex.ToString)
End Try

[nubie.net]

hi dev
I follow your code but cannot create txt files from CSV Files, I think you forget to put library excel interoup and your code cannot read excel csv files. can u explain more details about your code. thanks.

[Begginnerdev]

I use code very close to this to manipulate CSV files all of the time.

A CSV file is treated as a text file; it just has formatting that you would not see usually.

What error are you getting for your csv?

Be sure to close the StreamReader/Writer with you are finished with your operations.

sr.Close()
sr = Nothing
sw.Close()
sw = Nothing

[nubie.net]

thank for replay

I don't getting error but your code cannot create data files txt, I mean txt null after csv has been execute in this program. files csv cannot transfer data to txt files. or I'm a beginner so I don't understand this code and I must learn more. I hope you give an example if there is free time to this role use project (sln). in my project I just put 1 button. oh .. in your code I getting red underline in postion

'Read/Split all lines.
Do While sr.Peek <> -1
lstAllLines.Add(sr.ReadLine().Split(",").ToList)
End While

Error 2 'End While' must be preceded by a matching 'While'.
Error 1 'Do' must end with a matching 'Loop'.

and so I remove DO then I type Loop under 1stAllines..

Sorry, I need your help.

I put link to you, kindly u can download to review

Download This Project

Thank you

[Begginnerdev]

The correct syntax would be:

'Read/Split all lines.
 While sr.Peek <> -1
     lstAllLines.Add(sr.ReadLine().Split(",").ToList)
 End While

[nubie.net]

Error sir ..
Image Error

Click Here to view Image

[Begginnerdev]

Do you have the file open in Excel while trying to run the process?

Something has control of the file at that time.

[nubie.net]

no.. i'm not open excel files same the times.
excel files only stay at folder

[Begginnerdev]

Are you trying to read/write from/to the same file?

[nubie.net]

no sir, streamreader .csv and streamwriter .txt

thank for your help .. problem solve.
i create using interoup excel and then befove save to database i put data in dataset then I push button to save. i think this is not simple, so why? I can't create simple app.

thanks you so much Begginnerdev