How to get the formatted data in datatable or excel through read csv activity

I have data in csv file but when using the read csv activity it’s not storing the data in a formatted datatable.
If we are writing to excel by using write range in excel application scope all the data storing in single column with delimiters and quotes. How to get the formatted data. which activities are good to use.
Attached the sample.csv.
I need the csv data in a formatted manner and get the each cell in the row to fill the application form.

there is a option preserve format in read range activity for this

@user123456, While using Read CSV have you changed Delimiter as needed? E.g.

Cheers

yes, if we are using delimiter as semicolon, receiving the below error.
Message: The CSV file format for sample.csv is invalid

Preserve format is used to preserve the format but I don’t need to preserve that format. I need to get the data in datatable or excel in structured format without ; and ".

@user123456, Do you have semicolon after Phone Number? I think this is because you have semicolon at the end of each line. Can you remove that and try again?
Again, above suggestions are based on certain assumption but it will be great if you can attached sample CSV and workflow if possible.

Cheers

yes, I have semicolon in the end, its dynamic generated csv file and every time we download the file it will be there. I am unable to attach the zip folder I am a new user. please check the screenshots in the question for sample file and workflow screenshot is here.

@user123456, You should set Delimiter property to Semicolon for sure as I suggested earlier. (In your screenshot I can see it is set to Comma which is wrong). If you are not able to attached zip then can you please post CSV as string here (Not as screenshot)?

I tried with that so many times it’s giving the above mentioned error. please check the screenshot also.

. If I am using comma at least I am able to read the data through csv activity and store in datatable. But the data in datatable is not in a formatted way to fill the forms. I need to use the F Name, L Name .etc all the fields to fill the form from each row.

@user123456, I understand and that’s the reason I am asking you to post your csv content here as a text (not screenshot), so that I can have a look whats wrong. I tried on my local and its working fine. So I suspect that there is something wrong in CSV.

Cheers.

F Name;L Name;C Name;Designation In Company;Experience in year,Address;E-Mail;Phone Number
“A1”;“A2”;“AAA USA”;“Software Developer”;“1”;“500001 the A";"A1@gmail.com”;“1111111111”;
“B1”;“B2”;“BBB USA”;“Software Developer”;“2”;“500002 the B";"B2@gmail.com”;“2222222222”;
“C1”;“C2”;“CCC USA”;“Software Developer”;“3”;“500003 the C";"C3@gmail.com”;“3333333333”;

@user123456, There are two issues in your CSV.

  1. There is a comma instead of semicolon after “Experience in year,”
  2. Semicolon is missing at the end in the header line

Here is updated one:

F Name;L Name;C Name;Designation In Company;Experience in year;Address;E-Mail;Phone Number;
“A1”;“A2”;“AAA USA”;“Software Developer”;“1”;“500001 the A";"A1@gmail.com”;“1111111111”;
“B1”;“B2”;“BBB USA”;“Software Developer”;“2”;“500002 the B";"B2@gmail.com”;“2222222222”;
“C1”;“C2”;“CCC USA”;“Software Developer”;“3”;“500003 the C";"C3@gmail.com”;“3333333333”;

After this correction, it should work fine.

Cheers

  1. Apologies, it’s a semi colon only. My original csv I cannot share, so I have prepared the sample csv, while I was writing the data to this sample csv mistakenly kept the comma instead of semicolon. It’s semicolon only. Thank you for noticing it and your time.
  2. In my original file also, I don’t have semicolon at the end of the header.
  3. It’s a dynamic file, header will be the same every time and the content will be changed.
  1. I tried with original file also in the read activity, I have faced same above mentioned errors.

@user123456, Ok. Can you tell me count of semicolons in your header row and your first row? Ideally it should be same?
Alternatively can you open this file in Excel manually as csv?

Header= 7 semicolons
First row= 8 semicolons (including ending one)
All the remaining rows also have 8 semicolons
I am able to open but its giving an warning to save in csv format. Please check all the data in first column. Thank you so much for your time.
Screenshot:

@user123456, That’s the problem. Adding semicolon at the end of header row will solve the problem.
Reason: Header row will become columns which is 7 in your case, but data rows has values for 8 column which results in exception.

Cheers

Thank you for your time. Yes, manually it’s working. but we don’t want this option because we need to open the file after download and add the semicolon at the end of header row, save it through automation and use the read csv. I have to use this work flow for multiple times (>1000) for same format csvs to fill the forms. Isn’t it going to be a performance issue and slow the process. Let me know if there is any other possible way. Thank you again for looking into this issue and helping us.

I mean is there any way to add the ; to that header row and any other activity which handle this issue. Thank you

When I’ve had problems reading a csv file I’ve used File.ReadAllLines and String.Split.

File.ReadAllLines with create an array of strings with each line in the file as an element in the array.

String.Split will create an array of strings based on a delimiter.

Then is just a matter of looping through the array returned by File.ReadAllLines as an outer loop and an inner loop for the array returned by String.Split to create a csv file or datatable to export.