Special charecter gettting added in the first column title

Help
,
1

HI Team

Iam trying to execute a query using execute query component then save the output as excel and upload that in a site,while trying to upload the excel it is not getting uploaded if we check log of that website it is showing that special charecter ‘???’ is getting added before the first column heading. If i generate the same excel by copy pasting the content in DB by executing the same query and try to upload it is getting uploaded. Is it problem with the execute query component or with the excel?

2

Hi @Divya_Gomathy,

May be it adds some extra spaces before the column heading so it might be worthful if you use the Trim function before writing the output to the excel.

colHeading.ToString.Trim()

3

@rtallapudi tried that too but same thing happens the file is not getting uploaded.

4

Hi @Divya_Gomathy,

Check the generated file Manually before upload it in the application, print the first header alone by reading it as excel. Check whether it is excel generation error, or during upload it occurs?

5

HI @sarathi125 actually i want to generate as CSV is there any option to ignore first row and start writing from second row i think only in excel it is possible.

6

In the write CSV Activity property window Uncheck the “Add Headers” option, then write it.

7

@sarathi125 but i need the headers too even if i copy without header it will generate the datas from first row only right!

8

@Divya_Gomathy,

yes we can ignore the first row. To do this you can read the excel sheet (Read Range) into a data table, from there you can write the data into another excel sheet by ignoring the headers.

image

9

@rtallapudi yea i tried this option but i want it as a csv file,the option i could think of is adding the header previously in an excel file then writing data from the query and recording the file–>save as–>.csv option…any other way is there??

10

Few thoughts:

  • Create a csv file with the headers (Assuming that you know header names)
  • Read data from the excel file (source data) into a data table by ignoring the headers
  • Write the data from data table into csv file using Write CSV activity - here we should check the Add headers
12

@rtallapudi tried this way too it dint work,special character is getting added when write csv component is executed.

13

Hi,

If you would like to rename the column header then you can use an Assign activity:

Assign dt1.Columns(0).ColumnName = "abc"

If you want to rename the first cell then you can like this:

Assign dt1.Rows(0).Item(0) = "abc"

If you want to skip first row, then you can use this in the Write CSV:

dt1.AsEnumerable.Skip(1).CopyToDataTable

Or you could possible just ignore Headers with unchecking Add Headers.

You can also use a string (because CSV files are text-based comma-delimited)

Output Data Table to strData
String.Join(System.Environment.Newline,strData.Split({System.Environment.Newline},System.StringSplitOptions.None).Skip(1))
Write Text File with .CSV extension

Just some ideas I’m throwing out there, and I apologize if I make any mistakes.

Regards.

14

Hi @ClaytonM if i use dt1.AsEnumerable.Skip(1).CopyToDataTable the first row is not getting skipped :neutral_face:,any option to just ignore the header and write from row 2?

15

Thanks @arivu96 the solution u gave worked.

1)Creating header manually
2)Appending the csv by passing the data table values.

I was able to upload :slight_smile:

1 Like