Notepad to Excel write


#1

I have 3 columns in notepad with multiple rows.I want to read these values from notepad and write it inside Excel .I can able to write in to excel but everything goes inside a single column . Now I am trying to do this with data table data row property but stuck . Please help.


System Index out of Range Exception
System Index out of Range Exception
#2

How are the columns saperated in the text file? You may try below logic to add text file data to data table.

Assuming columns are spareated by comma
Create a datatable with 3 columns.
string readText = File.ReadAllLines(“yourfile.txt”);
foreach(item in readText)
{
If (item<> string.empty)
{
Splititem= Split.string(item,",")
add data row activity here and in Array row property add below line and datatable variable in datatable

{Splititem(0),Splititem(1),Splititem(2)}

}

}

Now load the datatable into Writerange property.

Typing from mobile so can’t attach code.Hope it helps.


#3

Thanks for the quick turnaround . I already did most of the parts as you suggested but my code works well if i use array row : for example {item}. Since i am using single {item} it goes in to single column inside excel. I figured out data row is the best choice here but stuck on the syntax . Can you please share the sample . Thanks .


#4

Just forgot to add in notepad columns were separate through Space.


#5

Sorry I don’t have access to laptop, I will try to send tomorrow.i see @acaciomelo is online may be he can help.

Why don’t you try below piece of code in the meanwhile.

Split.string(item," ")

add data row activity here and in Array row property add below line and datatable variable in datatable

{item(0),item(1),item(2)}


#6

Once again thanks for the quick turn around . I will wait till tomorrow for the sample. Thanks


#7

Just an final update :

I tried below options but i am doing some thing wrong still all three columns are going in to single excel column :
add data row activity here and in Array row property add below line and datatable variable in datatable

{item(0),item(1),item(2)}


System Index out of Range Exception
#8

@SKE Attached is the rudimentary logic and you can tweak it as per your requirement. Let me know if you have questions.

TexttoExcel.zip (5.3 KB)


#9

Got it. Works Great. Thanks a lot :slight_smile:


#10

lSl. No.| Part No. | Description of Goods | HSN |GST Rate| Qty. | Rate | Per l
SPARES FOR :KOMATSU DUMPER
4 42820120 Cross (Spider Assy. Rear) (Koyo Japan) 8731 18% 5 34000 .00 EACH

this is the text that is have extracted from a PDF and save it in text format.
I want to extract (Rate),(Qty.) from the first row as a key whose value will be (34000.00),(5) which is in the third row. Now I am trying to do this with data table data row property and dump it into Excel but stuck while extracting only the key pair value.Please help.


#11