Import Text file data to Excel

Question

I have a text file that contains below data:

1,Name1,Age1,City1
2,Name2,Age2,City2
3,Name3,Age3,City3

I want this to import in an Excel file, for example, the starting cell will be "B1".

I have a script that can only update a single cell in the Excel file but I don't know what is the right approach if it will be some texts delimited by comma.

Edit: I forgot that I have a working code on this using VB but I need a help on converting it to Powershell.

Dim objExcel As object
Dim objSheet As object
Dim worksheet As string
Dim txt as string
Dim connectionName as string

worksheet = "Sheet1"
txt = "c:\test.txt"
objExcel =  New Excel.Application
Dim objWB As Excel.Workbooks = objExcel.Workbooks
objWB.Open("c:\excelfile.xlsx",IgnoreReadOnlyRecommended:=True)
objSheet = objExcel.ActiveWorkbook.Worksheets(worksheet)
connectionName = "TEXT;" + txt
With objSheet.QueryTables.Add(Connection:=connectionName, Destination:=objSheet.Range("B1"))
        .Name = txt
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = 0 'xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = 1 'xlDelimited
        .TextFileTextQualifier = 1 'xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .Refresh (BackgroundQuery:=False)
    End With

objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.Save()
objExcel.ActiveWorkbook.Close()

System.Runtime.InteropServices.Marshal.FinalReleaseComObject (objWB)
objExcel.quit()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject (objExcel)
objWB = Nothing
objExcel = Nothing
System.Threading.Thread.Sleep (2000)
GC.Collect()

Start with recording the desired actions as a macro, then translate the macro to PowerShell. Come back when you have a specific question about something you can't get to work. As it stands your question is too broad, since your existing code has nothing to do with the problem you want to solve. — Ansgar Wiechers
I already referred you to the blog post where I outline things to observe when translating VBA to PowerShell. What particular problem do you need help with? — Ansgar Wiechers

Mathias R. Jessen Mathias R. Jessen · Accepted Answer · 2016-01-05T10:41:06

I don't know what is the right approach if it will be some texts delimited by comma.

Such a text file format is commonly known as CSV (Comma-Separated Values).

Fortunately, PowerShell comes with an arsenal of CSV parsing tools, including Import-CSV:

$Lines = Import-Csv C:\my\textfile.txt -Header 'ID','Name','Age','City'

Now, $Lines is an array of objects representing each row in the text file. Each object will have properties named ID, Name, Age and City that you can use to grab the different values:

foreach($Line in $Lines)
{
    '{0} from {1} is {2} years old' -f $Line.Name,$Line.City,$Line.Age
}

Now all you need to do is substitute the foreach loop with a for loop and start populating your excel worksheet!

Import Text file data to Excel

1 Answers