Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have approximately 9,000 columns (goes to column MGN) which have 14 rows of data points. I need to get the data in a single column with the format Column B below A, C below B, etc.

Is there any macro or task I can execute so it can be done in one go? Any help would be appreciated.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

If I understand correctly, then you get a result of 126 000 lines, or slightly more than 2,500 pages of A4 ... If you need to analyze it in person, the idea of ​​a single column is not very good.

ex:

xls2col2.vbs Book 1.xlsx

Or drag&drop excel file to vbscript:

drag&drop excel file to vbscript

use Book 1.txt

xls2col2.vbs:

If WScript.Arguments.Count <1 then 
   Wscript.Echo "Ex.1: cscript xls2col.vbs filename.xls"
   Wscript.Echo "Ex.2: cscript xls2col.vbs filename.xlsx"
   Wscript.Quit
End If

Const OpenAsDefault = -2
Const FailIfNotExist = 0
Const ForReading = 1
Const ForWriting = 2

Const xlTextWindows=20
Const xlUnicodeText=42   
Const ExtName = ".txt"

Dim ConvertExt:ConvertExt = xlTextWindows

Dim strFilePath:strFilePath = WScript.Arguments.Item(0) 

Dim FSO:Set FSO = CreateObject("Scripting.FileSystemObject")
    If FSO.FileExists(strFilePath) Then

Dim FileProperties:Set FileProperties=FSO.GetFile(strFilePath)
    Dim strOpenFileName : strOpenFileName = FileProperties.Path
    Dim FileConvertName : FileConvertName = FSO.BuildPath(FileProperties.ParentFolder, FSO.GetBaseName(FileProperties) & ExtName)

Dim ExcelApplication:Set ExcelApplication = CreateObject("Excel.Application")
ExcelApplication.Workbooks.Open(strOpenFileName)

ExcelApplication.DisplayAlerts = FALSE
ExcelApplication.Visible = FALSE

ExcelApplication.Workbooks(1).SaveAs FileConvertName, ConvertExt
ExcelApplication.Quit

WScript.Sleep 5000

Set FSO = CreateObject("Scripting.FileSystemObject")
Dim file: Set file = FSO.OpenTextFile(FileConvertName, ForReading, FailIfNotExist, OpenAsDefault)

Dim FileReadAll: FileReadAll = file.ReadAll
file.Close
FileReadAll = Replace(FileReadAll, Chr(9), Chr(13)&Chr(10))

Set file = FSO.OpenTextFile(FileConvertName, ForWriting, True)
file.Write(FileReadAll)
file.Close

Else
      WScript.Echo "File Open Error: file not exist!"
      WScript.Quit
End If

This script saves your file first in the text, and then it converts the text in it in one column. If it is needed it is in excel format, open the text file and resave, but I do not see the need.

EViews 7 txt file:

EViews 7 txt file

share|improve this answer
    
That would be correct. I would have close to 126,000 rows solely in column A. The data is being imported to EViews 7 so I am formatting it for panel data. –  nvdkgm Mar 16 '13 at 16:51
    
@nvdkgm ok, see and test script –  STTR Mar 16 '13 at 20:01
    
Appreciate the help and the screenshots. –  nvdkgm Mar 17 '13 at 6:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.