Reading CSV and storing to excel problem

Help
1

Hi

I am reading an csv file and exporting it to DT and then saving it to excel. Doing nothing with the file. Just read csv and store it to excel.
It works fine except it stores to the excel with delimitiers and everything.
It does not convert delimiters from CSV to excel columns.
Also it generates two extra columns named “Column 1 and Column2” that are not in the original CSV file.

Cant share the files sorry

2

Hi @Petar_Soce ,
Please try this.
csvToExcel.zip (6.7 KB)

Let us know if this doesn’t help you.

Best Regards,
SP

3

Unfortunately I have tried this and it wont work. Let me try and explaine a little detailed.
I have an incoming mail with a link in the body to the csv file. I extract that link with regex and then pass it to the excel application scope. Inside excel scope I have read range and output is dtCSV.
That output dtCSV if I write it with write range it writes me all the data with delimiters and creates two new columns. It makes a mess of a file really

the new excel with dtCSV written in to it look like this

https://www.google.hr/search?q=csv+excel+data&source=lnms&tbm=isch&sa=X&ved=0ahUKEwiYreqsi4PbAhUHKVAKHbJ-AwMQ_AUICigB&biw=1366&bih=642#imgrc=LD419BX6yrrHiM:

4

Hi.

Just out of curiosity,

Does it work if you Excel Write Range the dtCSV instead of Write CSV? And do you really need to use CSV filetype?

If you use Output Data Table to store it in a string, then use Write Text file with extension .CSV, does it work? If you append “sep=,” to the top of that string then use Write Text file again, does it work? (sep=, —> can be used to set the delimitter)

In the past, I have had to use the Save As Dialogue to convert an .xlsx to .csv file. So you might take that approach as well, assuming you are going from Excel to CSV.

Regards.

1 Like
5

HI

THe link that i got in the mail body which is a csv file generated from the database so it will always be a csv.
Yes, I tried to write range after read range. input for write range is DT output of read range. It writes the file but when I open it the data is not separated and sorted by columns. The data is like at the link I posted earlier. Just raw csv.

;;;;;;;;;;

6

I will trie to output it to txt with .csv extension and let you know

7

If you use an Excel scope to open the CSV, you need to use the Save As dialogue I believe to save it back to a CSV. Or use the Read CSV instead of Read Range to get the data, but it depends on how you are able to get the data.

8

I have to use read range. Read CSV is not working for some reason it always gives me an error that there are invalid characters in path. I presume because of the / symbols in the file path.
How do I access Save as dialog ?

9

You get to Save as by using TypeInto “[k(alt)]fa” to do Alt+fa. Then, you need to type in the filepath and filetype to CSV.

You can still use the Write Text file option as well as an alternative.

Regards.

10

Will try that…thank you :slight_smile:

11

Type Into inside excel app scope does not give anything…I have tried write text with .csv extension and hahah it saves it and all except one row are inside their columns.

I have 20 rows and 20 columns. Only one row is left unsorted…so the one row look like

data;data;data;data;data;data;data

…and all others are inside cells as they are suppose to be

12

data;data;data;data;data;data;data

You need to replace the “;” prior to outputting the file.
You can use the .Replace method on the string variable, like

Write Text file using text.Replace(";",",")

After the semicoln gets replaced with comma, it should delmit that line correctly.

13

Clayton thank you for your help. You are on to something here. Sharing the picture of my flow.
Problem is when it gets to read csv it gives me an error saying CSV file format is invalid.

csv_problem
csv_problem265×617 17.5 KB

I am using remove column 1 and 2 becaue it always generates it. I think that is becaude of the default database it has Column 1 and 2. Also i am removing first two rows as they are blank and dont need it.
It gives me CSV file and it has commas inside but like I said when it comes to “Read CSV” i gives me an error CSV file format is invalid.

14

Try with capital csv “Untitled.CSV” when you write the file. Do you still get Invalid CSV file format?

15

Just tried it. Still the same error. The CSV file format for Untitled.CSV is invalid

16

It’s possible that when you Remove the columns, you aren’t removing the columns found in:
data;data;data;data;data;data;data

therefore there are more columns in that row then the rest of the data, after you fix the delimitter.
If you upload the text, I could verify.

But, I’m thinking one possible solution to this would be to remove the Columns after you use Read CSV, so the number of columns stays consistent when you write it to the CSV.

17

Tried it and now I am getting an error a column named “Column1” already belongs to this datatable.
I just switch remove columns activites after read csv

I really appreciate your help since there is no one I can ask, but unfortunately I cant share any of the data because of sensitivity.

18

Do you have the AddHeaders checked in your Read CSV? You will probably want it checked so it sees the Column0,Column1,… as headers.

19

hi,

Please follow the below tutorial,

Thanks,
Karthik.

20

Hi

Still have a problem with read CSV…It wont read the file but I made a workaround which is working for me. I extracted CSV name and link to it and opened it and when the excel is opened i send hotkey ctrl+s and saved it as excel and from there I continued on.