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

0

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:

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

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

excel powershell csv formatting

share|improve this question

asked Nov 19 '18 at 13:46

Rakha

463316

• 
  
  
  

  
  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 '18 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 '18 at 16:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks guys i'll use the module!
  – Rakha
  Nov 19 '18 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 '18 at 22:36
  

  
  
  
  

add a comment | 

0

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:

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

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

excel powershell csv formatting

share|improve this question

asked Nov 19 '18 at 13:46

Rakha

463316

• 
  
  
  

  
  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 '18 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 '18 at 16:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks guys i'll use the module!
  – Rakha
  Nov 19 '18 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 '18 at 22:36
  

  
  
  
  

add a comment | 

0

0

0

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:

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

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

excel powershell csv formatting

share|improve this question

asked Nov 19 '18 at 13:46

Rakha

463316

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:

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

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

excel powershell csv formatting

excel powershell csv formatting

share|improve this question

asked Nov 19 '18 at 13:46

Rakha

463316

share|improve this question

asked Nov 19 '18 at 13:46

Rakha

463316

share|improve this question

share|improve this question

asked Nov 19 '18 at 13:46

Rakha

463316

asked Nov 19 '18 at 13:46

Rakha

463316

asked Nov 19 '18 at 13:46

Rakha

463316

463316

• 
  
  
  

  
  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 '18 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 '18 at 16:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks guys i'll use the module!
  – Rakha
  Nov 19 '18 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 '18 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 '18 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 '18 at 16:03
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks guys i'll use the module!
  – Rakha
  Nov 19 '18 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 '18 at 22:36
  

  
  
  
  

1

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 '18 at 14:15

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 '18 at 14:15

1

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 '18 at 16:03

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 '18 at 16:03

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

Thanks guys i'll use the module!
– Rakha
Nov 19 '18 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 '18 at 22:36

@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 '18 at 22:36

add a comment | 

2 Answers
2

active

oldest

votes

1

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

share|improve this answer

answered Nov 19 '18 at 22:33

Mike Twc

1,061312

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

1

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()

share|improve this answer

edited Nov 20 '18 at 14:54

answered Nov 20 '18 at 13:40

Rakha

463316

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',
autoActivateHeartbeat: false,
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
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

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

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

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

Email

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

active

oldest

votes

active

oldest

votes

1

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

share|improve this answer

answered Nov 19 '18 at 22:33

Mike Twc

1,061312

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

1

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

share|improve this answer

answered Nov 19 '18 at 22:33

Mike Twc

1,061312

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

1

1

1

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

share|improve this answer

answered Nov 19 '18 at 22:33

Mike Twc

1,061312

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

share|improve this answer

answered Nov 19 '18 at 22:33

Mike Twc

1,061312

share|improve this answer

share|improve this answer

answered Nov 19 '18 at 22:33

Mike Twc

1,061312

answered Nov 19 '18 at 22:33

Mike Twc

1,061312

answered Nov 19 '18 at 22:33

Mike Twc

1,061312

1,061312

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

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

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

add a comment | 

1

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()

share|improve this answer

edited Nov 20 '18 at 14:54

answered Nov 20 '18 at 13:40

Rakha

463316

add a comment | 

1

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()

share|improve this answer

edited Nov 20 '18 at 14:54

answered Nov 20 '18 at 13:40

Rakha

463316

add a comment | 

1

1

1

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()

share|improve this answer

edited Nov 20 '18 at 14:54

answered Nov 20 '18 at 13:40

Rakha

463316

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()

share|improve this answer

edited Nov 20 '18 at 14:54

answered Nov 20 '18 at 13:40

Rakha

463316

share|improve this answer

share|improve this answer

edited Nov 20 '18 at 14:54

edited Nov 20 '18 at 14:54

edited Nov 20 '18 at 14:54

answered Nov 20 '18 at 13:40

Rakha

463316

answered Nov 20 '18 at 13:40

Rakha

463316

answered Nov 20 '18 at 13:40

Rakha

463316

463316

add a comment | 

add a comment | 

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

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

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

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

Email

Required, but never shown

Sign up or log in

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

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

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

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

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

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

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