0

I am trying to import a tab delimited file into excel using powershell. Some of my numbers are very long and I need to import them as text to preserve them. I made an Excel Macro of the process and I want to incorporate the "Preserve Formatting" parameter. Everything in the macro from .TextFileStartRow = 1 till the end is represented in the powershell script but earlier parameters are omitted.

How do I communicate to skip those parameters? Is there a way to preserve the format using the [blahblah.interop.excel]::____ format and insert one line in?

Below is my powershell and further down is a snippet from the macro

$thing = "C:\Users\michaelw\Desktop\Nasty 19\Testfile.txt" #where the original files are $excel = New-Object -ComObject Excel.Application Add-Type -AssemblyName Microsoft.Office.Interop.Excel $excel.Visible = $true $excel.DisplayAlerts = $false $wb = $excel.Workbooks.OpenText( $thing, # file to open [Microsoft.Office.Interop.Excel.XlPlatform]::xlWindows, 1, # start from row 1 [Microsoft.Office.Interop.Excel.XlTextParsingType]::xlDelimited, [Microsoft.Office.Interop.Excel.XlTextQualifier]::xlTextQualifierDoubleQuote, $false, # Consecutive Delimiter $true, # tab $false, # semicolon $false, # comma $false, # space $false, # use other '|') 
With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;C:\Users\michaelw\Desktop\Nasty 19\Testfile.txt", _ Destination:=Range("$A$1")) .CommandType = 0 .Name = "Testfile" .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(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With
Improve this question

1 Answer 1

Reset to default
1

This is working in one of my scripts...

$dataSheet = $wbObj.Worksheets.Item(1) $dataSheet.Name = "Data" $qryConn = ("TEXT;" + $csvPath) $qryDest = $dataSheet.Range("A1") $conn = $dataSheet.QueryTables.Add($qryConn,$qryDest) $dataSheet.QueryTables.item($conn.name).TextFileCommaDelimiter = $true $dataSheet.QueryTables.item($conn.name).TextFileParseType = 1 $dataSheet.QueryTables.item($conn.name).FillAdjacentFormulas = $true $dataSheet.QueryTables.item($conn.name).TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2) [void]$dataSheet.QueryTables.item($conn.name).Refresh()

I'd think you could replace TextFileCommaDelimiter with TextFileTabDelimiter and make it work. Your best bet is to try and do it piece mail rather than all on one line.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

It imported the files but the cells were in the "general" format (not the "text" format and it still turned long numbers into scientific notation. I even added this line. What am I doing wrong? $dataSheet.QueryTables.item($conn.name).preserveformatting = 1
I'm guessing you want to bring in the line with TextFileColumnDataTypes...?
I later tried putting this in but it didn't work. Do I need to specify each column or is there a way to say do this for all columns? $dataSheet.QueryTables.item($conn.name).textfilecolumndatatypes = "xlTextFormat"
I think you need to set it to the value of the xlTextFormat constant, which I think is 2. This might help a bit: blogs.technet.com/b/heyscriptingguy/archive/2008/06/27/…
I added a line showing how I think it should work. Give it a try and let me know.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.