How to read tab delimited data file into data table?

Help
,
1

Hi all,

I have a data file to read in for processing which is in tab delimited format. But the Read CSV activity only accept specification of delimiter in Comma or Semicolon. There’s no way for me to specify tab as the delimiter.

Anyone have good idea how to overcome my problem?

Thanks,

Tew

2

Hi Leepoo.

As we don’t have tab delimiter in “Read CSV” Activity.
Why don’t you convert your file to comma/semicolon delimiter format and try the same.,
Though its not right approach .Hope some one will come up with the better idea.
Please refer the link.

3

Apart from @ddpadil solution, you can read the file using File.ReadAllLines(“file.txt”) and then loop through each line and split the text in that line in an array using string.split function based on tab as delimiter.
Now each value of your array, you can assign it to datacolumn of a datarow and add that row into the datatable.

1 Like
4

Hello.

I think the easiest solution is to use the newly added Generate Data Table Activity. Just read the file as a text file and give as an input to the activity. Inside specify what type of column and row separators you need. This should return the results as a datatable.

1 Like
5

Hi Sachin_Desai and Cosmin_Ion_Nicolae,

Thank you very much for the suggestions, I’ll give them a try.

6

Hi Cosmin,
The Generate Data Table Activity seems rather buggy and glitchy. Almost always reads all the columns into a single column and doesn’t recognize the column separator. Not sure if this has been raised as a bug yet

1 Like
7

Hi @leepoo, CSVBuddy might be a good tool to use. It’s free for personal and commercial use, and can be run as an executable or as an Auto Hot Key script, which means it could easily be incorporated in your workflow.

Changing field delimiters is really easy with this tool. Here’s a screenshot:

csvbuddy_screenshot

Make sure you get the latest version - update if necessary. I recommend compiling the source or running the script from the CSVBuddy github repo.

Update: Also included here are CSVBuddy-master.zip, a replacement CSVBuddy.ahk to account for a missing lib, and the missing lib (ObjCSV.ahk). If you run CSVBuddy from the extracted folder as-is, you’ll get an error message about the missing lib ObjCSV.ahk. The edited CSVBuddy.ahk requires ObjCSV.ahk to be in the same folder as CSVBuddy.ahk. The executable downloaded from the link at the top doesn’t have this issue.

CSVBuddy-master.zip (1.4 MB)

CSVBuddy_update.zip (36.5 KB)

Regards,
burque505

8

Some improvements will be implemented for the Generate Datatable Activity. It will allow you to specify a special functionality when interacting with csv strings and should improve the accuracy on this type of input.

1 Like
9

its easy one what problem you are facing in this?

10

please watch this one

11

Hi,
Similar concept explained here,

12

Hi all, Has anyone found a solution for reading the data into multiple columns instead of a single column?

13

I am dealing with a similar issue here. Has this been resolved?

14

This is a true statement. The generate data table puts everything into one column and does not recognize the column separator even when defined in the parameters.

15

There is a simple solution to this. Probably the easiest.

Read the text file.
Assign to the same (or any other string) a replaced variable by replacing vbTab to “,”

image

If any of the text already has a comma then you’ll need to quote them or replace the comma if your requirement permits that.

Hope this works.

16

Then you can use ‘generate datatable activity’ using the CSV string. This will work better than the tab-delimited string.