Converting text to excel file

Help Activities
, , ,
1

Hi please help, I’m trying to convert this text file to an Excel file and I need the accounting period and RI extraxt to show up. The solution i used is writing the headers in the first column (Startdate, etc.).

The image displays a text excerpt outlining a summary of RI Extract information from an accounting period, including terms and data annotations. (Captioned by AI)
The image displays a text excerpt outlining a summary of RI Extract information from an accounting period, including terms and data annotations. (Captioned by AI)1117×129 4.36 KB

Excel file
image

2

@pabaleloh

Check this post if it helps.

4

@pabaleloh

how about creating another datatable which only keeps first 2 rows.

then while writing to excel, first you write this datatable with only 2 rows.

then you write datatable with rest of the table into excel. you may need to specify the range to start from A3 instead in this case.

5

Follow the below step:

1.Read the Text File.
2.filteredText = String.Join(Environment.NewLine, fileContent.Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries).SkipWhile(Function(line) Not line.ToLower().Contains(“startdate”)))

3.Use Generate DataTable Activity.
4.Input: filteredText,Output: myDataTable
Settings:Column Separator: ;

Mark solution if its help :blush:
Happy Automation

1 Like
6

Hi @Pabaleloh, here are some options you can try. Let me know if any of this helps:

Option 1: Aleem’s Method (Best one)

Use Read Text File activity to read your file, then Generate DataTable with semicolon separator. For filtering the lines, use Assign activity with and handle it with an expression. Something like this:

filteredLines.SkipWhile(Function(x) Not x.Contains(“startdate”))

Then use Write Range for your RI Extract info first, then Append Range for data from A3 onwards.

Option 2: Text to Columns Method

Use a Read Text File to put everything in Excel as it is, then use Text to Columns activity to split the semicolon data. Your metadata will stay on top.

Option 3: Build Step by Step

Use Build Data Table activity to make your structure, then Add Data Row for RI Extract and Accounting Period rows, then For Each loop for your actual data.

I would suggest trying Option 1 first - Aleem’s method is good and the Generate DataTable activity will handle most of the work for you.

Try it out and tell me how it goes! :slight_smile:

7

Hey @pabaleloh,try the bellow steps

  1. Read the text file using Read Text File activity.
  2. Split the lines using Split(text, Environment.NewLine) to access specific lines.
  3. Use Write Cell activity to write the first few lines:
  • Cell A1 → "RI Extract Summary All RI's (Revised 9 Aug)"
  • Cell A2 → "Accounting Period=349"
  1. Then, write your table starting from row 4:
  • Use Write Range and set StartingCell to “A4”
  • This will avoid overwriting the metadata.
1 Like
8

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