Hi, I want import csv file to excel, Currently I am reading csv file and writing it directly to excel but if I import it from excel directly the format is changing, any leads
Hi,
Can you elaborate with specific sample and/or screenshot etc?
Regards,
this the sample csv data
num|name|abc|def|fgh|sec|ry|rty|ryt|qwe|asd|zxc|yhu|fvb|oly|
123|SDA312|123.1|123.11|2022-06-09|0|bb 12|adasd|sdf||2023-08-14|0.000000|984.399700|||
You can use the “Excel Application Scope” activity along with the “Write Range” activity to import the CSV data into Excel with better control over the format. You can use the activities:
- Excel Application Scope
- Read CSV
- Write Range
But with using import text/csv in excel gives cell format, so I thought of import the file through code
Public Class ExcelImport
Public Shared Sub ImportTextCSV(filePath As String, sheetName As String)
’ Create Excel application
Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
' Make Excel visible for demonstration purposes
excelApp.Visible = True
' Open a new workbook
Dim workbook As Microsoft.Office.Interop.Excel.Workbook = excelApp.Workbooks.Add()
' Add a new worksheet
Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(workbook.Worksheets.Add(), Microsoft.Office.Interop.Excel.Worksheet)
worksheet.Name = sheetName
' Import the text/CSV file with "|" as the delimiter
With worksheet.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=worksheet.Range("A1"))
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileCommaDelimiter = False
.TextFileOtherDelimiter = "|" ' Set the custom delimiter
.Refresh()
End With
End Sub
End Class
- this is my code for reference
Hi @quick_123
You can use the below code in Invoke VBA.
Sub ImportTextFile()
' Disable Excel visibility
Application.Visible = False
' Open a new workbook
Dim workbook As Workbook
Set workbook = Workbooks.Add()
' Add a new worksheet
Dim worksheet As Worksheet
Set worksheet = workbook.Worksheets.Add()
worksheet.Name = "YourSheetName"
' Import the text/CSV file with "|" as the delimiter
With worksheet.QueryTables.Add(Connection:="TEXT;C:\Your\FilePath\YourFile.txt", Destination:=worksheet.Range("A1"))
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileCommaDelimiter = False
.TextFileOtherDelimiter = "|"
.Refresh
End With
' Enable Excel visibility after the import
Application.Visible = False
End Sub
Paste the above code in text file and Entry Method name is ImportTextFile. You can follow the below workflow and create your workflow. Pass the excel file in Use Excel file.
Hope it helps!!
My MS Excel is 2016 version
I have enabled trust access to VBA but still I am getting the below error
yes, I did pass the correct function name
input.zip (240 Bytes)
This is masked data of input file
Hi @quick_123
You can use the below code:
Sub ImportTextFile()
Dim filePath As String
Dim delimiter As String
Dim ws As Worksheet
' Set the file path and delimiter
filePath = "C:\Users\spputhucode\Downloads\input.csv" 'Replace with your file path
delimiter = "|"
' Create a new worksheet
Set ws = Worksheets.Add
' Import data from text file
With ws.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=ws.Range("A1"))
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFilePlatform = xlWindows
.TextFileParseType = xlDelimited
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileOtherDelimiter = delimiter
.Refresh
End With
End Sub
In filePath pass the Input CSV path and this should work for you.
Input:
Output:
Workflow:
Hope it helps!!
Still the same Macro problem
Invoke VBA: In order for this activity to work, Trust Access to the VBA project object model must be enabled from Excel (File > Options > Trust Center > Trust Center Settings > Macro Settings > Select the Trust Access to the VBA project object model check box)",
input.zip (240 Bytes)
test vbs.xaml (11.3 KB)
Here is the xaml
Hi @quick_123 ,
We can use VB code to convert CSV to excel
You can see
"
Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkbook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorksheet As Microsoft.Office.Interop.Excel.Worksheet
Dim xlRange As Microsoft.Office.Interop.Excel.Range
xlApp = New Microsoft.Office.Interop.Excel.ApplicationClass
Try
xlWorkbook = xlApp.Workbooks.Open(in_SourceFilePath)
xlWorkbook.SaveAs(in_DistinationFilePath, Microsoft.Office.Interop.Excel.XLFileFormat.xlOpenXMLWorkbook)
xlWorkbook.Close
Catch ex As Exception
Throw ex
Finally
xlApp.Quit
End Try
"
Hope it help,
Anil_G
sonaliaggarwal47
ashokkarale
sharazkm32
Jon_Smith
Bal_Son
Nitesh
Marielle_Timajo_Apay_NCS
adi.mehare
Youri98