Support Portal

for ProcessRobot and WinAutomation

Start a new topic
Answered

Convert Excel cell to custom value

Hello, 


We have an integration csv that when we convert to Excel drops the leading zeros in one of our cells. We need the full formate (ex 000043 not 43) because we are inserting that into our ERP system. Is there a way to convert the format of that cell to custom and enter in the appropriate amount of chars? 


Best Answer

James,


I am using powershell to convert csv to xlsx.

The trick is we write all column data as text format.

xlTextFormat2Text.


Ref : https://docs.microsoft.com/en-us/office/vba/api/excel.xlcolumndatatype

  

$csv = 'E:\My Document\Power Shell\Csv\Book1.csv'
$xlsx = 'E:\My Document\Power Shell\Csv\Book1.xlsx'
$delimiter = "," #Specify the delimiter used in the file

# Create a new Excel workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

# Build the QueryTables.Add command and reformat the data
$TxtConnector = ("TEXT;" + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

# Execute & delete the import query
$query.Refresh()
$query.Delete()

# Save & close the Workbook as XLSX.
$Workbook.SaveAs($xlsx,51)
$excel.Quit()

  

Ps :


- Make sure your csv data does not contain NewLine

1 Comment

Answer

James,


I am using powershell to convert csv to xlsx.

The trick is we write all column data as text format.

xlTextFormat2Text.


Ref : https://docs.microsoft.com/en-us/office/vba/api/excel.xlcolumndatatype

  

$csv = 'E:\My Document\Power Shell\Csv\Book1.csv'
$xlsx = 'E:\My Document\Power Shell\Csv\Book1.xlsx'
$delimiter = "," #Specify the delimiter used in the file

# Create a new Excel workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

# Build the QueryTables.Add command and reformat the data
$TxtConnector = ("TEXT;" + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

# Execute & delete the import query
$query.Refresh()
$query.Delete()

# Save & close the Workbook as XLSX.
$Workbook.SaveAs($xlsx,51)
$excel.Quit()

  

Ps :


- Make sure your csv data does not contain NewLine

Login or Signup to post a comment