0

I want to open the CSV file using powershell Excel.Application. my code is like this:

$csv = "csv name"
$xlsx = "output excel name"
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$wb = $excel.Workbook.Open($csv)
$wb.SaveAs($xlsx,51)
$excel.Quit()

But Turns out that the data in the csv "004" will loaded as 4

Anyone can think of a way to do this?

Noted that there are many special case in my csv:

  1. there are data like "004", "01234678" in the csv and I would like to import all of them as text.
  2. there are comma within the data like "FlatA, 7/F"
  3. there are newline character within the data like "abcdef def ghi" you can also give your own solution that can load the csv to excel using powershell which can fulfill all the above cases.

Thanks a lot. You will save my life if you able to do this.

MasterBeast64
  • 11
  • 1
  • 3
  • Excel autoformats data a lot, including trimming of leading zeroes and converting values to dates. This can't be disabled by any setting, so a workaround is tricky. Maybe the best way is to use [Open XML SDK library](https://stackoverflow.com/q/151005/503046) to write the file? – vonPryz Nov 16 '21 at 06:08
  • Thanks @vonPryz. As we are working around in the our customers machine so we cannot install the required software of lib to the machine. But if that is the only solution, we will propose to them. Thank a lot again. – MasterBeast64 Nov 16 '21 at 06:32
  • Hi, If it is only a CSV file you may try with Get-Content. – Dilly B Nov 16 '21 at 07:10
  • If you prefix those numbers with a TAB character in the csv, Excel won't convert them but treat them as text. Of course, make sure each field is surrounded by double-quotes – Theo Nov 16 '21 at 08:29
  • @Theo Your idea let me learn new things but thats not the solution because we need to hand in to the MS D365 to do the migration and the \t is not acceptable. But thanks, that is really interesting discovery for me – MasterBeast64 Nov 16 '21 at 12:53
  • 1
    @DillyB can you explain more about your idea? – MasterBeast64 Nov 16 '21 at 12:53
  • Could be please elaborate what are you trying to achieve with your sample data and the expected output? Get-Content can read CSV files as it is, so it is better to give a try :) – Dilly B Nov 16 '21 at 13:05
  • @DillyB what we have is bunch of csv, where the biggest file will have 1,000,000-1,200,000 rows of record, so looping the csv and put the value cell by cell is not possible for us cos it is time coonsuming. so we want to use the direct open or copy and paste data to the excel (because we are going to migrate to D365 and those ppl said they only accept excel file and need to be the correct data and format.) So do you know how to convert csv to excel without looping the file using powershell and it can satisfy below cases – MasterBeast64 Nov 17 '21 at 01:54
  • 1. for row having '003', treat the column as string 2. for '2021-03-27', treat them as date with format dd/mm/yyyy 3. handle the cell with comma inside such as 'Flat A, 10/F, 123' 4. handle the cell with new line character, i.e. "abc new line def" – MasterBeast64 Nov 17 '21 at 01:54
  • What works for us is prepending the columns we want want excel to interprete as text with a a tab character. Drawback offcourse is that you are actually changing data so you have to decide if that would work for you. Your workflow could then be 1. Import-Csv; 2. Loop over all data and change data to tab+data for given columns; 3. Export-Csv; 4. Your code to open and save as xlsx; – Lieven Keersmaekers Nov 17 '21 at 07:00
  • Hi, May be you can start with the script here. https://community.spiceworks.com/topic/2177239-convert-csv-to-excel-and-if-a-leading-zero-exist-change-column-data-type-to-text – Dilly B Nov 17 '21 at 08:25

0 Answers0