Adding new column to CSV Issue

Hi,

Steps I am doing -

  1. Read csv with delimeter tab (need to use tab as some issue with other delimeters) and store in DT
  2. Add three columns to DT
  3. Write csv with delimeter semicolon (because if other delimeters used then all column data is written in one column ony)

Issue -
What happens is new column added are appended to last column with ; separated

Image -

I want it to write to csv with separate columns like image
Please help with this!

Thanks in advance,
Mohini

Hi, you can split that column using text to columns. In this case, we can use a macro to do that. You can use the invoke VBA to inject the code to your file.

Code:

Sub test()

'Test
Range("AI1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("AI1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=";", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

End Sub

Hi, I tried this but my file is csv. And invoke VBA can work on xlsx files.
I tried writting file in xlsx but again I am getting all data in first column, also not proper in new lines else i would have used this code on first column.

can this code be used with csv file ?

Thanks

@mohininemade301094,

If you really need it in CSV you will have to use the data table method you are trying to accomplish. There must be something wrong which your workflow. You will need to find it. Invoke VBA will not work on CSV files, what you can do is make your stuff in a xlsx then copy the data table and paste it back but that’s not following the best practices. I think the issue that you’re having is that you’re trying to append. Try using for each row activity and inside of it use add row to make sure the info gets added to the build data table. If you didn’t use the build data table activity then that’s another something that you should include.

I find it strange that you use CSV (comma-separated values) with a tab delimiter. Actually, XLS is a tab-delimited file, while CSV is comma-delimited in most cases.

Anyway, I assume you use the Read CSV successfully.
Then, use 3 Add Data Column activities to the Data Table.
Finally, use a Write CSV and change the delimiter in the properties, if necessary.

If the columns are not separating when opening in Excel, then refer to some solutions here: How to correctly display .csv files within Excel 2013? - Stack Overflow

I would still recommend using the default comma-delimiter, because as I understand Excel will delimit it automatically (or should).

Hopefully, this helps.

Regards.

1 Like

The screenshots show you opening the final csv in excel. Can you open it in notepad or something similar instead and show us the screenshots? Excel will alter the formatting.

Also, I don’t understand why you want to use semi-colon instead of a comma? The name csv stands for Comma Separated Values, so generally you’d want to keep the comma as the delimiter

1 Like

Okay. converting to xlsx i have tried but not working for large size files.
Will try with for each row…

Thank you!

comma is not working at all that why tried with different delimeters by refering to some of the post from forum.

Will go through the post you have shared. and will get back if doesnt work

Thanks!