Solved

Need convert CSV to pipe delimited text file, with numbering

Posted on 2004-10-13
13
4,085 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:Sirocco
  • 5
  • 4
  • 3
13 Comments
 
LVL 10

Expert Comment

by:anv
ID: 12296735
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
 
LVL 16

Assisted Solution

by:jimbobmcgee
jimbobmcgee earned 60 total points
ID: 12296887
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
 
LVL 10

Expert Comment

by:anv
ID: 12296931
thanx jimbobmcgee

for the correction..
0
 

Author Comment

by:Sirocco
ID: 12299875
anv,

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

Author Comment

by:Sirocco
ID: 12301069
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
 
LVL 10

Accepted Solution

by:
anv earned 65 total points
ID: 12305058
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12308206
>> 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
 

Author Comment

by:Sirocco
ID: 12310312
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
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12321117
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
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12321142
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
 
LVL 16

Expert Comment

by:jimbobmcgee
ID: 12321158
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
 

Author Comment

by:Sirocco
ID: 12339273
Its not clear where this macro should go -in CSV file or in Excel file?

Thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now