Solved

Need convert CSV to pipe delimited text file, with numbering

Posted on 2004-10-13
13
4,089 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Opening Remote & Local Data Connection 2 51
Access 2016 VB code 9 103
How to make an ADE file by code? 11 80
MsgBox 4 48
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…
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…

867 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

21 Experts available now in Live!

Get 1:1 Help Now