Trying to convert CSV to a xlsx

Help Studio
1

Hi all ive been trying to convert my csv to an xlsx for a while now. Im unable to use the conventional method as i meet the error getting error on read csv line 6 contains more values than the header line. As such i tried to use the read range function to read the values i want, but am encountering issues.

image
image496×596 18.7 KB

The error i get is that my csv file is not existent in my FormSGOutput workbook, but the point is that its a blank workbook, i want to read the range in my current csv file and output to a datatable, and then output it to the workbook itself.
Any help would be appreciated.

2

@Benjamin1,

I think you are using wrong activity to read CSV file. You should be using Read CSV activity.

image
image1325×398 25.8 KB

Thanks,
Ashok :slight_smile:

3

Hi @Benjamin1

→ Use Read CSV activity to read the data from CSV file and it will give output as DataTable. Let’s say dtInput.
->And then use Write Range Workbook activity and pass above output DataTable dtInput to write into Excel file.

image

Cheers!!

4

Hi sorry but i am unable to use the Read CSV activity because of the format of my CSV file. As a reference, i am encountering the same scenario as this person.

The only issue is i need help with the formatting for the structure as shown above in my screenshot.

5

Hi sorry but i am unable to use the Read CSV activity because of the format of my CSV file. As a reference, i am encountering the same scenario as this person.

The only issue is i need help with the formatting for the structure as shown above in my screenshot. So im looking to solve the issues using this instead.

6

Read CSV as a data table and then write the same in Excel file.

@Benjamin1 hope these steps solve your issue.

Here’s how to convert a CSV file to an xlsx file using UiPath Studio:

Using Read CSV and Write Excel Activities**

This method involves reading the CSV data and writing it to a new Excel file.

  1. Drag and drop a “Read CSV” activity onto the workflow designer.

    • Set the “File Path” property to the path of your CSV file.
    • Optionally, configure other properties like “Headers” (if the first row contains column names) and “Delimiter” (if different from comma).

  2. Drag and drop a “Write Excel” activity after the “Read CSV” activity.

    • Set the “File Path” property to the desired path and filename for the output Excel file (e.g., “Output.xlsx”).
    • Set the “Sheet Name” property to the name of the worksheet in the Excel file (optional, defaults to “Sheet1”).

  3. Connect the “Read CSV” activity to the “Write Excel” activity. The output from “Read CSV” (usually named “dt_Result”) will be used as input for “Write Excel.”

  4. Run the workflow. This will read the data from the CSV file, create a new Excel file, and write the data to the specified sheet.

7

You cannot Read Range a CSV. Read Range is for Excel files.

It sounds like your CSV is improperly formatted. Can you drag and drop it into a reply so I can look at it?

8

I am unable to reformat the csv even though i know its format has issues. The only 2 ways i am able to use is to either read.txt file or read range. Below ive attached the link to the documentation for the explanation for read range and it shows that read range can be used for.csv files as well.

9

Can you show me the file so I can see how it’s formatted and come up with a strategy for reading it properly?

10

image
image1830×173 7.85 KB

Due to sensitive info we cant show the data info below, but as you can see the first few rows 1 to 5 are shorter and i want to only start reading from a6 onwards, but due to the nature of the document, i cannot delete the contents, so im currently looking for a solution. Thanks

11

Use Read Text File, remove the first 5 lines, then use Generate Data Table.

12

Hi Thanks for the suggestion. I have read the file as a .txt, but i want to filter such that i only collect text from the start of row6 and column D normally in an excel file or CSV, how can i achieve this result now that its a .txt file?

13

To remove the first 5 lines, use this in an Invoke Code:

System.IO.File.WriteAllLines(destinationFile,System.IO.File.ReadAllLines(sourceFile).Skip(5).ToArray)

Then you can just use Read CSV to read the destinationFile. Once you have it in a datatable you can remove columns you don’t need and do whatever else you want with it.

14

Hi we managed to remove the lines, but we have trouble filtering out the columns using the filter data table function.

image
image298×601 14.6 KB

image
image1344×952 20.5 KB

image
image816×567 20.5 KB

We tried to filter like this, but we get the error that value for the argument ‘Column Name’ is not set or invalid.

15

The error is because those column names don’t exist in the datatable.

Also, you’re on the wrong tab in Filter Data Table. That tab is to remove rows. Go to the Output Columns tab and either list the columns to keep, or set it to remove and list the columns to remove.

Also, you don’t have to use a bunch of different datatable variables. You can put the same datatable as your input and output for Generate Data Table and Filter Data Tabl.

16

Hi postwick,
The issue encountered is the filtering is improper, which results in the output being inaccurate and unable to trace the columns. When checking what exactly is inside the data table, it seems that theres an error in the formatting somewhere, which was already present before the filtering into a data table itself.

image
image1329×649 43.7 KB

image
image912×296 9.23 KB

This is the sample output i get when i retrieve the datatable before it gets filtered. It seems the column headers dont match

17

Can you post the text file so I can look at it? Looks like you’re not properly removing the lines at the beginning, so you’re not ending up with the headers on line 1. I suggest using the expression I gave you instead of Split.

18

Hi postwick,

image
image1392×239 12.5 KB

due to confidential information in the columns, we are unable to show the data, but from here, it seems that the column headers have split into four different lines, as for the expression you have given, ill give it a shot but the split worked fine during testing.

19

That just looks like word wrap making it go to the next line, not actual linefeeds.

And that’s not CSV data, that looks like fixed length. You have to designate it that way in the Generate Data Table and give it the length of each column.

20

hi @Benjamin1 ,
You can use Readrange inside Excel scope to read CSV file then write datatable to other excel file
Or try convert by VB code
Could you share your file, I will try it

Regards,