Eitel Dagnin
asked on
VBS - Convert Tab Delimited CSV File to XLSX
Originally posted here:
https://stackoverflow.com/questions/50329940/vbs-convert-tab-delimited-csv-file-to-xlsx
I have already posted a similar question before which converts CSV files to xlsx.
This question can be found here:
https://stackoverflow.com/questions/49294151/vbs-loop-through-multiple-csv-files-in-a-folder-and-convert-the-files-to-xls
Code: VBS
The issue is that the above does not work on the current CSV files I need to convert to xlsx..
From what I can see, the above code reads the CSV file as comma delimited, but the files I currently use are tab delimited.
Below is a screenshot of the output of the code and the expected output:
Row 1: Current output
Row 2: Expected output
The below code will convert a tab delimited csv file into an xlsx file:
Link to where I found the answer:
https://www.experts-exchange.com/questions/29028367/CSV-to-Excel-specific-delimiter-Vbscript.html
This code works like a dream, but now I seem to be having a different issue..
it would appear as though the data is separated by tabs, however, the data is not "formatted" correctly..
Here is the current output:
Columns A - G:
Columns H - T:
Columns U - Z:
As you can see from the above, the data is so to say, misplaced..
I have attached a sample workbook for reference as well - In the workbook is the current output and the expected output.
For reference; Here is a macro I recorded in excel of how the import is supposed to happen - Not sure if this can be used to help answer the question:
Code: VBA
https://stackoverflow.com/questions/50329940/vbs-convert-tab-delimited-csv-file-to-xlsx
I have already posted a similar question before which converts CSV files to xlsx.
This question can be found here:
https://stackoverflow.com/questions/49294151/vbs-loop-through-multiple-csv-files-in-a-folder-and-convert-the-files-to-xls
Code: VBS
'Constants
Const xlOpenXMLWorkbook = 51 '(without macro's in 2007-2016, xlsx)
Const xlOpenXMLWorkbookMacroEnabled = 52 '(with or without macro's in 2007-2016, xlsm)
Const xlExcel12 = 50 '(Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
Const xlExcel8 =56 '(97-2003 format in Excel 2007-2016, xls)
' Extensions for old and new files
strExcel = "xlsx"
strCSV = "csv"
strXLS = "xls"
' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")
strFolder = "B:\EE\EE29088597\Files"
' Access the folder to process
Set objFolder = objFSO.GetFolder(strFolder)
' Load Excel (hidden) for conversions
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts = False
' Process all files
For Each objFile In objFolder.Files
' Get full path to file
strPath = objFile.Path
' Only convert CSV files
If LCase(objFSO.GetExtensionName(strPath)) = LCase(strCSV) Or LCase(objFSO.GetExtensionName(strPath)) = LCase(strXLS) Then
' Display to console each file being converted
Wscript.Echo "Converting """ & strPath & """"
' Load CSV into Excel and save as native Excel file
Set objWorkbook = objExcel.Workbooks.Open(strPath, False, True)
strNewPath = objFSO.GetParentFolderName(strPath) & "\" & objFSO.GetBaseName(strPath) & "." & strExcel
objWorkbook.SaveAs strNewPath, xlOpenXMLWorkbook
objWorkbook.Close False
Set objWorkbook = Nothing
End If
Next
'Wrap up
objExcel.Quit
Set objExcel = Nothing
Set objFSO = Nothing
The issue is that the above does not work on the current CSV files I need to convert to xlsx..
From what I can see, the above code reads the CSV file as comma delimited, but the files I currently use are tab delimited.
Below is a screenshot of the output of the code and the expected output:
Row 1: Current output
Row 2: Expected output
The below code will convert a tab delimited csv file into an xlsx file:
Const xlTextQualifierDoubleQuote = 1
Const xlInsertDeleteCells = 1
Const xlDelimited = 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.DisplayAlerts= False
strCSVfile = Wscript.Arguments(0) & "\input.csv"
strXLSfile = Wscript.Arguments(0) & "\input.xlsx"
Set objWorkbook = objExcel.Workbooks.Add
Set objWorkSheet = objWorkbook.Worksheets(1)
With objWorkSheet.QueryTables.Add("TEXT;" & strCSVfile, objWorkSheet.Range("$A$1"))
.Name = "input"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "#"
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh False
End With
'Save Spreadsheet, 51 = Excel 2007-2010
objWorkSheet.SaveAs strXLSfile, 51
'Release Lock on Spreadsheet
objExcel.Quit()
Set ObjExcel = Nothing
Link to where I found the answer:
https://www.experts-exchange.com/questions/29028367/CSV-to-Excel-specific-delimiter-Vbscript.html
This code works like a dream, but now I seem to be having a different issue..
it would appear as though the data is separated by tabs, however, the data is not "formatted" correctly..
Here is the current output:
Columns A - G:
Columns H - T:
Columns U - Z:
As you can see from the above, the data is so to say, misplaced..
I have attached a sample workbook for reference as well - In the workbook is the current output and the expected output.
For reference; Here is a macro I recorded in excel of how the import is supposed to happen - Not sure if this can be used to help answer the question:
Code: VBA
Sub Import_CSV()
'
' Import_CSV Macro
'
'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Location\test.csv", Destination _
:=Range("$A$1"))
.CommandType = 0
.Name = "test"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
Sample.xlsx
ASKER
Hi Bill, I would like to just upload the original document but there is too much sensitive information in the document to upload or otherwise distribute outside of the company network.
I tried editing the original document simply with dummy details in the place of the actual data, but the spacing is off.
When I move my cursor to the position I want to edit the data, it places the cursor directly over the letter or number (almost like a vertical strike-through) and when attempt to edit, my cursor seems to be a few spaces in front of or behind the place I can see it at.
It really is very confusing to explain but Im not sure how else I would be able to provide you with the information...
I tried editing the original document simply with dummy details in the place of the actual data, but the spacing is off.
When I move my cursor to the position I want to edit the data, it places the cursor directly over the letter or number (almost like a vertical strike-through) and when attempt to edit, my cursor seems to be a few spaces in front of or behind the place I can see it at.
It really is very confusing to explain but Im not sure how else I would be able to provide you with the information...
Okay, I appreciate it contains sensitive data. Based on what you are describing it sounds like it may be an "interesting" file so without seeing it I don't think I can help.
Is there any possibility you can adjust at the source of the file, so that it is a more standard format that Excel can consume? That might be easier than correcting the "problem" downstream when importing into Excel.
I'm sure other experts will be along that may want to suggest code for you to test.
»bp
Is there any possibility you can adjust at the source of the file, so that it is a more standard format that Excel can consume? That might be easier than correcting the "problem" downstream when importing into Excel.
I'm sure other experts will be along that may want to suggest code for you to test.
»bp
ASKER
Hi Bill,
Thank you again for the reply.. I do hope someone will be able to provide me with a code sample to test..
I have looked at the original sheet again and I see the "headers" are also formatted the same way as the body of the data. So what I have done is removed the actual data and left only the first few lines in the csv file. This file I have uploaded to see if it could help.
Report.csv
Thank you again for the reply.. I do hope someone will be able to provide me with a code sample to test..
I have looked at the original sheet again and I see the "headers" are also formatted the same way as the body of the data. So what I have done is removed the actual data and left only the first few lines in the csv file. This file I have uploaded to see if it could help.
Report.csv
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you found comments here helpful in getting to your solution I would approach as follows. Select you last comment ID:42566032 as the solution. Then select one or more of the expert comments that you deem useful as assisting (or I think it is called maybe helpful now?).
Here's a link that may also help...
»bp
Here's a link that may also help...
»bp
ASKER
Original data file was corrupt.
»bp