Space delimited file (formatted text)

[Anonymous]

Hello,

I am PhD student in Beijing, China. My major is about Biological Sciences.

I have the data in excel format, I want to convert it into formatted text(space delimited),  I tried in excel the save as option but it did not work.
As a matter of fact I know some text editors such as notepad++ and others, but then again notepad++ and other text editors did not convert properly. It gave very strange format. I confirmed it while opening txt file in excel converted by text editor. All my efforts went in vain. May be I am missing key point in conversion.
Could you  tell me other software or way to convert excel into formatted text (space delimited)?
Looking forward!!
Best Regards,
Abdul

[Pointdump]

Abdul,

After saving the file as a CSV in Excel, pretty much any text editor has a "find and replace" function where you can replace the comma with a space:

Dave

[Anonymous]

@Pointdump Thank You very much, but still there is problem.

If you do not mind, I can share my file with you.

since there are 102 plant species in upper title rows, but it yields 103.

Could you please address this issue?

Best Regards,

Abdul

[Pointdump]

Abdul,

There are 103 Columns because 1 X A is necessarily blank; top row and left column are descriptive headers. If you need 102 columns X 61 rows you'll need to strip out that top row and left column.

What program are you inserting this data into?

Dave

[Anonymous]

I am using NODF software to do some analysis about ecology.

Between I am thinking that if I strip out the left column and top row .I may use lose the title information.

What you suggest?

Best Regards,

Abdul

[Pointdump]

Abdul,

"...if I strip out the left column and top row I may use lose the title information."

I'm unfamiliar with NODF software, so I don't know if stripping out the descriptive headers is a good thing or not. You might look for a statistical forum to ask your question.

Dave

[autoMick]

Stripping out the header rows from data files has been a frustration of mine ever since I started using Civil3d. Today I created 26 duplicate files for no other reason but to allow Civil3d to read them, but as the headers had the survey dates, benchmark references etc. embedded, I still needed to keep the originals and ensure that the new files don't get separate from this information. It would be so much simpler if Civil3d would do this::

http://forums.autodesk.com/t5/autocad-civil-3d-ideas/point-file-formats-to-accept-header-rows/idi-p/...

In the meantime, I just have duplicate files one with header data and one without.

Cheers

- Mick

[autoMick]

As far as text editors go, I find it very hard to look past AkelPad (google it). It has good search/replace can handle very large files and is fast.

Microsoft Excel has this great button:

and as Dave says, you can save in a whole variety of formats.

Also, if you really want to get into statistical analysis google the "R project" - takes some getting used to, but this is an incredibly flexible platform for data handling, recoding and analysis (I use the Deducer front end).

Cheers

- Mick

[neilyj666]

I'm not sure what relevance this thread has to Civil 3D...as the data file doesn't look like anything that would be of use???

[Anonymous]

@Pointdump Thank you very much for your suggestion.

But still my problem is hanging. I hope to solve it as soon as possible.

Best Regards,

Abdul

[autoMick]

@Anonymous I'm pretty sure that @Pointdump has answered it. You should import your data into Excel, resolve the issue with your missing first header label and any other inconsistencies then export (i.e. Save As...) from Excel in the format you want. The issue doesn't seem have anything to do with Civil3d as @neilyj666 points out, and in fact I don't think Civil3d could do what you need if you wish to retain the header information - the software I mentioned in my previous post has much better capabilities.

Cheers

- Mick

[Anonymous]

Thank you very much Mick. The above mentioned all solutions did not work.

@neilyj666,I am also fully aware that this forum is not txt editor or excel, but I was trying.

Best Regards,

Abdul

[autoMick]

Abdul, please see attached - using the techniques described above.

Regards

- Mick

[Anonymous]

Dear Mick

Thank You very much. I run that format in NODF software, but again problem is not solved.

I am going to send you the txt file generated from excel by some one.

I also asked him many times how to generate accurate format, but he never told me technique, instead he sent me the accurate file.

I can send to as well, I tried to figure out how he did it, but I was kind of lost.

You may have look, this is accurate format.

Thanks

Abdul

[autoMick]

Try this one....

[Anonymous]

Dear Mick, You absolutely nailed it. Thank you very much.

Could you please explain me, how you converted?

it would be great If you can show me steps by images?

Really Looking forward for that.

Best Regards,

Abdul

[autoMick · Accepted solution]

OK that's great Abdul,

The original file had both spaces and tab characters, as well as spaces in the PAT Number column. So the process was to open up the csv file in Excel, rename PAT 1 to PAT01, etc. and add in a field name "PATNo" at the top. to fix up that column.

Then I saved this and opened the file in AkelPad (MS Word will do it too), I searched for all tab characters and replaced with spaces, then I searched for all double spaces and replaced with single spaces (just to make sure).

then I renamed the file extension to ".txt"

As an optional extra, I then opened the file in Excel, then used the "Text to columns" tool highlighted previously to make sure all the data parsed correctly into uniform columns.

Regards

- Mick

Snip below is from Akelpad, showing settings used to replace tabs with spaces - the rest I hope would be fairly straightforward.

[Anonymous]

Dear Mick Once again I am thankful to you.

You guys are doing amazing job.

This forum is really helpful.

My all best wishes to you and experts of this forum.

I am really thankful for this masterpiece.

Best Regards,

Abdul