Select excel columns and copy datatable to text file

Help
1

LCC 20190827.xlsx (9.4 KB)

Dears,
See attached excel file.
Currently I have a task to copy column A,B,C of this file(include headers) and save it into a new text file.
I only want to copy column A,B,C, not other columns, and the total cell numbers of each column is not fixed, so it’s not proper to set read range from A1 to a exact C cell.
After reading the range, I use a write text activity and change it to string, but it doesn’t work.
Pls help me on this.

Capture
Capture277×590 34.8 KB

2

Hi @Allen_Huang

Please find the attached file for reference.

Test.zip (18.1 KB)

Steps followed:

  1. Read the whole file converted it to datatable
  2. Got the end of the rows for the datatable
  3. Use that value to re-read the range
  4. Used the new output to write in the output plane, you can update it to write on the Text file

Hope this helps :slight_smile:

Good day :smiley: :slight_smile:

2 Likes
3

@Allen_Huang

  1. Read Excel and assign it to datatable dt_1.
  2. use assign dt_2=dt_1.DefaultView.ToTable(false,“ColumnName1”,“ColumnName2”) to select particular columns.
    3.Use Output Datatable Activity to convert datatable into text.
    4.Write into text file using Write Text File Activity
1 Like
4

Dear Shubham_Varshney,
Perfect, it works!
It’s a very clever way to get the number of cells.
Let me share the key point here.
Read Range to datatableA, range all.
Assign Value=datatableA.rows.count
Read Range to datatable B, range “A1:C”+Value

And for excel to text, Output Data Table can convert the datatable to a text.

1 Like
5

Dear,
I tried your method, but I met two problems.
No.1 It only copy 2 columns instead of what I want 3 columns, I re-set as 3 columns, it’s ok.
No.2 The converted text contains a “,” between columns, which I don’t need. I need the text be displayed in notepad exactly like it in the original form.

i.e.
I want
A B C
1 AB LS
2 BC CN

But what we got is
A,B,C
1,AB,LS
2,BC,CN

Pls help on point 2#, thks.

6

Dear,
By using your workflow there’s still a little trouble, pls help to advise the solution.
The converted text contains a “,” between columns, which I don’t need. I need the text be displayed in notepad exactly like it in the original form.

i.e.
I want
A B C
1 AB LS
2 BC CN

But what we got is
A,B,C
1,AB,LS
2,BC,CN

7

@Allen_Huang Use str_value.Replace(“,” ," ").ToString in Write Text File Activity

1 Like
8

Thanks a lot.
But I’m sorry to bother you again, because there’s still a minor problem.
How to keep the excel layout when we insert the value into the notepad?

This is the result get by above method.
NC1CNKG8KR6AG40105AA220XX51
NC1CNKG8QX6AG40105AA220XX51
NC1CNKG99H6AG40105AA220XX51

And below is the result we can get by manual Ctril C+Ctril V the excel range.
NC1CNKG8KR 6AG40105AA220XX5 1
NC1CNKG8QX 6AG40105AA220XX5 1
NC1CNKG99H 6AG40105AA220XX5 1

9

Put a space in the replace “,”," "
I hope this will help…
Sorry for late response, had an hectic day today :sweat_smile:

10

Dear,
Thank you for your reply.
Yes, this method can remove the “,”, but my current problem is that it can’t keep the original format in excel. Therefore it doesn’t work when I use the text file to upload to SAP.
You know, this is not a problem just to put some space " " into the quote, it’s a problem of format, excel always come with a structured format divided by cells.
Do you have any ideas about this?

11

Dear,
Another problem.
When I run the workflow based on your count row method, the text always can’t get the last row.
E.g, I have 1720rows, set range"A2:C"+value(I don’t need the headers), it only returns 1719 rows, can’t get the data of last row.
Pls help to check, thks.

12

Hi @Allen_Huang,

Please let me know how much space is required for pasting in the SAP ?

As for the range I apologize, it must be as follow:
“A2:C”+(value+1)

As the index starts from “0” thus it’s piking one less :slight_smile:

1 Like
13

NG OK
Dear,
See my screen capture, what I need is the OK.txt, but if we set a fixed number of space value we will get NG.txt.
This is like the “left align” function in the excel file, while the value length of each column is different, so we can not just put a fixed number of space to achieve this.
Do you have any idea how to fix this?

Thanks.

14

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