Force open CSV with Column Data Format = TEXT in Excel (Powershell)

up vote
0
down vote

favorite

I have this CSV file I generate using Export-CSV. Everything is fine with it but it display like this when opening in Excel because the cells are not formatted as TEXT: enter image description here

I want to force open the CSV with the cells all set to TEXT like you can do manually with the interface.

enter image description here

Is there a way to automate that with PowerShell, opening the CSV in Excel with cells formatted as text?

asked Nov 19 at 13:46

Rakha

423116

1

Not sure if it will do what you want, but certainly worth taking a look at the ImportExcel module as it's the most featured module for Excel.
– James C.
Nov 19 at 14:15

1

you CANNOT format a CSV file [beyond the delimiter used]. that is part of the definition of a CSV file. [grin] to format the file, you will have to do that in the import step via COM automation, OR do as @JamesC. recommends and use one of the PoSh Excel modules that can generate an excel file instead of a CSV file.
– Lee_Dailey
Nov 19 at 16:03

Thanks guys i'll use the module!
– Rakha
Nov 19 at 20:13

@lee_daily, would it be possible to do it with com automation though ? Having the CSV opened with text formatting for cells ?
– Rakha
Nov 19 at 22:36

add a comment |

up vote
0
down vote

favorite

I have this CSV file I generate using Export-CSV. Everything is fine with it but it display like this when opening in Excel because the cells are not formatted as TEXT: enter image description here

I want to force open the CSV with the cells all set to TEXT like you can do manually with the interface.

enter image description here

Is there a way to automate that with PowerShell, opening the CSV in Excel with cells formatted as text?

asked Nov 19 at 13:46

Rakha

423116

1

Not sure if it will do what you want, but certainly worth taking a look at the ImportExcel module as it's the most featured module for Excel.
– James C.
Nov 19 at 14:15

1

you CANNOT format a CSV file [beyond the delimiter used]. that is part of the definition of a CSV file. [grin] to format the file, you will have to do that in the import step via COM automation, OR do as @JamesC. recommends and use one of the PoSh Excel modules that can generate an excel file instead of a CSV file.
– Lee_Dailey
Nov 19 at 16:03

Thanks guys i'll use the module!
– Rakha
Nov 19 at 20:13

@lee_daily, would it be possible to do it with com automation though ? Having the CSV opened with text formatting for cells ?
– Rakha
Nov 19 at 22:36

add a comment |

up vote
0
down vote

favorite

I have this CSV file I generate using Export-CSV. Everything is fine with it but it display like this when opening in Excel because the cells are not formatted as TEXT: enter image description here

I want to force open the CSV with the cells all set to TEXT like you can do manually with the interface.

enter image description here

Is there a way to automate that with PowerShell, opening the CSV in Excel with cells formatted as text?

asked Nov 19 at 13:46

Rakha

423116

I have this CSV file I generate using Export-CSV. Everything is fine with it but it display like this when opening in Excel because the cells are not formatted as TEXT: enter image description here

I want to force open the CSV with the cells all set to TEXT like you can do manually with the interface.

enter image description here

Is there a way to automate that with PowerShell, opening the CSV in Excel with cells formatted as text?

excel powershell csv formatting

asked Nov 19 at 13:46

Rakha

423116

asked Nov 19 at 13:46

Rakha

423116

asked Nov 19 at 13:46

Rakha

423116

asked Nov 19 at 13:46

Rakha

423116

asked Nov 19 at 13:46

Rakha

423116

1

Not sure if it will do what you want, but certainly worth taking a look at the ImportExcel module as it's the most featured module for Excel.
– James C.
Nov 19 at 14:15

1

you CANNOT format a CSV file [beyond the delimiter used]. that is part of the definition of a CSV file. [grin] to format the file, you will have to do that in the import step via COM automation, OR do as @JamesC. recommends and use one of the PoSh Excel modules that can generate an excel file instead of a CSV file.
– Lee_Dailey
Nov 19 at 16:03

Thanks guys i'll use the module!
– Rakha
Nov 19 at 20:13

@lee_daily, would it be possible to do it with com automation though ? Having the CSV opened with text formatting for cells ?
– Rakha
Nov 19 at 22:36

add a comment |

1

