kp
asked on
Convert Pipe Delimited text file into Excel (xlsx) file using VBScript
My requirement is to convert below pipe delimited text (input.txt) file into an excel file (.XLSX) using VBScript .
Here is example of my text input file :-
input.txt (attached for reference)
Name|age|gender|department
don|23|male|computer|2010
ptalakes|24|female|maths |2015
chang|28|male|arts |2016
prag|29|female|science |2018
tom|26|male|arts |2017
john|27|female|sports |2018
simon|22|male|arts |2018
Output file (output.xlsx attached ) should be saved in excel file as below in corresponding cell :-
Name age gender department year
don 23 male computer 2010
ptalakes 24 female maths 2015
chang 28 male arts 2016
prag 29 female science 2018
tom 26 male arts 2017
john 27 female sports 2018
simon 22 male arts 2018
Would appreciate if some one could please help me to get complete code in vbscript.
Thanks in advance
Praveen
Input.txt
output.xlsx
Here is example of my text input file :-
input.txt (attached for reference)
Name|age|gender|department
don|23|male|computer|2010
ptalakes|24|female|maths |2015
chang|28|male|arts |2016
prag|29|female|science |2018
tom|26|male|arts |2017
john|27|female|sports |2018
simon|22|male|arts |2018
Output file (output.xlsx attached ) should be saved in excel file as below in corresponding cell :-
Name age gender department year
don 23 male computer 2010
ptalakes 24 female maths 2015
chang 28 male arts 2016
prag 29 female science 2018
tom 26 male arts 2017
john 27 female sports 2018
simon 22 male arts 2018
Would appreciate if some one could please help me to get complete code in vbscript.
Thanks in advance
Praveen
Input.txt
output.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Welcome, glad that helped.
»bp
»bp
ASKER
Hi Bill , Need one more help !
My input file (attached Input-with null string.txt) has been populated with Null values in several places . Need your help to handle null values in the code .
For example, age field has null values in several places. Would appreciate if you could please suggest some remedy to handle in your previous code.
Name|age|gender|department
don||male|computer|2010
ptalakes||female|maths |2015
chang|28|male|arts |2016
prag|29|female|science |2018
tom|26|male|arts |2017
john|27|female|sports |2018
simon|22|male|arts |2018
My input file (attached Input-with null string.txt) has been populated with Null values in several places . Need your help to handle null values in the code .
For example, age field has null values in several places. Would appreciate if you could please suggest some remedy to handle in your previous code.
Name|age|gender|department
don||male|computer|2010
ptalakes||female|maths |2015
chang|28|male|arts |2016
prag|29|female|science |2018
tom|26|male|arts |2017
john|27|female|sports |2018
simon|22|male|arts |2018
By null values do you mean blank values, or are there actually "nulls" in the TXT file (I assume possible 0x00 values? Need a sample of the actual input file you have this situation with to better propose solution...
»bp
»bp
ASKER
Thanks ! I am able to resolve the Null values now but need need another help !
With reference of your VBScript code cscript EE29122716.vbs, I am trying to insert a default value in an output excel file .
Excel file has to be generated with some additional default "hard coded "values in second row just underneath the header . Please refer the attached output file .
Would appreciate if you kindly suggest a solution for this .
Please find attached the sample Output and Input files .
Input File :-
Row Number|Row Action|Department|Year
123456|Insert|Airlines|201
3456|Update|Engineering|20
234234|Insert|Arts|2015
452435|Insert|Design|2016
Thanks in advance
kp
OutPut-File.xlsx
Input-file.txt
outputfile-imagefile.PNG
With reference of your VBScript code cscript EE29122716.vbs, I am trying to insert a default value in an output excel file .
Excel file has to be generated with some additional default "hard coded "values in second row just underneath the header . Please refer the attached output file .
Would appreciate if you kindly suggest a solution for this .
Please find attached the sample Output and Input files .
Input File :-
Row Number|Row Action|Department|Year
123456|Insert|Airlines|201
3456|Update|Engineering|20
234234|Insert|Arts|2015
452435|Insert|Design|2016
Thanks in advance
kp
OutPut-File.xlsx
Input-file.txt
outputfile-imagefile.PNG
Here is one approach for that...
»bp
' Define constants
Const cExcel7 = 51
Const xlContinuous = 1
Const xlEdgeBottom = 9
Const xlEdgeLeft = 7
Const xlEdgeRight = 10
Const xlEdgeTop = 8
' Create file system object
Set objFSO = CreateObject("Scripting.FilesystemObject")
' Files to work woth
strInputFile = objFSO.GetAbsolutePathname("input.txt")
strOutputFile = objFSO.GetAbsolutePathname("output.xlsx")
' Read text file into array
With objFSO.OpenTextFile(strInputFile, 1)
arrInput = Split(.ReadAll, vbNewLine)
End With
' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
' Initialize row index
intRow = 0
' Process each line of input file
For Each strInput in arrInput
' Skip all blank lines
If strInput <> "" Then
' Start a new row in Excel, start at first column
intRow = intRow + 1
intCol = 0
' Parse input text line
arrTokens = Split(strInput, "|")
' Add each value to Excel sheet
For Each strToken In arrTokens
intCol = intCol + 1
With objSheet.Cells(intRow, intCol)
.Value = Trim(strToken)
' Bold first row
If intRow = 1 Then
.Font.Bold = True
End If
' Borders on all cells
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
' If header row, add hard coded row 2
If intRow = 1 Then
intRow = intRow + 1
objSheet.Cells(intRow, 1).Value = "Investigation.inv_r_a.true.string"
objSheet.Cells(intRow, 2).Value = "Investigation.inv_r_a.true.string"
objSheet.Cells(intRow, 3).Value = "Investigation.inv_r_a.false.string"
objSheet.Cells(intRow, 4).Value = "Case.inv_w_p.true.date"
End If
Next
End If
Next
' Write file and close Excel
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strOutputFile, cExcel7
objExcel.ActiveWorkbook.Close False
objExcel.Quit»bp
ASKER
Thanks Bill !
Cell (1,1) coming with the expected result , however, rest of the headers (Row Action, Department, Year) have moved to second row and all hard coded values are not printing ..
Output for this code as below :-
Row Number
Investigation.inv_r_a.true
123456 Insert Airlines 2016
3456 Update Engineering 2014
234234 Insert Arts 2015
452435 Insert Design 2016
Desired Output is as follow :-
Row Number Row Action Department Year
Investigation.inv_r_a.true
123456 Insert Airlines 2016
3456 Update Engineering 2014
234234 Insert Arts 2015
452435 Insert Design 2016
Would be great help if you could please suggest to get the desired result.
Thanks and Regards
KP
Cell (1,1) coming with the expected result , however, rest of the headers (Row Action, Department, Year) have moved to second row and all hard coded values are not printing ..
Output for this code as below :-
Row Number
Investigation.inv_r_a.true
123456 Insert Airlines 2016
3456 Update Engineering 2014
234234 Insert Arts 2015
452435 Insert Design 2016
Desired Output is as follow :-
Row Number Row Action Department Year
Investigation.inv_r_a.true
123456 Insert Airlines 2016
3456 Update Engineering 2014
234234 Insert Arts 2015
452435 Insert Design 2016
Would be great help if you could please suggest to get the desired result.
Thanks and Regards
KP
Sorry, added that code a bit too early in the looping, try this.
»bp
' Define constants
Const cExcel7 = 51
Const xlContinuous = 1
Const xlEdgeBottom = 9
Const xlEdgeLeft = 7
Const xlEdgeRight = 10
Const xlEdgeTop = 8
' Create file system object
Set objFSO = CreateObject("Scripting.FilesystemObject")
' Files to work woth
strInputFile = objFSO.GetAbsolutePathname("input.txt")
strOutputFile = objFSO.GetAbsolutePathname("output.xlsx")
' Read text file into array
With objFSO.OpenTextFile(strInputFile, 1)
arrInput = Split(.ReadAll, vbNewLine)
End With
' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
' Initialize row index
intRow = 0
' Process each line of input file
For Each strInput in arrInput
' Skip all blank lines
If strInput <> "" Then
' Start a new row in Excel, start at first column
intRow = intRow + 1
intCol = 0
' Parse input text line
arrTokens = Split(strInput, "|")
' Add each value to Excel sheet
For Each strToken In arrTokens
intCol = intCol + 1
With objSheet.Cells(intRow, intCol)
.Value = Trim(strToken)
' Bold first row
If intRow = 1 Then
.Font.Bold = True
End If
' Borders on all cells
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
Next
' If header row, add hard coded row 2
If intRow = 1 Then
intRow = intRow + 1
objSheet.Cells(intRow, 1).Value = "Investigation.inv_r_a.true.string"
objSheet.Cells(intRow, 2).Value = "Investigation.inv_r_a.true.string"
objSheet.Cells(intRow, 3).Value = "Investigation.inv_r_a.false.string"
objSheet.Cells(intRow, 4).Value = "Case.inv_w_p.true.date"
End If
End If
Next
' Write file and close Excel
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs strOutputFile, cExcel7
objExcel.ActiveWorkbook.Close False
objExcel.Quit»bp
ASKER