Formatting CSV / Excel File

I use Studio modern design.

“Text to column” works one time but the next time its still the format i dont want to have.

@Katarina ,it strange.Let me think.Before I use VBA,I use other way but failed.Let me search some other ways.

1 Like

@HuaiYe
@Katarina
you can use generate data table activity with CSV parsing and AutoDetectTypes Set to “True” to get the format correctly
steps to do

  1. Read Csv with headers → dt_RawData
  2. output data table ->dt_RawData to txtdt
  3. Generate data table from txtdt with “CSV parsing” and “AutoDetectTypes” Set to Truedt_Extracted
    4.write range dt_Extracted

Settings, i’ve used :

and the result

1 Like

@Katarina you can try this way.But @hemanth.bhat ,the data you show still become different than before yet?I tried your way,the format still change.Although my excel changed the setting before.

Hello @Katarina

If youa re writing the datatable to an excel, i hope that excel can be like a static template.
So keep a static excel with all the required formats for the columns.

As a first step of the execution , copy that tempalte to a folder and after getting the datatable write it to that excel. i hope it will help to preserve the format as required.

@HuaiYe ,
read csv by default outputs all the data as string
and using generate data table with auto detect type will generate by inferring the type
however if a column contains different type like string and number it will default to string

Thank you! Did you add any settings for decimales? 13461,59 becomes 1346159

image

Thats how my data table looks like before using Generate Data Table

and thats the output of the data table

@Katarina
for the decimal place representation you are using “,” separator
that is related to cultureinfo, I’m not sure how to resolve it
hope this article in the forum may help you

What about now?Did you solve it?

1 Like

Thanks for all of your help. I think I solved it - hope I wont find any new problems with it.
After reading the CSV I looped trough the datatable and replaced “” and the comma. Thanks! :slight_smile:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.