Not sure if it will do what you want, but certainly worth taking a look at the ImportExcel module as it's the most featured module for Excel.
– James C.
Nov 19 at 14:15

1

you CANNOT format a CSV file [beyond the delimiter used]. that is part of the definition of a CSV file. [grin] to format the file, you will have to do that in the import step via COM automation, OR do as @JamesC. recommends and use one of the PoSh Excel modules that can generate an excel file instead of a CSV file.
– Lee_Dailey
Nov 19 at 16:03

Thanks guys i'll use the module!
– Rakha
Nov 19 at 20:13

@lee_daily, would it be possible to do it with com automation though ? Having the CSV opened with text formatting for cells ?
– Rakha
Nov 19 at 22:36

Not sure if it will do what you want, but certainly worth taking a look at the ImportExcel module as it's the most featured module for Excel.
– James C.
Nov 19 at 14:15

you CANNOT format a CSV file [beyond the delimiter used]. that is part of the definition of a CSV file. [grin] to format the file, you will have to do that in the import step via COM automation, OR do as @JamesC. recommends and use one of the PoSh Excel modules that can generate an excel file instead of a CSV file.
– Lee_Dailey
Nov 19 at 16:03

Thanks guys i'll use the module!
– Rakha
Nov 19 at 20:13

@lee_daily, would it be possible to do it with com automation though ? Having the CSV opened with text formatting for cells ?
– Rakha
Nov 19 at 22:36

add a comment |

2 Answers
2

active

oldest

votes

up vote
1
down vote

There is a little trick you can use - convert your data to html, and save with "xls" extention. For example:

Get-Process | convertto-html | Out-File csv2.xls

You'll see a warning when opening it, just click OK.

You can suppress that warning message by adding extra key in registry:

open regedit

HKEY_CURRENT_USERSoftwareMicrosoftOffice15.0ExcelSecurity

Create a new DWORD with name ExtensionHardening and value 0

answered Nov 19 at 22:33

Mike Twc

54629

Thanks I'll try it tomorrow!
– Rakha
Nov 19 at 22:35

add a comment |

up vote
0
down vote

accepted

Found a very good way to make it happen!

After generating your CSV file, here is how to automatically load it into Excel with AutoFit column width and TEXT format for cells :) :

$Fichier = "PATH_TO_CSV.csv"

$objExcel = New-Object -ComObject Excel.Application

$WorkBook = $objExcel.Workbooks.Open($Fichier)

$WorkSheet = $WorkBook.worksheets.item(1)



$objExcel.Visible = $true



$Range = $worksheet.UsedRange.Cells

$range.NumberFormat = "@"



$WorkSheet.Columns("A:B").AutoFit()

edited Nov 20 at 14:54

answered Nov 20 at 13:40

Rakha

423116

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375995%2fforce-open-csv-with-column-data-format-text-in-excel-powershell%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
1
down vote

There is a little trick you can use - convert your data to html, and save with "xls" extention. For example:

Get-Process | convertto-html | Out-File csv2.xls

You'll see a warning when opening it, just click OK.

You can suppress that warning message by adding extra key in registry:

open regedit

HKEY_CURRENT_USERSoftwareMicrosoftOffice15.0ExcelSecurity

Create a new DWORD with name ExtensionHardening and value 0

answered Nov 19 at 22:33

Mike Twc

54629

Thanks I'll try it tomorrow!
– Rakha
Nov 19 at 22:35

add a comment |

up vote
1
down vote

There is a little trick you can use - convert your data to html, and save with "xls" extention. For example:

Get-Process | convertto-html | Out-File csv2.xls

You'll see a warning when opening it, just click OK.

You can suppress that warning message by adding extra key in registry:

open regedit

HKEY_CURRENT_USERSoftwareMicrosoftOffice15.0ExcelSecurity

Create a new DWORD with name ExtensionHardening and value 0

answered Nov 19 at 22:33

Mike Twc

54629

Thanks I'll try it tomorrow!
– Rakha
Nov 19 at 22:35

add a comment |

up vote
1
down vote

There is a little trick you can use - convert your data to html, and save with "xls" extention. For example:

Get-Process | convertto-html | Out-File csv2.xls

You'll see a warning when opening it, just click OK.

