Need convert CSV to pipe delimited text file, with numbering

I have CSV format database. There are 10 columns with data. It has no numbering of rows.
I need convert this CSV file to pipe (|) delimited text data file. I need add in front numbering of lines to get looks like this:

1| column1|column2|column3|column4|column5|column6|column7|column8|column9|column10
2| column1|column2|column3|column4|column5|column6|column7|column8|column9|column10
3| column1|column2|column3|column4|column5|column6|column7|column8|column9|column10
.
.
.
I use following macro:

Sub ChangeDelimiter()
 Dim inFile As String, vFileNum As Integer, eLine As String
 Dim oldDelim As String, newDelim As String, vFile2 As Integer, outFile As String
 inFile = "C:\temp\csvfile.csv"
 outFile = "C:\temp\semifile.txt"
 oldDelim = ","
 newDelim = "|"
 vFileNum = FreeFile()
 Open inFile For Input As #vFileNum
 vFile2 = FreeFile()
 Open outFile For Output As #vFile2
 Do While Not EOF(vFileNum)
  Line Input #vFileNum, eLine
  eLine = Replace(eLine, oldDelim, newDelim, 1, -1, 1)
  Print #vFile2, eLine
 Loop
 Close #vFileNum
 Close #vFile2
End Sub

How to add lines numbering feature to this macro?
Also, probably, you can offer more convenient solution(though mentioned macro is handy enough, just to rename infile/outfile, etc)

Thanks

SiroccoAsked:
Who is Participating?
 
anvConnect With a Mentor Commented:
hi Sirocco

actually u need to add following instead of
 eline = i + newDelim + eline

 eline = i & newDelim & eline

since i is an integer and eline is a string, so it was performing Addition instead of concatenation..and thus the error
0
 
anvCommented:
How to add lines numbering feature to this macro?

use
Sub ChangeDelimiter()
 Dim inFile As String, vFileNum As Integer, eLine As String
 Dim oldDelim As String, newDelim As String, vFile2 As Integer, outFile As String
 inFile = "C:\temp\csvfile.csv"
 outFile = "C:\temp\semifile.txt"
 oldDelim = ","
 newDelim = "|"
 vFileNum = FreeFile()
 Open inFile For Input As #vFileNum
 vFile2 = FreeFile()
 Open outFile For Output As #vFile2
dim i!
i=0
 Do While Not EOF(vFileNum)
  i=i+1
  Line Input #vFileNum, eLine
  eLine = Replace(eLine, oldDelim, newDelim, 1, -1, 1)
  eline = i + " " + eline
  Print #vFile2, eLine
 Loop
 Close #vFileNum
 Close #vFile2
End Sub
0
 
jimbobmcgeeConnect With a Mentor Commented:
There's a slight change you may want to make to anv's code above.  Change

    eline = i + " " + eline

to

    eline = i & newDelim & eline

to make your line numbers appear in their very own column.

J.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
anvCommented:
thanx jimbobmcgee

for the correction..
0
 
SiroccoAuthor Commented:
anv,

are you tried this macro?  It show "Run time error '13' Type mismatch".(highlighted the line you offer).  And it not work.
0
 
SiroccoAuthor Commented:
jimbobmcgee macro work, OK

eline = i & newDelim & eline

Its unclear, when I set this:

oldDelim = ","
 newDelim = "|"

I got semi-colon (;) separated datafile.
When I use this value:

oldDelim = ";"
 newDelim = "|"    

I got correct, pipe(|)  delimited text. Why? Original CSV file is semi-colon or comma delimited?

0
 
jimbobmcgeeCommented:
>> I got semi-colon (;) separated datafile.

Not too sure what your asking but, if you have a comma-separated file, oldDelim = ",".  If you have a semi-colon-separated file, oldDelim = ";".
0
 
SiroccoAuthor Commented:
First, I create a CSV file (save Excel file as CSV format) Is this real "," delimited file?
Then I insert macro in Excel CSV file where, set as follows:
oldDelim = ","  
newDelim = "|"
But I got semi-colon (;) separated text file as output, though i need pipe delimited.

When I use this value in macro:

oldDelim = ";"
 newDelim = "|"    

then I got correct, pipe(|)  delimited text. Its unclear.



0
 
jimbobmcgeeCommented:
If you are running this macro from within Excel's rendering of the .CSV file, it will not find ","s.  Instead of saving as a .CSV file, write your own implementation of the save routine to save as pipe-delimeted:

Sub ExportToFile()
   
    'User input
    Const Overwrite     As Boolean = True           'Overwrite existing file?
    Const OutSheet      As String = "Sheet1"        'Sheet to save
    Const StartCol        As Integer = 1                 'Save columns 1(A)...
    Const EndCol          As Integer = 5                 '...to 5(E)
    Const Delim            As String = "|"                'Delimeter
   
    'System variables
    Dim OutputFile      As String
    Dim FSO               As New Scripting.FileSystemObject
    Dim FileObj           As Object
    Dim RowCount       As Integer
    Dim OutCount        As Integer
    Dim ColLoop          As Integer  
   
    'Code begin
    OutputFile = InputBox("Enter output filename", "Export")

        If OutputFile = "" Then
            MsgBox "No export file specified.", vbCritical, "Export"
            Exit Sub
        Else
            Set FileObj = FSO.CreateTextFile(OutputFile, Overwrite)       'Create file for output
        End If

    RowCount = 1
   
    Do Until Sheets(OutSheet).Cells(RowCount, 1) = Empty

        For ColLoop = StartCol To EndCol
           
           FileObj.Write(Sheets(OutSheet).Cells(RowCount, ColLoop).Value)   'Write cell to file
           If ColLoop < EndCol Then FileObj.Write(Delim)                              'Write the delimeter

        Next ColLoop

        RowCount = RowCount + 1

    Loop
   
    FileObj.Close
   
    MsgBox UCase(OutputFile) & " successfully created.", vbInformation, "Export"
           
End Sub


HTH

J.
0
 
jimbobmcgeeCommented:
If you need the row number in front, change:

    Do Until Sheets(OutSheet).Cells(RowCount, 1) = Empty

        For ColLoop = StartCol To EndCol
           
           FileObj.Write(Sheets(OutSheet).Cells(RowCount, ColLoop).Value)   'Write cell to file
           If ColLoop < EndCol Then FileObj.Write(Delim)                              'Write the delimeter

        Next ColLoop

        RowCount = RowCount + 1

    Loop

to:

    Do Until Sheets(OutSheet).Cells(RowCount, 1) = Empty

        FileObj.Write(RowCount) & Delim       'Write Row Number and Delimeter

        For ColLoop = StartCol To EndCol
           
           FileObj.Write(Sheets(OutSheet).Cells(RowCount, ColLoop).Value)   'Write cell to file
           If ColLoop < EndCol Then FileObj.Write(Delim)                              'Write the delimeter

        Next ColLoop

        RowCount = RowCount + 1

    Loop

Regards
J.
0
 
jimbobmcgeeCommented:
And I've just noticed a slight bug.  Change:

        RowCount = RowCount + 1

to:

        RowCount = RowCount + 1
        FileObj.Write(vbCrLf)                    'Write a new line into the file

Regards
J.
0
 
SiroccoAuthor Commented:
Its not clear where this macro should go -in CSV file or in Excel file?

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.