Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Need convert CSV to pipe delimited text file, with numbering

Posted on 2004-10-13
13
Medium Priority
?
4,141 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
12 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 180 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
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!

 

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 195 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
 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

580 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