You can suppress that warning message by adding extra key in registry:

open regedit

HKEY_CURRENT_USERSoftwareMicrosoftOffice15.0ExcelSecurity

Create a new DWORD with name ExtensionHardening and value 0

answered Nov 19 at 22:33

Mike Twc

54629

There is a little trick you can use - convert your data to html, and save with "xls" extention. For example:

Get-Process | convertto-html | Out-File csv2.xls

You'll see a warning when opening it, just click OK.

You can suppress that warning message by adding extra key in registry:

open regedit

HKEY_CURRENT_USERSoftwareMicrosoftOffice15.0ExcelSecurity

Create a new DWORD with name ExtensionHardening and value 0

answered Nov 19 at 22:33

Mike Twc

54629

answered Nov 19 at 22:33

Mike Twc

54629

answered Nov 19 at 22:33

Mike Twc

54629

answered Nov 19 at 22:33

Mike Twc

54629

Thanks I'll try it tomorrow!
– Rakha
Nov 19 at 22:35

add a comment |

Thanks I'll try it tomorrow!
– Rakha
Nov 19 at 22:35

Thanks I'll try it tomorrow!
– Rakha
Nov 19 at 22:35

add a comment |

up vote
0
down vote

accepted

Found a very good way to make it happen!

After generating your CSV file, here is how to automatically load it into Excel with AutoFit column width and TEXT format for cells :) :

$Fichier = "PATH_TO_CSV.csv"

$objExcel = New-Object -ComObject Excel.Application

$WorkBook = $objExcel.Workbooks.Open($Fichier)

$WorkSheet = $WorkBook.worksheets.item(1)



$objExcel.Visible = $true



$Range = $worksheet.UsedRange.Cells

$range.NumberFormat = "@"



$WorkSheet.Columns("A:B").AutoFit()

edited Nov 20 at 14:54

answered Nov 20 at 13:40

Rakha

423116

add a comment |

up vote
0
down vote

accepted

Found a very good way to make it happen!

After generating your CSV file, here is how to automatically load it into Excel with AutoFit column width and TEXT format for cells :) :

$Fichier = "PATH_TO_CSV.csv"

$objExcel = New-Object -ComObject Excel.Application

$WorkBook = $objExcel.Workbooks.Open($Fichier)

$WorkSheet = $WorkBook.worksheets.item(1)



$objExcel.Visible = $true



$Range = $worksheet.UsedRange.Cells

$range.NumberFormat = "@"



$WorkSheet.Columns("A:B").AutoFit()

edited Nov 20 at 14:54

answered Nov 20 at 13:40

Rakha

423116

add a comment |

up vote
0
down vote

accepted

Found a very good way to make it happen!

After generating your CSV file, here is how to automatically load it into Excel with AutoFit column width and TEXT format for cells :) :

$Fichier = "PATH_TO_CSV.csv"

$objExcel = New-Object -ComObject Excel.Application

$WorkBook = $objExcel.Workbooks.Open($Fichier)

$WorkSheet = $WorkBook.worksheets.item(1)



$objExcel.Visible = $true



$Range = $worksheet.UsedRange.Cells

$range.NumberFormat = "@"



$WorkSheet.Columns("A:B").AutoFit()

edited Nov 20 at 14:54

answered Nov 20 at 13:40

Rakha

423116

Found a very good way to make it happen!

After generating your CSV file, here is how to automatically load it into Excel with AutoFit column width and TEXT format for cells :) :

$Fichier = "PATH_TO_CSV.csv"

$objExcel = New-Object -ComObject Excel.Application

$WorkBook = $objExcel.Workbooks.Open($Fichier)

$WorkSheet = $WorkBook.worksheets.item(1)



$objExcel.Visible = $true



$Range = $worksheet.UsedRange.Cells

$range.NumberFormat = "@"



$WorkSheet.Columns("A:B").AutoFit()

edited Nov 20 at 14:54

answered Nov 20 at 13:40

Rakha

423116

edited Nov 20 at 14:54

answered Nov 20 at 13:40

Rakha

423116

answered Nov 20 at 13:40

Rakha

423116

answered Nov 20 at 13:40

Rakha

423116

add a comment |

draft saved

draft discarded

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk