Solved

Need convert CSV to pipe delimited text file, with numbering

Posted on 2004-10-13
13
4,112 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month9 days, 13 hours left to enroll

624 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