Sirocco
asked on
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|co
2| column1|column2|column3|co
3| column1|column2|column3|co
.
.
.
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
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|co
2| column1|column2|column3|co
3| column1|column2|column3|co
.
.
.
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
thanx jimbobmcgee
for the correction..
for the correction..
ASKER
anv,
are you tried this macro? It show "Run time error '13' Type mismatch".(highlighted the line you offer). And it not work.
are you tried this macro? It show "Run time error '13' Type mismatch".(highlighted the line you offer). And it not work.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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 = ";".
Not too sure what your asking but, if you have a comma-separated file, oldDelim = ",". If you have a semi-colon-separated file, oldDelim = ";".
ASKER
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.
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.
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(OutputF
End If
RowCount = 1
Do Until Sheets(OutSheet).Cells(Row
For ColLoop = StartCol To EndCol
FileObj.Write(Sheets(OutSh
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.
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(OutputF
End If
RowCount = 1
Do Until Sheets(OutSheet).Cells(Row
For ColLoop = StartCol To EndCol
FileObj.Write(Sheets(OutSh
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.
If you need the row number in front, change:
Do Until Sheets(OutSheet).Cells(Row
For ColLoop = StartCol To EndCol
FileObj.Write(Sheets(OutSh
If ColLoop < EndCol Then FileObj.Write(Delim) 'Write the delimeter
Next ColLoop
RowCount = RowCount + 1
Loop
to:
Do Until Sheets(OutSheet).Cells(Row
FileObj.Write(RowCount) & Delim 'Write Row Number and Delimeter
For ColLoop = StartCol To EndCol
FileObj.Write(Sheets(OutSh
If ColLoop < EndCol Then FileObj.Write(Delim) 'Write the delimeter
Next ColLoop
RowCount = RowCount + 1
Loop
Regards
J.
Do Until Sheets(OutSheet).Cells(Row
For ColLoop = StartCol To EndCol
FileObj.Write(Sheets(OutSh
If ColLoop < EndCol Then FileObj.Write(Delim) 'Write the delimeter
Next ColLoop
RowCount = RowCount + 1
Loop
to:
Do Until Sheets(OutSheet).Cells(Row
FileObj.Write(RowCount) & Delim 'Write Row Number and Delimeter
For ColLoop = StartCol To EndCol
FileObj.Write(Sheets(OutSh
If ColLoop < EndCol Then FileObj.Write(Delim) 'Write the delimeter
Next ColLoop
RowCount = RowCount + 1
Loop
Regards
J.
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.
RowCount = RowCount + 1
to:
RowCount = RowCount + 1
FileObj.Write(vbCrLf) 'Write a new line into the file
Regards
J.
ASKER
Its not clear where this macro should go -in CSV file or in Excel file?
Thanks
Thanks
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